一、InnoDB锁的类型
① S行级共享锁
S锁简单理解就是:运行 select * from l where id = 1; innoDB就会对id = 1的这行数据进行加S锁;
② X行级排它锁
对数据进行修改时,就会对修改的行加X锁。 排它的意思就是:在同一时间内,两个事务不能同时对一条数据进行修改操作,这也是事务的四个特性中的隔离性。
③ IS 意向共享锁
④ IX 意向排它锁
怎么理解意向共享锁和意向排它锁?
意向锁是揭示下一层级请求的锁的类型,InnoDB存储引擎中的意向锁都是表锁。
上面的图展示的数据库的层级,假如我们现在要对记录进行加锁,数据并不是直接向记录直接加锁,而是先从数据库-->表-->页-->记录 进行加锁的,意向锁就是揭示下一层级请求锁的类型。
栗子:假如我们现在要查询表1中,id = 1的数据
①: 从树的最顶端开始加锁,数据库A加一个IS锁
②: 我们的S锁是行锁,所以一直要往下找,一直找到树的叶子节点加锁,那么一直往下表1,页都会加入IS锁。因为意向锁是揭示下一层请求的锁。
③: 最后记录会加入S锁,数据库A、表1、页就会加入IS锁
上面的例子可能有人有疑惑了,不是说mysql只有有表锁和行锁吗,怎么会出现数据库锁和页锁了? 上面只是数据库加锁的流程,方便理解意向锁。如果我们把数据库锁和页锁去掉的话,是不是就能理解mysql的行锁和意向锁了。
⑤ X S IS IX的兼容性
⑥ AI自增锁
自增锁是针对自增字段的锁,在事务提交前释放,其他的锁在事务提交时才释放。
session A:begin; insert into l(a,b,c,d) select null,k,c,sleep(1) from sbtest.sbtest1 limit 100;
sessionB : beigin; insert into l(a,b,c,d) select null,1,1,1;
这时session b就会阻塞。通过show engine innoDB status;就会出现下面的mode auto-inc锁。
自增锁是在insert语句执行完锁就结束了,所以上面的例子是必须要让sql每次insert一次都需要暂停1s.
这里可以通过变量 innodb_autoinc_lock_mode = 2来取消上面的自增锁。innodb_autoinc_lock_mode默认是1
innodb_autoinc_lock_mode = 2;上面的锁就不会出现,缺点就是insert into l(a,b,c,d) select null,k,c,sleep(1) from sbtest.sbtest1 limit 100; 插入的100条数据的id不是连着的,因为中间可能有新的自增id进来。但是对唯一性不影响。所以建议innodb_autoinc_lock_mode = 2
二、查看锁
① 通过 show engine innoDB status;
这里介绍两个变量
innodb_lock_wait_timeout = 2 ##锁的等待时间默认是50S,需要改成2s或者3s
innodb_status_output_locks = ON ##show engine innoDB status; 展示锁的信息更全面
A) 事务中执行
建表
Create Table: CREATE TABLE `l` (
`a` int(11) NOT NULL,
`b` int(11) DEFAULT NULL,
`c` int(11) DEFAULT NULL,
`d` int(11) DEFAULT NULL,
PRIMARY KEY (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
a是主键
update l set a = 1 where a = 2; a是主键
B) 查看
show engine innodb status\G
------------
TRANSACTIONS
------------
Trx id counter 5443784
Purge done for trx's n:o < 5443779 undo n:o < 0 state: running but idle
History list length 2799
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 5443774, ACTIVE 290 sec
2 lock struct(s), heap size 1136, 1 row lock(s), undo log entries 2
MySQL thread id 18738, OS thread handle 140023637182208, query id 1717185 localhost root cleaning up
TABLE LOCK table `test`.`l` trx id 5443774 lock mode IX
RECORD LOCKS space id 135 page no 3 n bits 72 index PRIMARY of table `test`.`l` trx id 5443774 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 6; compact format; info bits 32
0: len 4; hex 80000002; asc ;;
1: len 6; hex 0000005310be; asc S ;;
2: len 7; hex 360000013203a1; asc 6 2 ;;
3: len 4; hex 80000004; asc ;;
4: len 4; hex 80000006; asc ;;
5: len 4; hex 80000008; asc ;;
heap no 2表示数据行在页中的逻辑位置,heap no 0和heap no 1表示页中的虚拟行,heap no 0表示min最小的行, heap no 1 表示最大的行,heap no >=2 的才是存储的数据。
每行记录都有一个row_id,tx_id,roll_pointer (回滚指针)。就是上面的的0,1,2行。
如果表中没有主键id,mysql会自动创建一个row_id。
C) 如果这时候新开一个窗口执行 begin; update l set a = 1 where a = 2; a是主键 就会阻塞。这就是锁等待。
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 18769, OS thread handle 140023637452544, query id 1729007 localhost root updating
update l set a = 1 where a = 2
------- TRX HAS BEEN WAITING 7 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 135 page no 3 n bits 72 index PRIMARY of table `test`.`l` trx id 5443805 lock_mode X locks rec but not gap waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 6; compact format; info bits 32
0: len 4; hex 80000002; asc ;;
1: len 6; hex 0000005310be; asc S ;;
2: len 7; hex 360000013203a1; asc 6 2 ;;
3: len 4; hex 80000004; asc ;;
4: len 4; hex 80000006; asc ;;
5: len 4; hex 80000008; asc ;;
这里的展示跟事务的锁展示差不多,这里是锁等待的信息,可以看到执行的sql语句等信息
② 通过表查询
可以在 information_schema库中的
select * from innodb_trx limit 1\G
如果出现锁,可以在 select * from innoDB_LOCKS 查看
出现了锁等待select * from INNODB_LOCK_WAITS
上面三张表组合就是
select "HOLD:",ph.id h_processid,trh.trx_id h_trx_id,trh.trx_started h_started,trh.trx_state h_state,lsh.lock_table h_table,lsh.lock_index h_index,lsh.lock_mode as h_lock_mode,lsh.lock_type h_lock_type,ph.user h_user,ph.host h_host,ph.command p_command,ph.info p_info,"WAIT:",pw.id w_processid,trw.trx_id w_trx_id,trw.trx_started w_started,trw.trx_state w_state,lsw.lock_table w_table,lsw.lock_index w_index,lsw.lock_mode w_lock_mode,lsw.lock_type w_lock_type,pw.user w_user,pw.host w_host,pw.command w_command,pw.info w_info from information_schema.INNODB_LOCK_WAITS lw
inner join information_schema.INNODB_TRX trh on trh.trx_id=lw.blocking_trx_id inner join information_schema.PROCESSLIST ph on trh.trx_mysql_thread_id=ph.id left join information_schema.INNODB_LOCKS lsh on lsh.lock_trx_id=trh.trx_id inner join information_schema.INNODB_TRX trw on trw.trx_id=lw.requesting_trx_id inner join information_schema.PROCESSLIST pw on trw.trx_mysql_thread_id=pw.id left join information_schema.INNODB_LOCKS lsw on lsw.lock_trx_id=trw.trx_id
mysql5.7 后的sys数据库中可以通过 select * from innodb_lock_waits; 查看等待锁
三、锁的算法
① 算法分类
A ) Record Lock 单个行记录上的锁
B) Gap Lock 锁定一个范围,但不包含记录本身
C) Next-key Lock Record Lock+Gap Lock,锁定一个范围,并且锁定记录本身
下面的测试都是
Create Table: CREATE TABLE `l` (
`a` int(11) NOT NULL AUTO_INCREMENT,
`b` int(11) DEFAULT NULL,
`c` int(11) DEFAULT NULL,
`d` int(11) DEFAULT NULL,
PRIMARY KEY (`a`),
UNIQUE KEY `u_index` (`b`),
KEY `c_index` (`c`)
)
(root@localhost) [test]> select * from l;
+---+------+------+------+
| a | b | c | d |
+---+------+------+------+
| 2 | 4 | 6 | 8 |
| 4 | 6 | 8 | 10 |
| 6 | 8 | 10 | 12 |
| 8 | 10 | 12 | 14 |
+---+------+------+------+
从上图可以看出:在RR(repatable read)隔离级别下
A)根据没有索引的列更新的话,会锁住全表。这也太恐怖了吧。所以以后查询最好走索引。
B) 普通索引不用范围查询更新数据时,会加next lock+Rap锁,开销还是比较大的。这样做的目的是为了防止幻读。
C) 如果线上的系统业务不是跟钱相关,可以把事务设置成 read-committed 类型,binlog_format = row; 这样mysql的效率会更高,因为不会有这么多的锁。
上面的结果怎么统计出来的,演示一个
begin; select * from l where c = 8 for update; show engine innoDB status;的结果
上面的普通索引的next-key和gap锁,其实区间还是需要包含主键的
上面的next-key锁应该是((2,6),(4,8)] ,案例就是,我们可以插入a=1,c=6的数据,但是不能插入a=3,c = 6的数据
这是因为普通索引里面表示的是(索引值,主键值) ,c = 6就是(6, 2). 那么next-key和Gap锁的区间也会根据上面的(6,2) 的形式来判断。
感兴趣的可以自己去模拟试试,看一下效果。
② 特殊情况
A) begin; select * from l where c=12 for update;
这里c = 12是最大值,下面锁住的的heap no 1表示是虚拟行,存储的是最大值max,所以这里的的锁就是next-key锁,区间是[12,+无穷大], c>=12的数据都插入不了
heap no = 0则表示存储的最小min,如果锁住的这行就表示负无穷大。
B) 查询没有数据的sql 加 for update
begin; select * from l where c = 7 for update;
这里加了一个gap 的锁,区间是[7, 8)
总的来说,next-key和gap锁都是为了解决幻读,大家在思考到底锁的区间问题,可以反着思考一下这样能不能幻读,如果能幻读肯定就不在这个区间。
四、read-view
① innoDB 中一行的记录
一行数据:row_id、 trx_id 、 roll_pointer, 数据列1, 数据列2
一行记录里面有2个虚拟的列,trx_id、roll_pointer;
trx_id表示事务id, roll_pointer 表示记录的上次被修改记录 undo log 的指针
② read-view
mysql中有一个存储当前活跃事务的list.list的储存下面的信息:
A)m_ids,当前有哪些事务正在执行,且还没有提交,这些事务的 id 就会存在这里;
B)min_trx_id,是指 m_ids 里最小的值;
C)max_trx_id,是指下一个要生成的事务 id。下一个要生成的事务 id 肯定比现在所有事务的 id 都大;
D) creator_trx_id,每开启一个事务都会生成一个 ReadView,而 creator_trx_id 就是这个开启的事务的 id。
创建一个事务,就会创建一个headView.这个headView会加载活跃事务LIST.
我们判断两个事务中的数据是否可见,则通过当前事务的trx_id和活跃中的事务id,进行对比,如果当前事务的trx_id<min_trx_id,则表示当前事务是在活跃事务中前执行的,则可以看到活跃事务中的数据,trx_id>min_trx_id,查询的话,则需要到undo log中去找小于trx_id小的版本数据。
所以这里有一个细节:RR级别时,加载headView是在begin;执行第一条sql语句时,才会加载headView。这是事务才开始真正生效。并不是begin;开始的。可以通过start transaction with consistent snapshot; 创建事务则立马加载headView。
这里需要注意的是:repeatable-read隔离级别是创建事务时,加载一次readView.而read-committed隔离级别则是事务中每次sql都会加载一次readView。 所以相对来说,RC在事务中有多个条查询sql会比较消耗资源。
参考文档:【数据库】MySQL的ReadView_thesprit的博客-博客_readview
五、两种死锁的情况
① 购物车的下订单的情况
A) T1 : begin; select * from l where a = 1 for update;
B) T2 : begin; select * from l where a = 2 for update;
C) T1: select * from l where a = 2 for update;
D) T2: select * from l where a = 1 for update; ##这一步就会产生死锁
因为 T1-->T2 T2-->T1
案例:
------------------------
LATEST DETECTED DEADLOCK
------------------------
2022-04-07 14:51:04 0x7faf34169700
*** (1) TRANSACTION:
TRANSACTION 5477139, ACTIVE 88 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s)
MySQL thread id 85523, OS thread handle 140390628333312, query id 7559867 localhost root statistics
select * from l where 3=3 AND a = 2 for update
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 145 page no 3 n bits 80 index PRIMARY of table `test`.`l` trx id 5477139 lock_mode X locks rec but not gap waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
0: len 4; hex 80000002; asc ;;
1: len 6; hex 000000531af6; asc S ;;
2: len 7; hex 2a000001182459; asc * $Y;;
3: len 4; hex 80000004; asc ;;
4: len 4; hex 80000004; asc ;;
5: len 4; hex 8000000a; asc ;;
*** (2) TRANSACTION:
TRANSACTION 5477141, ACTIVE 35 sec starting index read
mysql tables in use 1, locked 1
3 lock struct(s), heap size 1136, 2 row lock(s)
MySQL thread id 85535, OS thread handle 140390469900032, query id 7560034 localhost root statistics
select * from l where 4=4 AND a = 1 for update
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 145 page no 3 n bits 80 index PRIMARY of table `test`.`l` trx id 5477141 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
0: len 4; hex 80000002; asc ;;
1: len 6; hex 000000531af6; asc S ;;
2: len 7; hex 2a000001182459; asc * $Y;;
3: len 4; hex 80000004; asc ;;
4: len 4; hex 80000004; asc ;;
5: len 4; hex 8000000a; asc ;;
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 145 page no 3 n bits 80 index PRIMARY of table `test`.`l` trx id 5477141 lock_mode X locks rec but not gap waiting
Record lock, heap no 12 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
0: len 4; hex 80000001; asc ;;
1: len 6; hex 00000053930b; asc S ;;
2: len 7; hex fc0000013a0110; asc : ;;
3: len 4; hex 80000070; asc p;;
4: len 4; hex 80000001; asc ;;
5: len 4; hex 8000000b; asc ;;
解析:
产生死锁的sql 是select * from l where 4=4 AND a = 1 for update, 这条语句是没有执行成功的,
HOLDS THE LOCK表示目前已经跟其他事务锁住的锁,
WAITING FOR THIS LOCK TO BE GRANTED 表示等待的锁
事务id 5477139 等待 事务id 5477141, 在a = 2的行上面,
这时候 事务id 5477141 又等待 事务id 5477139 a = 1;所以产生了死锁。
② 唯一索引会产生S锁的情况
现在有数据1,3,5,7数据
唯一索引插入3这条数据需要走:
A) 找到 >3 的第一条记录next-recode
B) 判断这个记录next-recode是否有gap锁 有锁就直接阻塞
C) 没有gap锁,找到 小于 next-recode 的第一条记录,pre_recode。
D) 如果pre_recode=3没有锁的话,pre_recode = 3那么就是唯一性的问题,直接报错,
E) 如果如果pre_recode=3,这时pre_recode有锁的话, 那么插入3这个事务就会加入一个S锁
肯定有人会有疑问:第E步为什么不直接判断插入3重复了,是因为可能第一个事务会删除3才加锁,后面的插入3等第一个事务删除后,就可以直接添加。系统更智能,更能理解。
这里加入一个S锁的原因是:当我们根据下面的操作执行后,就会出现,如果不加S锁,则会同时添加两个3,就破坏了索引唯一性了。
死锁的产生:
当sessionA在T5执行了commit命令后,session B和session C的阻塞会同时激活,然后两个事务都有S锁等待,B等待C, C等待B 那么就会形成死锁。
大部分的死锁都是唯一索引产生的,所以大家看索引可以第一眼看一下索引是否是唯一的,有大部分都是上面的情况产生的。
六、 死锁的处理
① 查询是否有锁表
show open tables where in_use > 0; ##多个线程同时使用某个表
show open tables from test where in_use > 0; ## from 后面跟的是数据库名
show open tables where name_locked > 0; ##是否有被锁
② 可以通过 show processlist
查看有锁的进程id,进行杀掉
③ 通过查询information_schema.innodb_lock_waits表中的
(root@localhost) [test]> select * from information_schema.innodb_lock_waits\G
*************************** 1. row ***************************
requesting_trx_id: 1982228 ##请求事务的 ID
requested_lock_id: 1982228:93:3:4 事务所等待的锁定的 ID。可以和 INNODB_LOCKS 表 JOIN。
blocking_trx_id: 1982227 ##阻塞事务的 ID。
blocking_lock_id: 1982227:93:3:4 ###某一事务的锁的 ID,该事务阻塞了另一事务的运行。可以和 INNODB_LOCKS 表 JOIN。
1 row in set, 1 warning (0.00 sec)
上面的结果显示的是事务id 1982227阻塞了其他事务,所以我们要通过事务id找到对应的线程id,进行kill.
(root@localhost) [test]> select * from information_schema.innodb_trx where trx_id = 1982227\G
*************************** 1. row ***************************
trx_id: 1982227
trx_state: RUNNING
trx_started: 2022-05-08 08:50:43
trx_requested_lock_id: NULL
trx_wait_started: NULL
trx_weight: 3
trx_mysql_thread_id: 2
trx_query: select * from information_schema.innodb_trx where trx_id = 1982227
trx_operation_state: NULL
trx_tables_in_use: 0
trx_tables_locked: 1
trx_lock_structs: 2
trx_lock_memory_bytes: 1136
trx_rows_locked: 1
trx_rows_modified: 1
trx_concurrency_tickets: 0
trx_isolation_level: REPEATABLE READ
trx_unique_checks: 1
trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
trx_adaptive_hash_latched: 0
trx_adaptive_hash_timeout: 0
trx_is_read_only: 0
trx_autocommit_non_locking: 0
1 row in set (0.00 sec)
##通过这个sql找出阻塞的线程id
select * from information_schema.innodb_trx where trx_id IN (select blocking_trx_id from information_schema.innodb_lock_waits) \G