Mysql 事务锁

天道酬勤-明天会更好

已于 2022-05-08 23:30:28 修改

阅读量559

点赞数

文章标签： mysql

于 2022-03-20 11:43:13 首次发布

本文链接：https://blog.csdn.net/u010648194/article/details/123609801

版权

一、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的博客-CSDN博客_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

天道酬勤-明天会更好

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
Mysql 事务锁

一、InnoDB锁的类型① S行级共享锁S锁简单理解就是：运行 select * from l where id = 1; innoDB就会对id = 1的这行数据进行加S锁;② X行级排它锁对数据进行修改时，就会对修改的行加X锁。排它的意思就是：在同一时间内，两个事务不能同时对一条数据进行修改操作，这也是事务的四个特性中的隔离性。③ IS 意向共享锁④ IX 意向排它锁怎么理解意向共享锁和意向排它锁?意向锁是揭示下一层级请求的锁的类型，InnoDB存储引擎中的意向锁都.
复制链接

扫一扫