mysql的锁

最新推荐文章于 2022-03-08 19:54:00 发布

朝闻道-夕死可矣

最新推荐文章于 2022-03-08 19:54:00 发布

阅读量469

点赞数

分类专栏： MySQL

本文链接：https://blog.csdn.net/jc_benben/article/details/68926463

版权

MySQL 专栏收录该内容

63 篇文章 1 订阅

订阅专栏

表/行-锁，支持情况

表级锁(table-level locking)：MyISAM和MEMORY存储引擎
行级锁(row-level locking) ：InnoDB存储引擎

表锁和行锁区别：

表级锁：开销小,加锁快;不会出现死锁;锁定粒度大,发生锁冲突的概率最高,并发度最低
行级锁：开销大,加锁慢;会出现死锁;锁定粒度最小,发生锁冲突的概率最低,并发度也最高

共享/排他锁区别：

共享锁又称读锁，是读取操作创建的锁。其他用户可以并发读取数据，但任何事务都不能对数据进行修改(获取数据上的排他锁)，直到已释放所有共享锁
排他锁又称写锁，如果事务T对数据A加上排他锁后，则其他事务不能再对A加任任何类型的封锁。获准排他锁的事务既能读数据，又能修改数据

Mysiam锁模式-MyISAM在执行查询语句(SELECT)前，会自动给涉及的所有表加读锁，在执行更新操作(UPDATE、DELETE、INSERT等)前，会自动给涉及的表加写锁
a、对MyISAM表的读操作(加读锁)，不会阻塞其他进程对同一表的读请求，但会阻塞对同一表的写请求。只有当读锁释放后，才会执行其它进程的写操作
b、对MyISAM表的写操作(加写锁)，会阻塞其他进程对同一表的读和写操作，只有当写锁释放后，才会执行其它进程的读写操作

InnoDB实现了两种标准行级锁，一种是共享锁(shared locks，S锁)，另一种是独占锁，或者叫排它锁(exclusive locks，X锁),另外mysql的 InnoDB引擎支持行锁，与Oracle不同，mysql的行锁是通过索引加载的，即是行锁是加在索引响应的行上的，要是对应的SQL语句没有走索引，则可能会锁所有行

另外，注意，InnoDB还有意向锁（Intention Locks），innodb锁模式-意向锁是InnoDB自动加的，不需要用户干预。对于insert、update、delete，InnoDB会自动给涉及的数据加排他锁(X)，因此事务要获取某个表上的S锁和X锁之前，必须先分别获取对应的IS锁和IX锁

意向共享锁（IS）：表示事务准备给数据行加入共享锁(shared locks，S锁)，也就是说一个数据行加共享锁前必须先取得该表的IS锁
意向排他锁（IX）：类似上面，表示事务准备给数据行加入排他锁(exclusive locks，X锁),，说明事务在一个数据行加排他锁前必须先取得该表的IX锁

InnoDB锁的兼容性矩阵：

	排它锁（X）	意向排它锁（IX）	共享锁（S）	意向共享锁（IS）
排它锁（X）	N	N	N	N
意向排它锁（IX）	N	Y	N	Y
共享锁（S）	N	N	Y	Y
意向共享锁（IS）	N	Y	Y	Y

对于一般的Select语句，InnoDB不会加任何锁，事务可以通过以下语句给显示加共享锁或排他锁。
共享锁： SELECT ... LOCK IN SHARE MODE; 排他锁： SELECT ... FOR UPDATE;

SELECT ... LOCK IN SHARE MODE走的是IS锁(意向共享锁)，即在符合条件的rows上都加了共享锁，这样的话，其他session可以读取这些记录，也可以继续添加IS锁，但是无法修改这些记录直到你这个加锁的session执行完成(否则直接锁等待超时innodb_lock_wait_timeout)，也就是只有其他的session没有share锁的时候才可以修改记录。

SELECT ... FOR UPDATE 走的是IX锁(意向排它锁)，即在符合条件的rows上都加了排它锁，其他session也就无法在这些记录上添加任何的S锁或X锁。for update之后并不会阻塞其他session的快照读取操作(快照读并不需要加锁)，除了select ...lock in share mode和select ... for update这种显示加锁的查询操作。
注意，如果autocommit=1的时候需要显示指定begin（默认开启事务操作执行锁定操作，否则当成一条事务完成了，也不构成锁事实）

