前言

刚开始接触MYSQL,对其锁机制并不了解,在项目里面,针对死锁以及锁竞争,约定了两条规则。

  • 对涉及多个业务表的更新,要遵守一定的顺序,如按照TABLE-A,TABLE-B,TABLE-C的次序
  • 对一个业务表的更新,要先排序,按顺序执行更新操作。

下面就在MYSQL中对一些场景进行演示。

死锁

死锁产生的条件:循环等待。我用的客户端工具是SQLyog,建立两个连接。以下简称SESSION-A, SESSION-B.另,大部分客户端,都设置成了autocommit,所以需要在每个会话里,先执行 set autocommit=0
在测试数据库里面,准备两个表a,b.

数据准备

CREATE TABLE `a` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(50) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `b` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(50) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

SESSION-A->SET autocommit=0;
SESSION-A->INSERT INTO a VALUES (1,'data');
SESSION-A->INSERT INTO b VALUES (1,'data');
SESSION-A->COMMIT;

好了,现在模拟死锁场景。

SESSION-A-> UPDATE a SET NAME='data from SESSION-A' WHERE id=1;
SESSION-B-> UPDATE b SET NAME='data from SESSION-B' WHERE id=1;
SESSION-B-> UPDATE a SET NAME='data from SESSION-B' WHERE id=1;
SESSION-A-> UPDATE b SET NAME='data from SESSION-A' WHERE id=1;

运行到第三步的时候,SESSION-B就被阻塞住了,
clipboard.png

在session-A中执行SHOW FULL PROCESSLIST;,显示当前被阻塞的会话和语句
clipboard.png
运行到第四步的时候,控制台输出UPDATE b SET NAME='data from SESSION-A' WHERE id=1;. 同时SESSION-A释放了所有的锁并回滚了。同时SESSION-B的阻塞也消失了,完成了a表的更新,执行commit可以顺利提交。同时SHOW STATUS LIKE 'innodb_row_lock%';,可以查看锁竞争的情况。

上面的例子,是对相同记录的锁竞争情况,那么对于不同的记录,表现是否相同呢,为了验证,新加入了两条记录

INSERT INTO a VALUES (2,'data');
INSERT INTO b VALUES (2,'data');
commit;
SESSION-A-> UPDATE a SET NAME='data from SESSION-A' WHERE id=2;
SESSION-B-> UPDATE b SET NAME='data from SESSION-B' WHERE id=1;
SESSION-B-> UPDATE a SET NAME='data from SESSION-B' WHERE id=1;
SESSION-A-> UPDATE b SET NAME='data from SESSION-A' WHERE id=2;

最后的结果,完全没有阻塞。

这个例子,也验证了InnoDB加的是行锁,不同行的更新不会被阻塞。下面详细讲一下MYSQL的锁模式。

锁模式

MYSQL的锁涉及两种行锁S和X,和两种内部使用的意向锁IS和IX,具体的说明如下:

  • 共享锁(S):允许一个事务去读一行,阻止其他事务获得相同数据集的排他锁
  • 排他锁(X):允许获得排他锁的事务更新数据,阻止其他事务取得相同数据集的共享读锁和排他写锁
  • 意向共享锁(IS):事务在给一个数据行加共享锁前必须先取得该表的IS锁。
  • 意向排他锁(IX):事务在给一个数据行加排他锁前必须先取得该表的IX锁。

clipboard.png
我们关心的是那几个标着兼容的单元格。
IX(当前) vs IX(欲加):即表示对不同的行加排他锁,是OK的。

SESSION-A->SELECT * FROM a WHERE id=1 FOR UPDATE;
SESSION-B->SELECT * FROM a WHERE id=2 FOR UPDATE;

IX(当前) vs IS:(欲加)和IS(当前) vs IX(欲加):即对不同的行加读锁和写锁,是OK的

SESSION-A->SELECT * FROM a WHERE id=1 FOR UPDATE;
SESSION-B->SELECT * FROM a WHERE id=2 LOCK IN SHARE MODE;
或者
SESSION-A->SELECT * FROM a WHERE id=1 LOCK IN SHARE MODE;
SESSION-B->SELECT * FROM a WHERE id=2 FOR UPDATE;

其他的,慢慢琢磨。
引用 https://blog.csdn.net/tanga84...
另有一个困惑,在原文中提到,INNODB的行锁通过索引来实现,但是我的例子中,两个表是都没有建立索引的,希望有人可以解答。


沈子平
183 声望17 粉丝

慢慢积累,一切都不会太晚.