锁列表
- 共享与列排他锁
- 意向锁
- 记录锁
- 间隙锁
- Next-Key锁
- 插入意向锁
- AUTO-INC锁
这次我们只来讨论和实验共享和排他锁。
共享与排他锁
共享和排他锁也可以称为共享读锁和排他写锁，分别用大写字母S和X表示，S和X分别对应为shared（S） locks 和exclusive（X） locks。
- 共享锁允许事务持有进行读行
- 排他锁允许事务持有该锁进行更新或删除行
共享读锁允许多个事务持有，排他写锁只允许一个事务持有。
如果一个事务持有排他写锁，那么其他事务就只有等待持有该锁的事务释放锁，如果事务等待锁时间过长，等待锁的事务就会被回滚。
例如：两个事务同时对同一行进行数据更新：
事务A：
begin;
update sys_user set name_pinyin='wangwu1' where id=17;事务B：
begin;
update sys_user set name_pinyin='wangwu1' where id=17;如果事务A先执行update语句，那么事务A先获取到id=17行的排他写锁，此时如果事务B执行update语句，事务B就只有等待事务A释放锁。如果事务A没有在事务B等待锁超时之前释放锁的话，那么事务B就会回滚，如下图：
事务B被回滚：
在事务B等待锁的过程中，我们使用以下SQL查询锁情况：
SELECT r.trx_id AS waiting_trx_id, r.trx_mysql_thread_id AS waiting_thread, LEFT(r.trx_query, 20) AS waiting_query
, concat(concat(lw.lock_type, ' '), lw.lock_mode) AS waiting_for_lock
, b.trx_id AS blocking_trx_id, b.trx_mysql_thread_id AS blocking_thread, LEFT(b.trx_query, 20) AS blocking_query
, concat(concat(lb.lock_type, ' '), lb.lock_mode) AS blocking_lock
FROM performance_schema.data_lock_waits w
INNER JOIN information_schema.innodb_trx b ON b.trx_id = w.BLOCKING_ENGINE_TRANSACTION_ID
INNER JOIN information_schema.innodb_trx r ON r.trx_id = w.REQUESTING_ENGINE_TRANSACTION_ID
INNER JOIN performance_schema.data_locks lw ON lw.ENGINE_TRANSACTION_ID = r.trx_id
INNER JOIN performance_schema.data_locks lb ON lb.ENGINE_TRANSACTION_ID = b.trx_id;得到如下输出：
使用show engine innodb status输出如下：
show engine innodb status；
---TRANSACTION 126422095, ACTIVE 39 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 28, OS thread handle 139734349227776, query id 26244 192.168.1.83 root updating
update sys_user set name_pinyin='wangwu1' where id=17
------- TRX HAS BEEN WAITING 39 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 484 page no 7 n bits 328 index PRIMARY of table `employees`.`sys_user` trx id 126422095 lock_mode X locks rec but not gap waiting
Record lock, heap no 257
------------------注意
- Innodb在可重复读的事务隔离级别下使用MVCC下对SELECT是不加锁的。