表级语法格式
LOCK TABLES
tbl_name [AS alias] {READ [LOCAL] | [LOW_PRIORITY] WRITE}
[, tbl_name [AS alias] {READ [LOCAL] | [LOW_PRIORITY] WRITE}] ...

配套的解锁语句
UNLOCK TABLES
注意，表级锁必须配对使用，跟是否使用事务没关系。

mysql发现 lock tables 命令的时候,会将带有锁标记的表(table) 带入“封闭空间”,直到出现 unlock tables 命令或线程结束, 才关闭“封闭空间”。

锁查看、强制解锁方法

使用lock tables t1 read;查看表状态

mysql> lock tables t1 read;
Query OK, 0 rows affected (0.00 sec)

mysql>  show open tables from t like '%t1'\G;
*************************** 1. row ***************************
   Database: t
      Table: t1
     In_use: 1
Name_locked: 0
1 row in set (0.00 sec)

再领一个session再次执行，查看In_use变为2,但是执行lock tables t1 write时候会出现互斥现象，也就是如果有其他人锁住，就不行再次获取锁
如果是InnoDB可以使用:

show engine innodb status \G;

或者查询锁语句：

SELECT r.trx_id waiting_trx_id,  
       r.trx_mysql_thread_id waiting_thread,
       r.trx_query waiting_query,
       b.trx_id blocking_trx_id,
       b.trx_mysql_thread_id blocking_thread,
       b.trx_query blocking_query
   FROM       information_schema.innodb_lock_waits w
   INNER JOIN information_schema.innodb_trx b  ON  
    b.trx_id = w.blocking_trx_id
  INNER JOIN information_schema.innodb_trx r  ON  
    r.trx_id = w.requesting_trx_id;

如果出现了：ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction，一般情况下是由于某个语句执行没有提交原故
1，查看隔离级别 select @@tx_isolation; -- 隔离级别详情看：https://blog.csdn.net/jc_benben/article/details/55258857
2，查看先当前库的线程情况
show processlist;-- 或者 show full processlist;或者
select id,command,time,state,info
from information_schema.processlist where user='XXX';
3，查看innodb的事务表INNODB_TRX
SELECT * FROM information_schema.INNODB_TRX\G;

mysql> SELECT * FROM information_schema.INNODB_TRX\G;
*************************** 1. row ***************************
                    trx_id: 11434407
                 trx_state: RUNNING
               trx_started: 2017-03-31 15:29:18
     trx_requested_lock_id: NULL
          trx_wait_started: NULL
                trx_weight: 1
       trx_mysql_thread_id: 606117
                 trx_query: SELECT * FROM information_schema.INNODB_TRX
       trx_operation_state: NULL
         trx_tables_in_use: 0
         trx_tables_locked: 1
          trx_lock_structs: 1
     trx_lock_memory_bytes: 1136
           trx_rows_locked: 1
         trx_rows_modified: 0
   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
*************************** 2. row ***************************
                    trx_id: 11434257
                 trx_state: RUNNING
               trx_started: 2017-03-31 15:28:45
     trx_requested_lock_id: NULL
          trx_wait_started: NULL
                trx_weight: 3
       trx_mysql_thread_id: 606114
                 trx_query: NULL
       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: 3
         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
2 rows in set (0.00 sec)

4，根据需要kill，
mysql> kill 606114

一些相关的查询：
-- 查询当前连接状态
status
-- 查看进程状态
show processlist;-- 或者
select id,command,time,state,info
from information_schema.processlist where user='XXX';
-- 查看表状态
show open tables from t like '%t1'\G;
-- 锁定状态
show status like '%lock%';
-- innodb引擎状态
show engine innodb status\G;
-- 查看超时状态

show variables like '%timeout%';

附：

innodb查看谁阻塞，谁等待，等待多久的查询

