Mysql死锁

1、insert死锁

三个事务并发insert，且有唯一索引冲突的。

说法一：第一个事务拿到排他锁，两外两个等待拿共享锁，略。这个应该是其他版本或者错误的说法。

说法二：第一个事务插入后，提交前，另外两个事务会拿到共享锁，当第一个事务提交后另外两个报逐渐冲突结束，当第一个事务回滚后，另外两个事务都会尝试提交，此时他们需要升级为排他锁，单需要对方放弃共享锁，所以发生死锁。

自己实验：和第二个说法类似，但是死锁后数据库会立马选择其中一个事务回滚，另一个事务成功。

事务T1成功插入记录，并获得索引上的排他记录锁(LOCK_X | LOCK_REC_NOT_GAP)。
紧接着事务T2、T3也开始插入记录，请求排他插入意向锁(LOCK_X | LOCK_GAP | LOCK_INSERT_INTENTION)；但由于发生重复唯一键冲突，各自请求的排他记录锁(LOCK_X | LOCK_REC_NOT_GAP)转成共享记录锁(LOCK_S | LOCK_REC_NOT_GAP)。
T1回滚释放索引上的排他记录锁(LOCK_X | LOCK_REC_NOT_GAP)，T2和T3都要请求索引上的排他记录锁(LOCK_X |LOCK_REC_NOT_GAP)。
由于X锁与S锁互斥，T2和T3都等待对方释放S锁。
于是，死锁便产生了。
如果此场景下，只有两个事务T1与T2或者T1与T3，则不会引发如上死锁情况产生。

2、select后不存在则insert，insert完了update，如果存在直接update死锁（在一个事务内）

死锁日志：

LATEST DETECTED DEADLOCK

2019-12-19 11:16:33 7f91c7a69700
*** (1) TRANSACTION:
TRANSACTION 2634845227, ACTIVE 0.020 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1184, 2 row lock(s)
LOCK BLOCKING MySQL thread id: 7485083 block 7492468
MySQL thread id 7492468, OS thread handle 0x7f9183863700, query id 3359450887 10.111.3.123 pay_account_rw updating
update payment_asset_gift set amount = amount + 1, update_time = '2019-12-19 11:16:33.954' where user_id = '74a587472238458dbd1f66474c1def02' and gift_id = '746'
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 9784 page no 24489 n bits 344 index uniq_user_id_gid of table payment_account.payment_asset_gift trx id 2634845227 lock_mode X locks rec but not gap waiting
Record lock, heap no 271 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
0: len 30; hex 373461353837343732323338343538646264316636363437346331646566; asc 74a587472238458dbd1f66474c1def; (total 32 bytes);
1: len 3; hex 373436; asc 746;;
2: len 8; hex 0000000000277ead; asc '~ ;;>

*** (2) TRANSACTION:
TRANSACTION 2634845230, ACTIVE 0.013 sec starting index read
mysql tables in use 1, locked 1
3 lock struct(s), heap size 1184, 2 row lock(s)
MySQL thread id 7485083, OS thread handle 0x7f91c7a69700, query id 3359450888 10.111.3.123 pay_account_rw updating
update payment_asset_gift set amount = amount + 1, update_time = '2019-12-19 11:16:33.954' where user_id = '74a587472238458dbd1f66474c1def02' and gift_id = '746'
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 9784 page no 24489 n bits 344 index uniq_user_id_gid of table payment_account.payment_asset_gift trx id 2634845230 lock mode S
Record lock, heap no 271 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
0: len 30; hex 373461353837343732323338343538646264316636363437346331646566; asc 74a587472238458dbd1f66474c1def; (total 32 bytes);
1: len 3; hex 373436; asc 746;;
2: len 8; hex 0000000000277ead; asc '~ ;;>

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 9784 page no 24489 n bits 344 index uniq_user_id_gid of table payment_account.payment_asset_gift trx id 2634845230 lock_mode X locks rec but not gap waiting
Record lock, heap no 271 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
0: len 30; hex 373461353837343732323338343538646264316636363437346331646566; asc 74a587472238458dbd1f66474c1def; (total 32 bytes);
1: len 3; hex 373436; asc 746;;
2: len 8; hex 0000000000277ead; asc '~ ;;>

*** WE ROLL BACK TRANSACTION (2)

问题分析：当有一个事务拿到排他锁做插入的时候，上面两个事务拿到共享锁，第一个事务提交后，这两个事务报唯一键冲突，然后这个异常被吃掉直接update，但是此时这两个事务都有S锁，在update时候会争X锁，并且等对方释放S锁，产生死锁，mysql回滚其中一个事务。

解决方案：select和insert 独立出来一个事务因为会提交另外的事务也不会产生死锁
我们的问题应该是之前insert获取了共享锁，没释放在update时候阻塞了

Mysql锁

MVCC

