出乎意料的征象
我们有一张测试表 t1,表中有一些数据,当 session1 开启一个事件,并实行了 select for update 操纵后仍未提交事件,在并发事件(如 session2)开启事件并行实行一些操纵会有不同的锁征象,体现在:
- select for update 会出现锁等待
- delete 会出现锁等待
- update 实行成功,不受影响
为什么 select for update、delete 会出现预期内的锁等待,而 update 不会出现锁等待呢?
题目复现
起首在 test 库中创建一张测试表 t1- greatsql> show create table t1\G;
- *************************** 1. row ***************************
- Table: t1
- Create Table: CREATE TABLE `t1` (
- `id` int NOT NULL,
- `type_id` int DEFAULT NULL,
- PRIMARY KEY (`id`)
- ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
- 1 row in set (0.00 sec)
复制代码 在表中插入原始数据如下- greatsql> select * from t1;
- +----+---------+
- | id | type_id |
- +----+---------+
- | 1 | 3 |
- | 2 | 3 |
- | 3 | 4 |
- | 4 | 3 |
- | 5 | 4 |
- | 6 | 4 |
- | 7 | 5 |
- | 8 | 5 |
- | 9 | 4 |
- | 12 | 4 |
- | 15 | 4 |
- +----+---------+
- 11 rows in set (0.00 sec)
复制代码 现在 session1 对表 t1 开启一个事件,并实行select for update操纵,保持事件,不实行 commit- greatsql> begin;
- Query OK, 0 rows affected (0.02 sec)
- greatsql> select * from t1 where type_id=4 for update;
- +----+---------+
- | id | type_id |
- +----+---------+
- | 3 | 4 |
- | 5 | 4 |
- | 6 | 4 |
- | 9 | 4 |
- | 12 | 4 |
- | 15 | 4 |
- +----+---------+
- 6 rows in set (0.02 sec)
复制代码 模拟并发 session2 开启事件,并发实行操纵- greatsql> select * from t1 where type_id=3 for update;
- ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
- greatsql> delete from t1 where type_id=3;
- ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
- greatsql> update t1 set type_id=2 where type_id=3;
- Query OK, 3 rows affected (0.01 sec)
- Rows matched: 3 Changed: 3 Warnings: 0
复制代码 征象解答
其实这并非是一个题目,而是 GreatSQL READ COMMITTED 隔离级别下的一种正常征象,具体体现在
当我们使用的是 GreatSQL 的 READ COMMITTED 隔离级别- greatsql> show variables like '%iso%';
- +-----------------------+----------------+
- | Variable_name | Value |
- +-----------------------+----------------+
- | transaction_isolation | READ-COMMITTED |
- +-----------------------+----------------+
- 1 row in set (0.03 sec)
复制代码 在 session1 实行 select * from t1 where type=4 for update; 后,由于表 t1 在 type_id 字段上并没有索引,这会导致全表扫描。但是呢,在 GreatSQL 的 READ COMMITTED 隔离级别下会对全部的行进行加锁,找到对应的记录后加锁,会对不符合条件的行释放锁
因此 session1 最后获取了意向排他锁(IX)和 6 条行锁(X,REC_NOT_GAP),仅仅锁住了 type_id=4 的 6 行记录。- greatsql> select ENGINE_LOCK_ID,ENGINE_TRANSACTION_ID,LOCK_MODE,LOCK_TYPE,INDEX_NAME,OBJECT_SCHEMA,OBJECT_NAME,LOCK_DATA,LOCK_STATUS,THREAD_ID from performance_schema.data_locks;
- +-----------------------------------------+-----------------------+---------------+-----------+------------+---------------+-------------+-----------+-------------+-----------+
- | ENGINE_LOCK_ID | ENGINE_TRANSACTION_ID | LOCK_MODE | LOCK_TYPE | INDEX_NAME | OBJECT_SCHEMA | OBJECT_NAME | LOCK_DATA | LOCK_STATUS | THREAD_ID |
- +-----------------------------------------+-----------------------+---------------+-----------+------------+---------------+-------------+-----------+-------------+-----------+
- | 140547538324696:1073:140547436105584 | 2719 | IX | TABLE | NULL | test | t1 | NULL | GRANTED | 93 |
- | 140547538324696:11:4:6:140547436102528 | 2719 | X,REC_NOT_GAP | RECORD | PRIMARY | test | t1 | 3 | GRANTED | 93 |
- | 140547538324696:11:4:7:140547436102528 | 2719 | X,REC_NOT_GAP | RECORD | PRIMARY | test | t1 | 5 | GRANTED | 93 |
- | 140547538324696:11:4:8:140547436102528 | 2719 | X,REC_NOT_GAP | RECORD | PRIMARY | test | t1 | 6 | GRANTED | 93 |
- | 140547538324696:11:4:9:140547436102528 | 2719 | X,REC_NOT_GAP | RECORD | PRIMARY | test | t1 | 9 | GRANTED | 93 |
- | 140547538324696:11:4:10:140547436102528 | 2719 | X,REC_NOT_GAP | RECORD | PRIMARY | test | t1 | 12 | GRANTED | 93 |
- | 140547538324696:11:4:11:140547436102528 | 2719 | X,REC_NOT_GAP | RECORD | PRIMARY | test | t1 | 15 | GRANTED | 93 |
- +-----------------------------------------+-----------------------+---------------+-----------+------------+---------------+-------------+-----------+-------------+-----------+
- 7 rows in set (0.00 sec)
复制代码 session2 实行 select * from t1 where type_id=3 for update; 后,由于 type_id 字段没有索引,实行全表扫描,从第一行开始,但是此时 session1 获得的行锁中第一个为 id=3 的记录,因此 session2 获得了 id=1 和 id=2 的行锁,但是当扫描到第三行时(即 id=3 这条记录),这一行已经被 session1 持有了锁,因此 session2 被阻塞了,出现了锁等待。
因此 session2 最后获取了意向排他锁(IX) 和 3 条行锁(X,REC_NOT_GAP),id=3 这一行的锁被阻塞获取不到。- greatsql> select ENGINE_LOCK_ID,ENGINE_TRANSACTION_ID,LOCK_MODE,LOCK_TYPE,INDEX_NAME,OBJECT_SCHEMA,OBJECT_NAME,LOCK_DATA,LOCK_STATUS,THREAD_ID from performance_schema.data_locks;
- +-----------------------------------------+-----------------------+---------------+-----------+------------+---------------+-------------+-----------+-------------+-----------+
- | ENGINE_LOCK_ID | ENGINE_TRANSACTION_ID | LOCK_MODE | LOCK_TYPE | INDEX_NAME | OBJECT_SCHEMA | OBJECT_NAME | LOCK_DATA | LOCK_STATUS | THREAD_ID |
- +-----------------------------------------+-----------------------+---------------+-----------+------------+---------------+-------------+-----------+-------------+-----------+
- | 140547538325504:1073:140547436111664 | 2726 | IX | TABLE | NULL | test | t1 | NULL | GRANTED | 94 |
- | 140547538325504:11:4:2:140547436108560 | 2726 | X,REC_NOT_GAP | RECORD | PRIMARY | test | t1 | 1 | GRANTED | 94 |
- | 140547538325504:11:4:3:140547436108560 | 2726 | X,REC_NOT_GAP | RECORD | PRIMARY | test | t1 | 2 | GRANTED | 94 |
- | 140547538325504:11:4:6:140547436108904 | 2726 | X,REC_NOT_GAP | RECORD | PRIMARY | test | t1 | 3 | WAITING | 94 |
- | 140547538324696:1073:140547436105584 | 2719 | IX | TABLE | NULL | test | t1 | NULL | GRANTED | 93 |
- | 140547538324696:11:4:6:140547436102528 | 2719 | X,REC_NOT_GAP | RECORD | PRIMARY | test | t1 | 3 | GRANTED | 93 |
- | 140547538324696:11:4:7:140547436102528 | 2719 | X,REC_NOT_GAP | RECORD | PRIMARY | test | t1 | 5 | GRANTED | 93 |
- | 140547538324696:11:4:8:140547436102528 | 2719 | X,REC_NOT_GAP | RECORD | PRIMARY | test | t1 | 6 | GRANTED | 93 |
- | 140547538324696:11:4:9:140547436102528 | 2719 | X,REC_NOT_GAP | RECORD | PRIMARY | test | t1 | 9 | GRANTED | 93 |
- | 140547538324696:11:4:10:140547436102528 | 2719 | X,REC_NOT_GAP | RECORD | PRIMARY | test | t1 | 12 | GRANTED | 93 |
- | 140547538324696:11:4:11:140547436102528 | 2719 | X,REC_NOT_GAP | RECORD | PRIMARY | test | t1 | 15 | GRANTED | 93 |
- +-----------------------------------------+-----------------------+---------------+-----------+------------+---------------+-------------+-----------+-------------+-----------+
- 11 rows in set (0.00 sec)
复制代码 session2 实行 delete from t1 where type_id=3; 后,由于 type_id 字段没有索引,实行全表扫描,从第一行开始,但是此时 session1 获得的行锁中第一个为 id=3 的记录,因此 session2 获得了 id=1 和 id=2 的行锁,但是当扫描到第三行时(即 id=3 这条记录),这一行已经被 session1 持有了锁,因此 session2 被阻塞了,出现了锁等待。
因此 session2 最后获取了意向排他锁(IX) 和 3 条行锁(X,REC_NOT_GAP),id=3 这一行的锁被阻塞获取不到。- greatsql> select ENGINE_LOCK_ID,ENGINE_TRANSACTION_ID,LOCK_MODE,LOCK_TYPE,INDEX_NAME,OBJECT_SCHEMA,OBJECT_NAME,LOCK_DATA,LOCK_STATUS,THREAD_ID from performance_schema.data_locks;
- +-----------------------------------------+-----------------------+---------------+-----------+------------+---------------+-------------+-----------+-------------+-----------+
- | ENGINE_LOCK_ID | ENGINE_TRANSACTION_ID | LOCK_MODE | LOCK_TYPE | INDEX_NAME | OBJECT_SCHEMA | OBJECT_NAME | LOCK_DATA | LOCK_STATUS | THREAD_ID |
- +-----------------------------------------+-----------------------+---------------+-----------+------------+---------------+-------------+-----------+-------------+-----------+
- | 140547538325504:1073:140547436111664 | 2727 | IX | TABLE | NULL | test | t1 | NULL | GRANTED | 94 |
- | 140547538325504:11:4:2:140547436108560 | 2727 | X,REC_NOT_GAP | RECORD | PRIMARY | test | t1 | 1 | GRANTED | 94 |
- | 140547538325504:11:4:3:140547436108560 | 2727 | X,REC_NOT_GAP | RECORD | PRIMARY | test | t1 | 2 | GRANTED | 94 |
- | 140547538325504:11:4:6:140547436108904 | 2727 | X,REC_NOT_GAP | RECORD | PRIMARY | test | t1 | 3 | WAITING | 94 |
- | 140547538324696:1073:140547436105584 | 2719 | IX | TABLE | NULL | test | t1 | NULL | GRANTED | 93 |
- | 140547538324696:11:4:6:140547436102528 | 2719 | X,REC_NOT_GAP | RECORD | PRIMARY | test | t1 | 3 | GRANTED | 93 |
- | 140547538324696:11:4:7:140547436102528 | 2719 | X,REC_NOT_GAP | RECORD | PRIMARY | test | t1 | 5 | GRANTED | 93 |
- | 140547538324696:11:4:8:140547436102528 | 2719 | X,REC_NOT_GAP | RECORD | PRIMARY | test | t1 | 6 | GRANTED | 93 |
- | 140547538324696:11:4:9:140547436102528 | 2719 | X,REC_NOT_GAP | RECORD | PRIMARY | test | t1 | 9 | GRANTED | 93 |
- | 140547538324696:11:4:10:140547436102528 | 2719 | X,REC_NOT_GAP | RECORD | PRIMARY | test | t1 | 12 | GRANTED | 93 |
- | 140547538324696:11:4:11:140547436102528 | 2719 | X,REC_NOT_GAP | RECORD | PRIMARY | test | t1 | 15 | GRANTED | 93 |
- +-----------------------------------------+-----------------------+---------------+-----------+------------+---------------+-------------+-----------+-------------+-----------+
- 11 rows in set (0.00 sec)
复制代码 讲到这里,不得不说一动手册权势巨子解释了,在 GreatSQL READ COMMITTED 隔离级别下,手册是这样解释的
对于 update 和 delete 操纵 innodb 仅仅会锁住更新和删除行,在 GreatSQL 中根据 where 条件搜索后,会将不满意条件行的行锁进行释放,这样可以很好的降低死锁发生的概率,但也仍旧可能发生。
Using READ COMMITTED has additional effects:
- For UPDATE or DELETE statements, InnoDB holds locks only for rows that it updates or deletes. Record locks for nonmatching rows are released after MySQL has evaluated the WHERE condition. This greatly reduces the probability of deadlocks, but they can still happen.
- For UPDATE statements, if a row is already locked, InnoDB performs a “semi-consistent” read, returning the latest committed version to MySQL so that MySQL can determine whether the row matches the WHERE condition of the UPDATE. If the row matches (must be updated), MySQL reads the row again and this time InnoDB either locks it or waits for a lock on it.
手册也说明了,对于 update 操纵,假如某行被锁后,innodb 会实行 semi-consistent(半一致读)操纵,将最新提交的版本返回给 GreatSQL ,以便 GreatSQL 来判断该行是否符合 update 的 where 条件。假如该行匹配(必须更新),GreatSQL 会再次读取该行,而且这一次 innodb 要么锁定它,要么等待它上的锁。
这也就将要说的 session2 实行 update t1 set tpye_id=2 where type=3; 后为什么成功了。
session2 实行 update 后,innodb 会进行半一致读,获取每一行的最新提交版本,然后通过 where 条件过滤,判断该行是否能被锁定,如下图,对于 id=1,id=2 的记录符合 where 条件,可以获取到行锁,对于 id=3 的记录并不符合 where 条件记录,所以 session2 对 id=3 的行不加锁,也就说和 session1 持有的 id=3 的行锁并不冲突。继续后续扫描,对于 id=4 的记录符合 where 条件,可以获取到行锁。直到完成全部的扫描和加锁。- greatsql> select ENGINE_LOCK_ID,ENGINE_TRANSACTION_ID,LOCK_MODE,LOCK_TYPE,INDEX_NAME,OBJECT_SCHEMA,OBJECT_NAME,LOCK_DATA,LOCK_STATUS,THREAD_ID from performance_schema.data_locks;
- +-----------------------------------------+-----------------------+---------------+-----------+------------+---------------+-------------+-----------+-------------+-----------+
- | ENGINE_LOCK_ID | ENGINE_TRANSACTION_ID | LOCK_MODE | LOCK_TYPE | INDEX_NAME | OBJECT_SCHEMA | OBJECT_NAME | LOCK_DATA | LOCK_STATUS | THREAD_ID |
- +-----------------------------------------+-----------------------+---------------+-----------+------------+---------------+-------------+-----------+-------------+-----------+
- | 140547538325504:1073:140547436111664 | 2749 | IX | TABLE | NULL | test | t1 | NULL | GRANTED | 94 |
- | 140547538325504:11:4:2:140547436108560 | 2749 | X,REC_NOT_GAP | RECORD | PRIMARY | test | t1 | 1 | GRANTED | 94 |
- | 140547538325504:11:4:3:140547436108560 | 2749 | X,REC_NOT_GAP | RECORD | PRIMARY | test | t1 | 2 | GRANTED | 94 |
- | 140547538325504:11:4:4:140547436108560 | 2749 | X,REC_NOT_GAP | RECORD | PRIMARY | test | t1 | 4 | GRANTED | 94 |
- | 140547538324696:1073:140547436105584 | 2748 | IX | TABLE | NULL | test | t1 | NULL | GRANTED | 93 |
- | 140547538324696:11:4:6:140547436102528 | 2748 | X,REC_NOT_GAP | RECORD | PRIMARY | test | t1 | 3 | GRANTED | 93 |
- | 140547538324696:11:4:7:140547436102528 | 2748 | X,REC_NOT_GAP | RECORD | PRIMARY | test | t1 | 5 | GRANTED | 93 |
- | 140547538324696:11:4:8:140547436102528 | 2748 | X,REC_NOT_GAP | RECORD | PRIMARY | test | t1 | 6 | GRANTED | 93 |
- | 140547538324696:11:4:9:140547436102528 | 2748 | X,REC_NOT_GAP | RECORD | PRIMARY | test | t1 | 9 | GRANTED | 93 |
- | 140547538324696:11:4:10:140547436102528 | 2748 | X,REC_NOT_GAP | RECORD | PRIMARY | test | t1 | 12 | GRANTED | 93 |
- | 140547538324696:11:4:11:140547436102528 | 2748 | X,REC_NOT_GAP | RECORD | PRIMARY | test | t1 | 15 | GRANTED | 93 |
- +-----------------------------------------+-----------------------+---------------+-----------+------------+---------------+-------------+-----------+-------------+-----------+
- 11 rows in set (0.00 sec)
复制代码 GreatSQL 就是利用 semi-consistent(半一致读)操纵对 update 进行的优化,从而进步并发性。
Enjoy GreatSQL
免责声明:如果侵犯了您的权益,请联系站长,我们会及时删除侵权内容,谢谢合作!更多信息从访问主页:qidao123.com:ToB企服之家,中国第一个企服评测及商务社交产业平台。 |