SELECT    
        R.TRX_ID WAITING_TRX_ID,    
        R.TRX_MYSQL_THREAD_ID WAITING_THREAD,    
        TIMESTAMPDIFF(    
            SECOND,    
            R.TRX_WAIT_STARTED,    
            CURRENT_TIMESTAMP    
        ) WAIT_TIME,    
        R.TRX_QUERY WAITING_QUERY,    
        L.LOCK_TABLE WAITING_TABLE_LOCK,    
        B.TRX_ID BLOCKING_TRX_ID,    
        B.TRX_MYSQL_THREAD_ID BLOCKING_THREAD,    
        SUBSTRING(    
            P. HOST,    
            1,    
            INSTR(P. HOST, ':') - 1    
        ) BLOCKING_HOST,    
        SUBSTRING(P. HOST, INSTR(P. HOST, ':') + 1) BLOCKING_PORT,    
        
    IF (P.COMMAND = 'SLEEP', P.TIME, 0) IDEL_IN_TRX,    
     B.TRX_QUERY BLOCKING_QUERY    
    FROM    
        INFORMATION_SCHEMA.INNODB_LOCK_WAITS W    
    INNER JOIN INFORMATION_SCHEMA.INNODB_TRX B ON B.TRX_ID = W.BLOCKING_TRX_ID    
    INNER JOIN INFORMATION_SCHEMA.INNODB_TRX R ON R.TRX_ID = W.REQUESTING_TRX_ID    
    INNER JOIN INFORMATION_SCHEMA.INNODB_LOCKS L ON W.REQUESTED_LOCK_ID = L.LOCK_ID   
    LEFT JOIN INFORMATION_SCHEMA. PROCESSLIST P ON P.ID = B.TRX_MYSQL_THREAD_ID    
    ORDER BY    
        WAIT_TIME DESC

innodb查询多少线程被哪些线程阻塞，

SELECT CONCAT('thread ' , b.trx_mysql_thread_id , ' from ' , p.host) AS who_blocks,  
           IF(p.command = "Sleep", p.time, 0 ) AS idle_in_trx,  
           MAX(TIMESTAMPDIFF(SECOND, r.trx_wait_started, NOW() )) AS max_wait_time,  
           COUNT(*) AS num_waiters  
    FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS AS w  
    INNER JOIN INFORMATION_SCHEMA.INNODB_TRX  AS b ON b.trx_id = w.blocking_trx_id  
    INNER JOIN INFORMATION_SCHEMA.INNODB_TRX  AS r ON b.trx_id = w.requesting_trx_id  
    LEFT  JOIN INFORMATION_SCHEMA.PROCESSLIST AS p ON p.id = b.trx_mysql_thread_id  
    GROUP BY who_blocks ORDER BY num_waiters DESC

InnoDB中不同的锁类型

常见的锁有Record锁，next-key锁，gap锁，插入意向锁，自增锁等

测试环境：

mysql> create table test(id int not null,xh int not null,primary key(id),key(xh)) engine=innodb charset=utf8mb4;
Query OK, 0 rows affected (0.01 sec)

mysql> insert into test values(1,1),(10,10);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

查看可以使用show engine innodb status;它可以显示最近几个事务的状态、查询和写入情况等信息

记录锁Record Locks

Record Lock是对索引记录的锁定。

X锁广义上是一种抽象意义的排它锁，即锁一般分为X模式和S模式，狭义上指row或者index上的锁，而Record锁是索引上的锁

记录锁有两种模式：S模式和X模式。例如：SELECT id FROM test WHERE id = 1 FOR UPDATE; 表示防止任何其他事务插入、更新或者删除id =1的行
记录锁始终只锁定索引。即使表没有建立索引，InnoDB也会创建一个隐藏的聚簇索引(隐藏的递增主键索引)，并使用此索引进行记录锁定

测试：

第一个session：

mysql> start transaction ;
Query OK, 0 rows affected (0.00 sec)

mysql> update test set id=2 where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

查看状态：

开启第二个session

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> update test set id=3 where id=1;

一直等待，并查看事务状态

看到 thread id=100185正在等待锁，锁状态标题是'事务正在等待获取锁'，描述中的lock_mode X locks rec but not gap就是本章节中的record记录锁，but not gap意思是只对record本身加锁，并不对间隙加锁

间隙锁