mvcc替代了基于锁的并发控制，达到了读写不冲突，极大的提升了并发性能。在MVCC并发控制中，读操作可以分成两类：快照读 (snapshot read)与当前读 (current read)。快照读，读取的是记录的可见版本 (有可能是历史版本)，不用加锁。当前读，读取的是记录的最新版本，并且，当前读返回的记录，都会加上锁，保证其他事务不会再并发修改这条记录。

在一个支持MVCC并发控制的系统中，哪些读操作是快照读？哪些操作又是当前读呢？以MySQL InnoDB为例：

快照读：普通的select语句不加锁（有例外）。
当前读：特殊的select，insert、update和delete都属于当前读，会加锁。

当Update SQL被发给MySQL后，MySQL Server会根据where条件，读取第一条满足条件的记录，然后InnoDB引擎会将第一条记录返回，并加锁 (current read)。待MySQL Server收到这条加锁的记录之后，会再发起一个Update请求，更新这条记录。一条记录操作完成，再读取下一条记录，直至没有满足条件的记录为止。因此，Update操作内部，就包含了一个当前读。同理，Delete操作也一样。Insert操作会稍微有些不同，简单来说，就是Insert操作可能会触发Unique Key的冲突检查，也会进行一个当前读。

隔离级别

Read Uncommited
可以读取未提交记录。此隔离级别，不会使用，忽略。
Read Committed (RC)
快照读忽略，本文不考虑。
针对当前读，RC隔离级别保证对读取到的记录加锁 (记录锁)，存在幻读现象。
Repeatable Read (RR)
快照读忽略，本文不考虑。
针对当前读，RR隔离级别保证对读取到的记录加锁 (记录锁)，同时保证对读取的范围加锁，新的满足查询条件的记录不能够插入 (间隙锁)，不存在幻读现象。
Serializable
从MVCC并发控制退化为基于锁的并发控制。不区别快照读与当前读，所有的读操作均为当前读，读加读锁 (S锁)，写加写锁 (X锁)。

锁的类型

共享锁（S）、排他锁（X）、意向共享（IS）、意向排他（IX）

1，Record Lock：单个行记录上的锁。

2，Gap Lock：间隙锁，锁定一个范围，但不包括记录本身。GAP锁的目的，是为了防止同一事务的两次当前读，出现幻读的情况。

3，Next-Key Lock：1+2，锁定一个范围，并且锁定记录本身。对于行的查询，都是采用该方法，主要目的是解决幻读的问题。

因为InnoDB对于行的查询都是采用了Next-Key Lock的算法，锁定的不是单个值，而是一个范围（GAP）。上面索引值有1，3，5，8，11，其记录的GAP的区间如下：是一个左开右闭的空间

意向锁：innodb的意向锁主要用户多粒度的锁并存的情况。比如事务A要在一个表上加S锁，如果表中的一行已被事务B加了X锁，那么该锁的申请也应被阻塞。如果表中的数据很多，逐行检查锁标志的开销将很大，系统的性能将会受到影响。为了解决这个问题，可以在表级上引入新的锁类型来表示其所属行的加锁情况，这就引出了“意向锁”的概念。举个例子，如果表中记录1亿，事务A把其中有几条记录上了行锁了，这时事务B需要给这个表加表级锁，如果没有意向锁的话，那就要去表中查找这一亿条记录是否上锁了。如果存在意向锁，那么假如事务Ａ在更新一条记录之前，先加意向锁，再加Ｘ锁，事务B先检查该表上是否存在意向锁，存在的意向锁是否与自己准备加的锁冲突，如果有冲突，则等待直到事务Ａ释放，而无须逐条记录去检测。事务Ｂ更新表时，其实无须知道到底哪一行被锁了，它只要知道反正有一行被锁了就行了。

说白了意向锁的主要作用是处理行锁和表锁之间的矛盾，能够显示“某个事务正在某一行上持有了锁，或者准备去持有锁”

命令

1. 查看事务隔离级别

SELECT @@global.tx_isolation;

SELECT @@session.tx_isolation;

SELECT @@tx_isolation;

2. 设置隔离级别

SET [SESSION | GLOBAL] TRANSACTION ISOLATION LEVEL {READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE}

例如：set session transaction isolation level read uncommitted;

3. 查看auto_increment机制模式

show variables like ‘innodb_autoinc_lock_mode’;

4. 查看表状态

show table status like ‘plan_branch’\G;

show table status from test like ‘plan_branch’\G;

5. 查看SQL性能

show profiles

show profile for query 1;

6. 查看当前最新事务ID

每开启一个新事务，记录当前最新事务的id，可用于后续死锁分析。

show engine innodb status\G;

7. 查看事务锁等待状态情况

select from information_schema.innodb_locks;

select from information_schema.innodb_lock_waits;

select * from information_schema.innodb_trx;

8. 查看innodb状态(包含最近的死锁日志)

show engine innodb status;

引用：

http://hedengcheng.com/?p=771