Mysql的锁机制

最新推荐文章于 2022-11-24 22:46:28 发布

*豆豆

最新推荐文章于 2022-11-24 22:46:28 发布

阅读量142

点赞数

文章标签： mysql 数据库

本文链接：https://blog.csdn.net/tedfdndg/article/details/121747345

版权

不同的存储引擎支持不同的锁机制。MyISAM和MEMORY存储引擎采用的是表级锁；InnoDB存储引擎默认情况下是支持行级锁，也支持表级锁。

表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低

行级锁：开销小，加锁慢；会出现死锁，锁定粒度小，发生锁冲突的概率最低，并发度最高

一、MyISAM表锁

Myisam不支持事务

mysql的表级锁有两种模式：表共享读锁和表独占写锁

(1) 写锁 当一个线程获得对一个表的写锁之后，只有持有锁的线程可以对表进行跟新操作，其他线程的读写操作都会等待，知道锁释放为止。

session1	session2
获取表的write锁定： lock table mylock write; (mylock是表名)
当前session对表的查询、插入、更新操作都可行： select * from mylock; insert into mylock values(5,'e');	当前session对表的查询等等操作会被阻塞： select * from mylock
释放锁：unlock tables;

(2) 读锁

lock table read 加读锁后，当前session更新或者访问其他表会提示错误；另一个session可以查询表中数据，更新就会等待

session1

session2

获得表的read锁定：

lock table mylock read；

当前session可以查询该表记录：

select * from mylock;

当前session可以查询该表记录：

select * from mylock;

当前session不能查询没有锁定的表

select * from person；

Table ‘person’ was not locked with LOCK TABLES;

当前session可以查询或者更新未锁定的表：

select * from mylock；

insert into person values(1,'zhangsan');

当前session插入或者更新表会提示错误：

insert into mylock values (6,'f')

update mylock set name='aa' where id = 1;

Table 'mylock' was locked with a READ lock and can't be updated

当前session插入数据会等待获得锁：

insert into mylock values(6,'f');

释放锁：unlock tables;

获得锁，更新成功

(3) 注意

myisam 一般不需要用户使用命令显示加锁，MyISAM在执行查询语句之前，会自动给涉及的所有表加读锁，在执行更新操作之前，会自动给涉及的表加写锁。

MyISAM的并行插入问题：myisam表的读和写是串行的，在一定条件下，MyISAM也支持查询和插入操作并发执行。

session1 获取表的readlocal锁定：lock table mylock read local;

session1中不能插入修改mylock表，不能查询没有锁定的表；

session2可以向mylock插入数据，但是该插入的数据，session1不能查看。

session2修改mylock表中数据，会阻塞等待。

session1释放锁：unlock tables;

show status like 'table%' --- table_locks_waited 的值比较高，则说明存在着严重的表级锁征用情况。

二、InnoDB锁

(1) 事务及ACID属性

Actomicity 原子性要么全部执行，要么全部都不执行

Consistent 一致性事务开始和完成时，数据必须保持一致

Isolation 隔离性不受外部并发操作影响

Durable 持久性事务完成后，对于数据的修改是永久性的

(2) 并发事务带来的问题

脏读、不可重复读、幻读 --- 都是数据一致性问题，通过锁解决

show status like ‘%innodb_row_lock%’

InnoDB_row_lock_waits和InnoDB_row_lock_time_avg的值比较高,说明锁争用比较严重。

(3)行锁

共享锁(s) 又称读锁；排他锁（X）又称写锁

mysql Innodb 引擎默认的修改数据语句：update、delete、insert都会自动给涉及到的数据加上排他锁，select语句默认不会加任何锁类型。

手动加排他锁： select .... for update；共享锁：select....lock in share mode

InnoDB行锁是通过给索引上的索引项加锁实现的，只有通过索引条件检索数据，InnoDB才能使用行级锁，否则使用表锁。

① tab表中没有主键没有索引；

session1	session2
set autocommit=0; ---- 系统默认开启事务，但并未默认提交事务，需手动提交； select * from tab where id = 1;	set autocommit=0; select * from tab where id = 2;
select * from tab where id =1 for update;
	select * from tab where id = 2 for update;

默认情况下，autocommit为1，开启事务，并且在执行完sql后，默认提交。

在autocommit为1的情况下，手动 start transaction; 需要手动提交。

session1只给了一行id=1加了排他锁，但session2在请求其他行锁的时候，会出现等待，因为没有索引的情况下，innodb使用表锁。

② tab表中，id是主键索引

session1	session2
set autocommit=0;	set autocommit=0;
select * from tab where id =1 and name='1' for update;
	select * from tab where id = 1 and name = '4' for update; 虽然session2访问的是和session1不同的记录，但是使用了相同的索引，所以需要等待锁。

③ 共享锁 所有事务都可以读取，但是所有session都不能修改，当前session也不能修改

④ 排他锁 加上排他锁后，不能在此行加其他锁；只有当前session才可以对数据进行修改，其他session可读，不可修改。

⑤ 意向共享锁 Intertion Shared Locks （简称IS锁，属于表锁）

表示事务准备给数据行加上共享锁，也就是一个数据在加入共享锁之前必须先取得该表的IS锁

⑥ 意向排他锁 Intention Exclusive Locks(简称IX锁，表锁)

表示事务准备给数据行加上排他锁，也就是一个数据在加入排他锁之前必须先取得该表的IX锁

意向锁是InnoDB数据操作之前自动加的，不需要用户干预

⑦ 自增锁 AUTO-INC Locks

针对自增列自增长的一个特殊的表级别锁

show variables like '%innodb_autoinc_lock_mode%'

--- 默认是1，代表连续

自增列中，事务之前是10，若事务添加10条记录，若事务执行失败回滚，则表中下次新增，也会从21开始。

⑧ 死锁

session1	session2
①select * from tab where id =1 for update;	①select * from tab where id = 2 for update;
②select * from tab where id = 2 for update;	②select * from tab where id =1 for update;

mysql中，如果发现死锁，会随机自动释放。

session1中②执行，等待。然后session2中②执行，可以出结果，则session1中的②报错：