间隙锁作用在索引记录之间的间隔，又或者作用在第一个索引之前，最后一个索引之后的间隙。不包括索引本身。
例如：
SELECT c1 FROM test WHERE c1 BETWEEN 10 and 20 FOR UPDATE;
这条语句阻止其他事务插入10和20之间的数字，无论这个数字是否存在。间隙可以跨越0个，单个或多个索引值。
间隙锁是性能和并发权衡的产物，只存在于部分事务隔离级别。
select * from table where id=1;
唯一索引可以锁定一行，所以不需要间隙锁锁定。如果列没有索引或者具有非唯一索引，该语句会锁定当前索引前的间隙，所以如果where条件存在唯一键天骄，则只有record锁，没有gap锁
在同一个间隙上，不同的事务可以持有上述兼容/冲突表中冲突的两个锁。例如，事务T1现在持有一个间隙S锁，T2可以同时在同一个间隙上持有间隙X锁。
InnoDB中的间隙锁的唯一目的是防止其他事务插入间隙。间隙锁是可以共存的，一个事务占用的间隙锁不会阻止另一个事务获取同一个间隙上的间隙锁。
如果事务隔离级别改为RC，则间隙锁会被禁用，这也就是有些人会将事务隔离级别设置为RC

下面以非唯一索引为例测试

预存了两条数据，row(1,1)和row(10,10)，此时这个间隙应该是1<gap<10。我们先插入row(2,2)来验证下gap锁的存在，再插入row(0,0)来验证gap的边界
session1：

session2：

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into test(id,xh) values(2,2);

查看锁状态：

再开启一个事务，插入(0,0)

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into test(id,xh) values(0,0);
Query OK, 1 row affected (0.00 sec)

可以看到，没问题

Next-key锁

默认情况下，InnoDB的事务隔离级别为RR，系统参数innodb_locks_unsafe_for_binlog的值为false。InnoDB使用next-key锁对索引进行扫描和搜索，这样就读取不到幻象行，避免了幻读的发生。
幻读是指在同一事务下，连续执行两次同样的SQL语句，第二次的SQL语句可能会返回之前不存在的行
当查询的索引是唯一索引时，Next-key lock会进行优化，降级为Record Lock，此时Next-key lock仅仅作用在索引本身，而不会作用于gap和下一个索引上

表内容如下

可能的next-key锁区间：

第一个session：

第二个session：

资源等待中

查看锁：

锁定的区域是code=5前一个索引到它的间隙，以及next-key的区域。code=5 for update对索引的锁定用区间表示，gap锁锁定了(1,5)，record锁锁定了{5}索引记录，next-key锁锁住了(5,10]，也就是说整个(1,10]的区间被锁定了。由于是for update，所以这里的锁都是X锁，因此阻止了其他事务中带有冲突锁定的操作执行。

如果我们在第一个事务中，执行了code>8 for update，在扫描过程中，找到了code=10，此时就会锁住10之前的间隙(5到10之间的gap)，10本身(record)，和10之后的间隙(next-key)。此时另一个事务插入(6,6),(9,9)和(11,11)都是不被允许的

自增锁

自增锁(AUTO-INC Locks)是事务插入时自增列上特殊的表级别的锁。最简单的一种情况：如果一个事务正在向表中插入值，则任何其他事务必须等待，以便第一个事务插入的行接收连续的主键值。
我们一般把主键设置为AUTO_INCREMENT的列，默认情况下这个字段的值为0，InnoDB会在AUTO_INCREMENT修饰下的数据列所关联的索引末尾设置独占锁。
在访问自增计数器时，InnoDB使用自增锁，但是锁定仅仅持续到当前SQL语句的末尾，而不是整个事务的结束，毕竟自增锁是表级别的锁，如果长期锁定会大大降低数据库的性能。由于是表锁，在使用期间，其他会话无法插入表中

插入意向锁

插入意向锁在行插入之前由INSERT设置一种间隙锁，是意向排它锁的一种。在多事务同时写入不同数据至同一索引间隙的时，不会发生锁等待，事务之间互相不影响其他事务的完成，这和间隙锁的定义是一致的。
假设一个记录索引包含4和7，其他不同的事务分别插入5和6，此时只要行不冲突，插入意向锁不会互相等待，可以直接获取。参照锁兼容/冲突矩阵。
插入意向锁可以查看gap锁的第一个例子

innodb中的幻读

1，概念

幻读在mysql官网定义：

The so-called phantom problem occurs within a transaction when the same query produces different sets of rows at different times. For example, if a SELECT is executed twice, but returns a row the second time that was not returned the first time, the row is a “phantom”row

读有两种类型，当前读和快照读，当前读是普通读后加for update，又叫加锁读，阻塞读。这种读取需要读取最新版本并上锁，而快照读不上锁
mysql的快照读，使得在RR的隔离级别上在next-Key的作用区间内，制造了一个快照副本，这个副本是隔离的，无论副本对应的区间里的数据被其他事务如何修改，在当前事务中，取到的数据永远是副本中的数据。
RR级别下之所以可以读到之前版本的数据，是由于数据库的MVCC(Multi-Version Concurrency Control，多版本并发控制)

