记一个引起MYSQL死锁Deadlock found when trying to get lock； try restarting transaction的例子 及 Share Locks和排它锁

一、记一个引起MYSQL死锁Deadlock found when trying to get lock; try restarting transaction的例子

    今天在尝试MYSQL事务的时候，这种情况总会引起死锁，不知道为什么，我使用的测试MYSQL表的创建SQL如下:

CREATE TABLE `user` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `age` int(10) unsigned NOT NULL DEFAULT '20',
  `nowtime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8

我已经通过命令修改全局的事务隔离级别:

SET @@global.tx_isolation=\'SERIALIZABLE\';
show variables like '%tx_isolation%';

其中在一个终端执行SQL命令A：

show variables like '%tx_isolation%';
set autocommit=0;
START TRANSACTION;
	select user.*,SLEEP(3) as tt from user where id=4;
	update user set age=200 where id=4;
#ROLLBACK;
COMMIT;
show WARNINGS;

在另外一个终端执行命令B：

show variables like '%tx_isolation%';
set autocommit=0;
START TRANSACTION;
  select * from user  where id=4;
	update user set age=age-5 where id=4;
COMMIT;

    执行命令A之后（因为sleep(3)的作用，会用一些时间），在此期间执行B。验证事务的SERIALIZABLE级别时的顺序执行。测试时也能看到事务是顺序执行的，即B事务必须等到A事务执行完毕才执行结束。

    两个命令分别执行(非同时)是没有问题的，但是先执行A，再执行B。B等待A执行完成后再执行，

    最后会报错：Deadlock found when trying to get lock; try restarting transaction。但不清楚这样为什么会报这个错误，试了一下也不是autocommit的值的问题。目前也没时间去排查吧。先留在这里做个记录。

#其它几个辅助的查询语句

#1,查询系统表中的锁信息
select * from information_schema.INNODB_LOCK_WAITS;
select * from information_schema.INNODB_LOCKS;
select * from information_schema.INNODB_TRX;
#发生死锁时查询死锁发生的SQL和时间
show engine innodb status;
#显示当前查询
show full processlist;
#查询innodb的锁等待超时配置
show VARIABLES like '%innodb_lock_wait_timeout%';

#。 本文 publish:April 13, 2017 -Thursday 的相关评论转载至此：

Level : 1.    User：us20190921182919-303    Time：2019-09-21 18:37:14
因为两个select语句都存在读锁，所以在执行update的时候，写锁会阻塞

Level : 2.    User：us20190316202924-932    Time：2019-09-24 21:40:58
[04007]，嗯。SERIALIZABLE隔离级别死锁问题：

Level : 3.    User：us20231207153702-897    Time：2023-12-07 16:14:35
[04007] 由多个事务同时访问同一数据，其中一个事务获取了排他锁，而另一个事务也想获取该排他锁，而此时第二个事务无法获取该数据的排他锁，从而导致死锁的产生。SERIALIZABLE 级别并不能避免死锁的产生。

二、关于mysql数据库innodb引擎的共享锁Share Locks和排它锁Exclusive Locks

1. MySQL共有三种锁的级别：页级、表级、行级。

    MyISAM和MEMORY存储引擎采用的是表级锁（table-level locking）
    BDB存储引擎采用的是页面锁（page-levellocking），但也支持表级锁；
    InnoDB存储引擎既支持行级锁（row-level locking），也支持表级锁，但默认情况下是采用行级锁。

我这里就只看innodb引擎的两个锁：共享锁(Share Locks)和排它锁(Exclusive Locks)

2. SELECT ... FOR UPDATE

    这即是排它锁（Exclusive Locks，即X锁）的实现，其在执行后会阻止其它对此行记录的for update读取和修改等操作。必须等到此锁释放其它业务才能操作。但可以允许普通的select操作，因为普通的select查询的是数据快照。这种操作就是悲观锁，可以防止出现问题。

3. select ... LOCK IN SHARE MODE

    这即是共享锁（Share Locks，即S锁）的实现，执行select ... LOCK IN SHARE MODE能读取最新的数据，select结果的全部行都会加行锁（如果这些行已被其它事务先加锁，如for update，则等待锁释放后再加锁），其它事务不能select .... for update、update和delete操作这些行记录，直到锁释放。

select ... FOR UPDATE同时只能有一个在语句执行，另一个会阻塞；select ... LOCK IN SHARE MODE可以多个同时执行（这也是和for update最大的区别）

select ... FOR UPDATE不会出现数据更新丢失等问题，select ... LOCK IN SHARE MODE可能会出现数据更新丢失。

    总之我觉得高并发下要保证不会出问题，使用for update是个比较好的方案。我一直也是使用的这个方案，当然某些情况下也会使用乐观锁。

    另外select ... lock in share mode运行时会在正在读取的行上加share mode lock.所以select ... lock in share mode在运行期间尚未读取某一行时，如果在这时对某一行进行更新是可以的。