这里还有两个理解，只读事务和读写事务：

当前事务如果未发生更新操作(增删改)，快照版本会保持不变，多次查询读取的副本是同一个；
当前事务如果发生更新(增删改)，再次查询时，会刷新快照版本

2，RC级别下的幻读

RC(Read Commit)隔离级别可以避免脏读，事务内无法获取其他事务未提交的变更，但是由于能够读到已经提交的事务，因此会出现幻读和不重复读。也就是说，RC的快照读是读取最新版本数据，而RR的快照读是读取被next-key锁作用区域的副本

3，RR级别下能否避免幻读

隔离级别：

mysql> select @@global.transaction_isolation,@@session.transaction_isolation;
+--------------------------------+---------------------------------+
| @@global.transaction_isolation | @@session.transaction_isolation |
+--------------------------------+---------------------------------+
| REPEATABLE-READ                | REPEATABLE-READ                 |
+--------------------------------+---------------------------------+
1 row in set (0.01 sec)

实验：

时间序列	事务1	事务2
1	mysql> start transaction; Query OK, 0 rows affected (0.00 sec) mysql> select * from test where xh>7; +----+----+ \| id \| xh \| +----+----+ \| 10 \| 10 \| +----+----+ 1 row in set (0.00 sec)
2		mysql> start transaction; Query OK, 0 rows affected (0.00 sec) mysql> insert into test values(8,8); Query OK, 1 row affected (0.00 sec)
3	mysql> select * from test where xh>7; +----+----+ \| id \| xh \| +----+----+ \| 10 \| 10 \| +----+----+ 1 row in set (0.00 sec)

数据库中的数据已经改变，为什么会读不到？
这个就是之前提到的next-key lock锁定的副本。RC及以下级别才会读到已经提交的事务。更多的业务逻辑是希望在某段时间内或者某个特定的逻辑区间中，前后查询到的数据是一致的，当前事务是和其他事务隔离的。这也是数据库在设计实现时遵循的ACID原则

4，更新丢失

时间序列	事务1	事务2
1	mysql> start transaction; Query OK, 0 rows affected (0.00 sec) mysql> select * from test where xh>=9; +----+----+ \| id \| xh \| +----+----+ \| 10 \| 10 \| +----+----+ 1 row in set (0.00 sec)
2		mysql> start transaction; Query OK, 0 rows affected (0.00 sec) mysql> update test set id=11,xh=11 where id=10; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> commit; Query OK, 0 rows affected (0.10 sec)
3	mysql> select * from test where xh>=9; +----+----+ \| id \| xh \| +----+----+ \| 10 \| 10 \| +----+----+ 1 row in set (0.01 sec) mysql> update test set id=12,xh=12 where id=10; Query OK, 0 rows affected (0.00 sec) Rows matched: 0 Changed: 0 Warnings: 0 mysql> commit; Query OK, 0 rows affected (0.00 sec)

加粗的字体可以看出来，事务一的更新是无效的，尽管在这个事务里程序认为还存在(10,10)记录。事务一中更新之前的SELECT操作是快照读，所以读到了快照里的(10,10)，而UPDATE中的WHERE子句是当前读，取得是最新版本的数据，所以matched: 0 Changed: 0

发生此情况，引入常见的两种方式来解决该问题：

乐观锁：在UPDATE的WHERE子句中加入版本号信息来确定修改是否生效；
悲观锁：在UPDATE执行前，SELECT后面加上FOR UPDATE来给记录加锁，保证记录在UPDATE前不被修改。SELECT ... FOR UPDATE是加上了X锁，也可以通过SELECT ... LOCK IN SHARE MODE加上S锁，来防止其他事务对该行的修改。

无论是乐观锁还是悲观锁，使用的思想都是一致的，那就是当前读。乐观锁利用当前读判断是否是最新版本，悲观锁利用当前读锁定行

使用乐观锁时仍然需要非常谨慎，因为RR是可重复读的，一定不能在UPDATE之前先把版本号使用快照读获取出来

具体看：https://blog.csdn.net/jc_benben/article/details/82691778

不同语句下InnoDB枷锁情况

1，SELECT * FROM XXX

SELECT ... FROM是快照读取，除了SERIALIZABLE的事务隔离级别，该SQL语句执行时不会加任何锁。

SERIALIZABLE级别下，SELECT语句的执行会在遇到的索引记录上设置S模式的next-key锁。但是对于唯一索引，只锁定索引记录，而不会锁定gap

2,UPDATE

S锁读取(SELECT ... LOCK IN SHARE MODE)，X锁读取(SELECT ... FOR UPDATE)、更新UPDATE和删除DELETE这四类语句，采用的锁取决于搜索条件中使用的索引类型。

如果使用唯一索引，InnoDB仅锁定索引记录本身，不锁定间隙；
如果使用非唯一索引，或者未命中索引，InnoDB使用间隙锁或者next-key锁来锁定索引范围，这样就可以阻止其他事务插入锁定范围。

UPDATE语句

UPDATE ... WHERE ... 在搜索遇到的每条记录上设置一个独占的next-key锁，如果是唯一索引只锁定记录。

当UPDATE修改聚簇索引时，将对受影响的二级索引采用隐式锁，隐式锁是在索引中对二级索引的记录逻辑加锁，实际上不产生锁对象，不占用内存空间。

UPDATE可能会导致新的普通索引的插入。当新的索引插入之前，会首先执行一次重复索引检查。在重复检查和插入时，更新操作会对受影响的二级索引记录采用共享锁定(S锁)。

DELETE语句

DELETE FROM ... WHERE ... 在搜索遇到的每条记录上设置一个独占的next-key锁，如果是唯一索引只锁定记录

3,INSERT

插入行之前，会设置一种插入意向锁，插入意向锁表示插入的意图。如果其它事务在要插入的位置上设置了X锁，则无法获取插入意向锁，插入操作也因此阻塞。

INSERT在插入的行上设置X锁。该锁是一个Record锁，并不是next-key锁，即只锁定记录本身，不锁定间隙，因此不会阻止其他会话在这行记录前的间隙中插入新的记录

死锁场景

1，Duplicate key error

并发条件下，唯一键索引冲突可能会导致死锁，一般分为两种：一种是rollback引发，另一种是commit引发

1.1，rollback引起的

时间序列	事务1	事务2	事务3
1	mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> insert into test values(2,2); Query OK, 1 row affected (0.01 sec)
2		mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> insert into test values(2,2); 等待
3			mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> insert into test values(2,2); 等待
4	mysql> rollback; Query OK, 0 rows affected (0.16 sec)
5		ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
6			Query OK, 1 row affected (6.25 sec)

事务一执行回滚时，事务二和事务三发生了死锁。InnoDB的死锁检测一旦检测到死锁发生，会自动失败其中一个事务

死锁产生的原因是事务一插入记录时，对(2,2)记录加X锁，此时事务二和事务三插入数据时检测到了重复键错误，事务二和事务三要在这条索引记录上设置S锁，由于X锁的存在，S锁的获取被阻塞。
事务一回滚，由于S锁和S锁是可以兼容的，因此事务二和事务三都获得了这条记录的S锁。此时其中一个事务希望插入，则该事务期望在这条记录上加上X锁，然而另一个事务持有S锁，S锁和X锁互相是不兼容的，两个事务就开始互相等待对方的锁释放，造成了死锁。

事务二和事务三为什么会加S锁，而不是直接等待X锁？
事务一的insert语句加的是隐式锁(隐式的Record锁、X锁)，但是其他事务插入同一行记录时，出现了唯一键冲突，事务一的隐式锁升级为显示锁。
事务二和事务三在插入之前判断到了唯一键冲突，是因为插入前的重复索引检查，这次检查必须进行一次当前读，于是非唯一索引就会被加上S模式的next-key锁，唯一索引就被加上了S模式的Record锁

1.2，commit引发的Duplicate key死锁

时间序列	事务1	事务2	事务3
1	mysql> begin; Query OK, 0 rows affected (0.01 sec) mysql> delete from test where id=2; Query OK, 1 row affected (0.11 sec)
2		mysql> begin -> ; Query OK, 0 rows affected (0.00 sec) mysql> insert into test values(2,2); 等待
3			mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> insert into test values(2,2); 等待
4	mysql> commit; Query OK, 0 rows affected (0.07 sec)
5		ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
6			Query OK, 1 row affected (6.25 sec)

原理跟1.1，commit时候一样；在oracle中，将会体现等待现象，不会出现死锁

一条数据在插入时经过以下几个过程：

假设数据表test.test中存在(1,1)、(5,5)和(11,11)三条记录。

事务开启，尝试获取插入意向锁。例如，事务一执行了select * from test where id>8 for update，事务二要插入(9,9)，此时先要获取插入意向锁，由于事务一已经在对应的记录和间隙上加了X锁，因此事务二被阻塞，阻塞的原因是获取插入意向锁时被事务一的X锁阻塞。
获取意向锁之后，插入之前进行重复索引检查。重复索引检查为当前读，需要添加S锁。
如果是已经存在唯一索引，且索引未加锁。直接抛出Duplicate key的错误。如果存在唯一索引，且索引加锁，等待锁释放。
重复检查通过之后，加入X锁，插入记录

2，GAP与Insert Intention冲突引起死锁

表记录：

mysql> select * from test;
+----+----+
| id | xh |
+----+----+
|  1 |  1 |
|  5 |  5 |
| 11 | 11 |
+----+----+
3 rows in set (0.04 sec)

测试

时间序列	事务1	事务2
1	mysql> begin; Query OK, 0 rows affected (0.00 sec)
2		mysql> begin; Query OK, 0 rows affected (0.00 sec)
3	mysql> select * from test where xh=5 for update; +----+----+ \| id \| xh \| +----+----+ \| 5 \| 5 \| +----+----+ 1 row in set (0.00 sec)
4		mysql> select * from test where xh=11 for update; +----+----+ \| id \| xh \| +----+----+ \| 11 \| 11 \| +----+----+ 1 row in set (0.00 sec)
5	mysql> insert into test values(7,7);
6		mysql> insert into test values(7,7);
7	Query OK, 1 row affected (9.37 sec)
8		ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

使用show engine innodb status查看死锁状态。先后出现lock_mode X locks gap before rec insert intention waiting和lock_mode X locks gap before rec字眼，是gap锁和插入意向锁的冲突导致的死锁

两个事务中的select ... for update做了哪些加锁操作：
xh=5时，首先会获取xh=5的索引记录锁(Record锁)，根据之前gap锁的介绍，会在前一个索引和当前索引之间的间隙加锁，于是区间(1,5)之间被加上了X模式的gap锁。除此之外RR模式下，还会加next-key锁，于是区间(5,11]被加了next-key锁。
因此，xh=5的加锁范围是，区间(1,5)的gap锁，{5}索引Record锁，(5,11]的next-key锁。即区间(1,11)上都被加上了X模式的锁。
同理，xh=11的加锁范围是，区间(5,11)的gap锁，{11}索引Record锁，（11,+∞)的next-key锁。

由gap锁的特性，兼容矩阵中冲突的锁也可以被不同的事务同时加在一个间隙上。上述两个select ... for update语句出现了间隙锁的交集，xh=5的next-key锁和xh=11的gap锁有重叠的区域——(5,11)

死锁的成因
当事务一执行插入语句时，会先加X模式的插入意向锁，即兼容矩阵中的IX锁。但是由于插入意向锁要锁定的位置存在X模式的gap锁。兼容矩阵中IX和X锁是不兼容的，因此事务一的IX锁会等待事务二的gap锁释放。
事务二也执行插入语句，与事务一同样，事务二的插入意向锁IX锁会等待事务一的gap锁释放。
两个事务互相等待对方先释放锁，因此出现死锁

最后总结下，死锁都是由于某个区间上或者某一个记录上可以同时持有锁引起。
例如不同事务在同一个间隙gap上的锁不冲突；不同事务中，S锁可以阻塞X锁的获取，但是不会阻塞另一个事务获取该S锁。这样才会出现两个事务同时持有锁，并互相等待，最终导致死锁

增、删、改的操作都会进行一次当前读操作，以此获取最新版本的数据，并检测是否有重复的索引

朝闻道-夕死可矣

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
打赏
0
评论
mysql的锁

目录表/行-锁，支持情况锁查看、强制解锁方法InnoDB中不同的锁类型记录锁Record Locks间隙锁Next-key锁自增锁插入意向锁innodb中的幻读死锁场景表/行-锁，支持情况表级锁(table-level locking)：MyISAM和MEMORY存储引擎行级锁(row-level locking) ：InnoDB存储引擎表锁...
复制链接

扫一扫