Mysql事务以及锁原理、死锁及解决方法

事务

概念：https://blog.csdn.net/mashaokang1314/article/details/85194820

补充内容

自动提交

mysql> show variables like 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | ON    |
+---------------+-------+
1 row in set, 1 warning (0.00 sec)

默认情况下，如果我们不显示的使用start transaction或者begin语句开启一个事务，那么每条语句都算是一个独立的事务，这种特性称之为事物的自动提交。

关闭这种功能的方法：

• 显式使用start transaction 或者begin 语句开启一个事务，这样在本次事务提交活着回滚前会暂时关闭自动提交功能。
• 把系统变量autocommit设置为OFF，写完语句手动提交commit或者回滚rollback。

set autocommit=OFF

隐式提交

当我们开启一个事务时，或者把系统变量设置为off，事务就不会进行自动提交。但是如果我们输入某些语句之后就会悄悄地提交掉，这些因为某些特殊语句导致事务提交的情况称为隐式提交，包括：

• 定义或修改数据库对象的数据库定义语言DDL，数据库对象如库、表、视图、存储过程等。当我们使用如create、alter、drop等语句去修改数据库对象时，就会隐式的提交前边语句所属于的事务。
• 隐式使用或修改mysql数据库中的表：当我们使用alter user、create user、drop user、grant、rename user、set password等语句时；
• 事务的控制语句或关于锁定的语句：当我们在一个事务还没提交或者回滚时就又用开启了另一个事务，会隐式提交上一个事务。或者当前的autocommit系统变量值为off，我们把它变为on，也会隐式提交，或者使用lock tables、unlock tables等关于锁定的语句也会隐式提交前边语句所属的事务。
• 加载数据的语句：比如使用load data语句来批量往数据库中导入数据时，也会隐式提交前边语句所属的事务。
• 其他一些语句：使用analysis table、cache index等语句；

保存点
mysql提出了一个保存点savepoint的概念，就是在事务对应的数据库语句中打几个点，我们在调用rollback语句时可以指定会回滚到哪个点，而不是回到最初的原点：

保存点语法：
savepoint 保存点名称

回滚到指定保存点：
rollback [work] to [savepoint] 保存点名称
如果rollback语句后面不跟随保存点名称的话会直接回滚到事务执行之前的状态；

删除保存点：
release savepoint 保存点名称

隔离性详解

修改隔离级别

mysql> set session transaction isolation level read uncommitted;

查看隔离级别

mysql> select @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+

读未提交(READ UNCOMMITTED)
一个事务可以读取到其他事务还没有提交的数据，会出现脏读；

事务A：

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

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

事务B：

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

mysql> select * from abc;
+---+---+---+---+----+
| a | b | c | d | e  |
+---+---+---+---+----+
| 1 | 1 | 1 | 1 | 1  |
| 2 | 4 | 2 | 3 | 4  |
| 3 | 2 | 2 | 3 | 4  |
| 4 | 2 | 5 | 3 | 4  |
| 5 | 2 | 3 | 3 | 10 |
| 6 | 2 | 4 | 3 | 10 |
+---+---+---+---+----+
6 rows in set (0.00 sec)

事务A修改了记录的值还未提交，事务B就读到了修改后的记录，如果事务B拿了修改后的值去用，而这时事务A将之前的修改回滚掉。事务B拿到的就是脏数据，产生了脏读。

读已提交

一个事务只能读到另一个已提交的食物修改过的数据，并且其他事务每对该数据进行一次修改并提交后，该事务都能查到最新的值，会出现不可重读读、幻读。

事务A：

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

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

mysql> commit;
Query OK, 0 rows affected (0.11 sec)

事务B：

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

mysql> select * from abc;
+---+---+---+---+----+
| a | b | c | d | e  |
+---+---+---+---+----+
| 1 | 1 | 1 | 1 | 1  |
| 2 | 4 | 2 | 3 | 4  |
| 3 | 2 | 2 | 3 | 4  |
| 4 | 2 | 5 | 3 | 4  |
| 5 | 2 | 3 | 3 | 10 |
| 6 | 2 | 4 | 3 | 10 |
+---+---+---+---+----+
6 rows in set (0.00 sec)

//事务A执行修改后，可以看出没有读出修改

mysql> select * from abc;
+---+---+---+---+----+
| a | b | c | d | e  |
+---+---+---+---+----+
| 1 | 1 | 1 | 1 | 1  |
| 2 | 4 | 2 | 3 | 4  |
| 3 | 2 | 2 | 3 | 4  |
| 4 | 2 | 5 | 3 | 4  |
| 5 | 2 | 3 | 3 | 10 |
| 6 | 2 | 4 | 3 | 10 |
+---+---+---+---+----+
6 rows in set (0.00 sec)

//事务A提交修改后，事务B未提交读出了事务A的修改

mysql> select * from abc;
+---+---+---+---+----+
| a | b | c | d | e  |
+---+---+---+---+----+
| 1 | 1 | 1 | 1 | 1  |
| 2 | 5 | 2 | 3 | 4  |
| 3 | 2 | 2 | 3 | 4  |
| 4 | 2 | 5 | 3 | 4  |
| 5 | 2 | 3 | 3 | 10 |
| 6 | 2 | 4 | 3 | 10 |
+---+---+---+---+----+
6 rows in set (0.00 sec)

在这种隔离级别下，事务B只能读到事务A修改后并提交的数据，未提交时不能读取到修改。但是在事务A提交前后，事务B读取的结果两次不一样，叫做不可重复读也可以叫做关于一条的幻读；

可重复读

一个事务第一次读过某条记录后，即使其他事务修改了该记录值并提交，该事务之后再度该条记录时，读到的仍是第一次读到的值，而不是每次都读到不同的数据，这就是可重复读，这种隔离级别解决了不可重复，但是耗时会出现幻读，比如：事务A第一次读取到10条记录，这时另一个事务B插入一条记录，事务A再查询一次，并没有发现多出一条记录，那是不是可重复读已经解决了幻读呢？

但是再通过一组实例演示一次：

• 对于读操作
  事务A：

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

mysql> insert into abc(b,c,d,e) values(7,7,7,7);
Query OK, 1 row affected (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.02 sec)

事务B查询

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

mysql> select * from abc;
+----+---+---+---+----+
| a  | b | c | d | e  |
+----+---+---+---+----+
|  1 | 1 | 1 | 9 | 1  |
|  2 | 5 | 2 | 9 | 4  |
|  3 | 2 | 2 | 9 | 4  |
|  4 | 2 | 5 | 9 | 4  |
|  5 | 2 | 3 | 9 | 10 |
|  6 | 2 | 4 | 9 | 10 |
|  8 | 5 | 5 | 9 | 5  |
|  9 | 6 | 6 | 9 | 6  |
| 10 | 9 | 9 | 9 | 10 |
+----+---+---+---+----+
9 rows in set (0.00 sec)

mysql> select * from abc;
+----+---+---+---+----+
| a  | b | c | d | e  |
+----+---+---+---+----+
|  1 | 1 | 1 | 9 | 1  |
|  2 | 5 | 2 | 9 | 4  |
|  3 | 2 | 2 | 9 | 4  |
|  4 | 2 | 5 | 9 | 4  |
|  5 | 2 | 3 | 9 | 10 |
|  6 | 2 | 4 | 9 | 10 |
|  8 | 5 | 5 | 9 | 5  |
|  9 | 6 | 6 | 9 | 6  |
| 10 | 9 | 9 | 9 | 10 |
+----+---+---+---+----+
9 rows in set (0.00 sec)

可以看到并没有出现幻读现象。

• 对于修改操作
  开启两个事务A、B；

事务A：插入一条数据，然后提交；

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

mysql> insert into abc(b,c,d,e) values(9,9,9,9);
Query OK, 1 row affected (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.01 sec)

事务B：修改事务A插入的数据，竟然可以修改成功，修改后再次查询也可以查询到事务A插入的这条数据：

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

mysql> select * from abc;
+---+---+---+---+----+
| a | b | c | d | e  |
+---+---+---+---+----+
| 1 | 1 | 1 | 9 | 1  |
| 2 | 5 | 2 | 9 | 4  |
| 3 | 2 | 2 | 9 | 4  |
| 4 | 2 | 5 | 9 | 4  |
| 5 | 2 | 3 | 9 | 10 |
| 6 | 2 | 4 | 9 | 10 |
| 8 | 5 | 5 | 9 | 5  |
| 9 | 6 | 6 | 9 | 6  |
+---+---+---+---+----+
8 rows in set (0.00 sec)

mysql> update abc set e=10 where a=10;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from abc;
+----+---+---+---+----+
| a  | b | c | d | e  |
+----+---+---+---+----+
|  1 | 1 | 1 | 9 | 1  |
|  2 | 5 | 2 | 9 | 4  |
|  3 | 2 | 2 | 9 | 4  |
|  4 | 2 | 5 | 9 | 4  |
|  5 | 2 | 3 | 9 | 10 |
|  6 | 2 | 4 | 9 | 10 |
|  8 | 5 | 5 | 9 | 5  |
|  9 | 6 | 6 | 9 | 6  |
| 10 | 9 | 9 | 9 | 10 |
+----+---+---+---+----+
9 rows in set (0.00 sec)

对于可重复读级别下是否能解决幻读问题？

对于读操作是解决了幻读，但是对于写操作并没有解决，事务B还是能对事务A提交的插入记录进行修改，并且修改之后还能查询到。

串行化(SERIALIZABLE)

以上3种隔离级别都允许对同一记录同时进行读-读、读-写、写-读的并发操作，如果我们不允许读-写、写-读的并发操作，可以使用这一隔离级别，这种隔离级别是对同一条记录的操作都是串行的，所以不会出现脏读、幻读等现象。

版本链
对于使用InnoDB存储引擎的表来说，它的聚簇索引记录中都包含两个必要的隐藏列：

• trx_id：每次对某条记录进行改动时，都会把对应的事务id赋值给trx_id隐藏列；
• roll_pointer:每次对某条记录进行改动时，这个隐藏列会存一个指针，可以通过这个指针找到代记录修改前的信息。

ReadView
对于使用read uncommitted隔离级别来说，直接读取记录的最新版本就好了。对于使用serializable隔离级别事务来说，使用加锁的方式来访问记录。

而对于read committed和repeatable隔离级别的事务来说，就需要使用上边的版本链了，核心问题是：需要判断一下版本链中的哪个版本是当前事务可见的。

ReadView中主要包含以下内容：

• m_ids：表示生成ReadView时当前系统中活跃(未提交的)的读写事物的事务id列表；
• min_trx_id:表示在生成ReadView时当前系统中活跃的读写事务中最小的事务id，也就是m_ids中的最小值；
• max_trx_id:表示生成ReadView时系统中应该分配给下一个事务的id值；
• creator_trx_id:表示生成该ReadView的事务的事务id；

注意max_trx_id并不是m_ids中的最大值，事务id时递增分配的。比如：现有id为1，2，3这三个事务，之后id为3的事务提交了。那么一个新的读事务再生成ReadView时，m_ids就包括1，2，min_trx_id的值就是1，max_trx_id的值就是4；

有了这个ReadView，这样在访问某条记录时，只需要按照下边的步骤判断记录的某个版本是否可见：

• 如果被访问版本的trx_id属性值与ReadView中的create_trx_id值相同，意味着当前事务在访问它自己修改过的记录，所以该版本可以被当前事务访问。
• 如果被访问版本的trx_id属性值小于ReadView中的min_trx_id值，表明生成该版本的事务在当前事务生成ReadView前已提交，所以该版本可以被当前事务访问。
• 如果被访问版本的trx_id属性值大于ReadView中的max_trx_id值，表明生成该版本的事务在当前事务生成ReadView后才开启，所以该版本不可以被当前事务访问；
• 如果被访问版本的trx_id属性值在ReadView中的min_trx_id值和max_trx_id值之间，则判断一下trx_id是否存在于m_ids中，如果存在则说明当前事务还在活跃，则不可以访问，如果不存在则说明事务已经提交，该版本可以被访问。

read committed的实现方式

每一次读取数据前都生成一个ReadView，实现了只访问已提交的版本，不访问未提交的版本。每次访问查询都生成一个ReadView，查看m_ids中是否存在要访问的trx_id，如果存在则不能访问，否则可以访问。

repeatable read 实现方式
在第一次读取数据时生成一个ReadView，以后读取数据都不生成。实现了只能访问第一个版本，进而查询的时候避免了不可重复读。

MVCC总结

MVCC（Multi-Version Concurrentcy Control 多版本并发控制）指的就是使用read committed、repeatable read这两种隔离级别的事务在执行普通select操作室访问记录的版本链过程。可以是不同事务的读-写、写-读操作并发执行，从而提升系统性能。两个级别最大的不同就是生成ReadView的时机不同。

事务锁

独占锁

• lock table [表名] write 独占锁，别的事务不能读也不能写；直到当前事务执行完释放锁。

又称为排他锁、写锁或X锁，若事务T对数据对象A加上X锁，则只允许T读取和修改A，其他任何事务都不能再对A加任何类型的锁，直到T释放A上的锁。它防止任何其他事务获取资源的锁，直到在事务的末尾将资源上的原始锁释放为止。

一个锁在某个时刻只能被一个事务占有，其他事务必须等待·被释放之后才能获取到锁。

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

mysql> lock table client1 write;
Query OK, 0 rows affected (0.00 sec)

mysql> select name from client1;
^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted
mysql> update client1 set sex='M' where name='zh';
^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted

共享锁

• lock table [表名] read 共享锁，其他事务只能读，不能写；

又称为S锁或读锁，其他用户可以并发读取数据，但任何事务都不能获取数据上的独占锁，直到已释放所有共享锁。

若事务T对数据对象A加上S锁，则事务T只能读取A；其他事务只能再对A加S锁，而不能加X锁，直到T释放A上的S锁。这样就保证了其他事务只能读取A，而在S锁释放之前不能对A做任何修改。

就是允许多个事务同时获取一个锁，一个锁可以同时被多个事务拥有。

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

mysql> lock table client1 read;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from client1;
+----------+------+------+
| clientId | sex  | name |
+----------+------+------+
|        1 | F    | zh   |
|        2 | M    | li   |
+----------+------+------+
2 rows in set (0.00 sec)

mysql> update client1 set sex='F' where name='zh';

更新锁
更新锁是为了防止死锁而设定的，当SQL server准备更新数据时，它首先对数据对象做更新锁锁定，这样数据将不能被修改，但可以读取。等到SQL server确定要进行更新数据操作时，它会自动将更新锁换位独占锁。但当对象上有其他锁存在时，无法对其更新锁锁定。

读操作

对于普通select语句，InnoDB不会加任何锁

• select ... lock in share mode
  将查找到的数据上加一个S锁，允许其他事务继续获取这些记录的S锁，不能获取这个记录的X锁；
  使用场景：读出数据后，其他事务不能修改，但自己也不一定能修改，因为其他事物也可以使用这条语句继续加S锁；

• select ... for update
  将查找到的数据加一个X锁，不允许其他事物获取这些记录的S锁和X锁；
  使用场景：读出数据后，其他事务既不能写，也不能加读锁，只有自己可以修改数据。

写操作

• delete：删除一条记录时，先对记录加X锁，再执行删除；
• Insert：插入一条记录时，会先加隐式锁来保护这条新插入的记录在本事务提交前不被别的事务访问到；
• update:如果被更新的列，修改前后没有导致存储空间变化，那么先给记录加X锁，再直接对记录进行修改；如果导致了存储空间发生变化，那么先会给记录加X锁，然后将记录删掉，在Insert一条新的记录。

隐式锁：一个事务插入一条记录后，还未提交，这条记录会保存本次事务id，而其他事务如果想要来对这个记录加锁时会发现事务id不对应，这时会产生X锁，所以相当于在插入一条记录时，隐藏的给这条记录加了一把隐式X锁。

行锁与表锁

制造事务锁阻塞，查看锁和事务情况：开启两个事务，事务A中插入一条记录，不提交，事务B中修改事务A中修改的这条记录，写锁阻塞。

查看锁情况sql语句：

• 记录当前运行的事务

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;
+--------+-----------+---------------------+-----------------------+---------------------+------------+---------------------+-------------------------------+---------------------+-------------------+-------------------+------------------+-----------------------+-----------------+-------------------+-------------------------+---------------------+-------------------+------------------------+----------------------------+---------------------------+---------------------------+------------------+----------------------------+
| trx_id | trx_state | trx_started         | trx_requested_lock_id | trx_wait_started    | trx_weight | trx_mysql_thread_id | trx_query                     | trx_operation_state | trx_tables_in_use | trx_tables_locked | trx_lock_structs | trx_lock_memory_bytes | trx_rows_locked | trx_rows_modified | trx_concurrency_tickets | trx_isolation_level | trx_unique_checks | trx_foreign_key_checks | trx_last_foreign_key_error | trx_adaptive_hash_latched | trx_adaptive_hash_timeout | trx_is_read_only | trx_autocommit_non_locking |
+--------+-----------+---------------------+-----------------------+---------------------+------------+---------------------+-------------------------------+---------------------+-------------------+-------------------+------------------+-----------------------+-----------------+-------------------+-------------------------+---------------------+-------------------+------------------------+----------------------------+---------------------------+---------------------------+------------------+----------------------------+
| 102457 | LOCK WAIT | 2019-05-25 15:58:31 | 102457:350:3:13       | 2019-05-25 15:59:21 |          2 |                   7 | update abc set d=4 where a=13 | starting index read |                 1 |                 1 |                2 |                  1136 |               1 |                 0 |                       0 | REPEATABLE READ     |                 1 |                      1 | NULL                       |                         0 |                         0 |                0 |                          0 |
| 102456 | RUNNING   | 2019-05-25 15:58:21 | NULL                  | NULL                |          3 |                   2 | NULL                          | NULL                |                 0 |                 1 |                2 |                  1136 |               1 |                 1 |                       0 | REPEATABLE READ     |                 1 |                      1 | NULL                       |                         0 |                         0 |                0 |                          0 |
+--------+-----------+---------------------+-----------------------+---------------------+------------+---------------------+-------------------------------+---------------------+-------------------+-------------------+------------------+-----------------------+-----------------+-------------------+-------------------------+---------------------+-------------------+------------------------+----------------------------+---------------------------+---------------------------+------------------+----------------------------+

INNODB_TRX表字段：

• trx_id:InnoDB存储引擎内部唯一的事物ID
• trx_status:当前事务的状态， RUNNING, LOCK WAIT, ROLLING BACK or COMMITTING
• trx_status:事务的开始时间
• trx_requested_lock_id：事务等待的锁的ID（如果事务状态不是LOCK WAIT，这个字段是 NULL），详细的锁的信息可以连查INNODB_LOCKS表
• trx_wait_started：事务等待的开始时间
• trx_weight：事务的权重，反应一个事务修改和锁定的行数，当发现死锁需要回滚时，权重越小的 值被回滚
• trx_mysql_thread_id：MySQL中的进程ID，与show processlist中的ID值相对应
• trx_query：事务运行的SQL语句
• trx_operation_state：事务当操作的类型 如updating or deleting，starting index read等
• trx_tables_in_use：查询用到的表的数量
• trx_tables_locked：查询加行锁的表的数量
• trx_rows_locked：事务锁住的行数（不是准确数字）
• trx_rows_modified：事务插入或者修改的行数

记录当前出现的锁

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;
+-----------------+-------------+-----------+-----------+--------------+------------+------------+-----------+----------+-----------+
| lock_id         | lock_trx_id | lock_mode | lock_type | lock_table   | lock_index | lock_space | lock_page | lock_rec | lock_data |
+-----------------+-------------+-----------+-----------+--------------+------------+------------+-----------+----------+-----------+
| 102457:350:3:13 | 102457      | X         | RECORD    | `test`.`abc` | PRIMARY    |        350 |         3 |       13 | 13        |
| 102456:350:3:13 | 102456      | X         | RECORD    | `test`.`abc` | PRIMARY    |        350 |         3 |       13 | 13        |
+-----------------+-------------+-----------+-----------+--------------+------------+------------+-----------+----------+-----------+

INNODB_LOCKS表

• lock_id:锁ID；
• lock_trx_id：拥有锁的事务 ID。可以和 INNODB_TRX 表 JOIN 得到事务的详细信息。
• lock_mode:锁的模式。
• lock_type：锁的类型。RECORD 代表行级锁，TABLE 代表表级锁。
• lock_table：被锁定的或者包含锁定记录的表的名称。
• lock_index：当LOCK_TYPE=’RECORD’ 时，表示索引的名称；否则为 NULL。
• lock_space：当LOCK_TYPE=’RECORD’ 时，表示锁定行的表空间 ID；否则为 NULL。
• lock_page：当 LOCK_TYPE=’RECORD’ 时，表示锁定行的页号；否则为 NULL。
• lock_rec：当 LOCK_TYPE=’RECORD’ 时，表示一堆页面中锁定行的数量，亦即被锁定的记录号；否则为NULL。
• lock_data：当 LOCK_TYPE=’RECORD’ 时，表示锁定行的主键；否则为NULL；

记录锁等待的对应关系：

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;
+-------------------+-------------------+-----------------+------------------+
| requesting_trx_id | requested_lock_id | blocking_trx_id | blocking_lock_id |
+-------------------+-------------------+-----------------+------------------+
| 102457            | 102457:350:3:13   | 102456          | 102456:350:3:13  |
+-------------------+-------------------+-----------------+------------------+

INNODB_LOCK_WAITS表：

• requesting_trx_id：申请锁资源的事务ID
• requesting_lock_id：申请的锁的ID
• blocking_trx_id：阻塞的事务ID
• blocking_lock_id：阻塞的锁的ID

行锁

• lock_rec_not_gap：单个记录上的锁；
• lock_gap：间隙锁，锁定一个范围，但不包含记录本身。gap锁的目的是为了防止同一事物的两次当前读，出现幻读情况。
• lock_ordinary:同上，但是锁定记录本身；

间隙锁——read committed级别

• 使用主键查询
  session1

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

mysql> select * from abc where a=1 for update;
+---+---+---+---+---+
| a | b | c | d | e |
+---+---+---+---+---+
| 1 | 1 | 1 | 9 | 1 |
+---+---+---+---+---+
1 row in set (0.00 sec)

session2:

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

mysql> select * from abc where a=1 for update;
^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted


mysql> select * from abc where a=2 for update;
+---+---+---+---+---+
| a | b | c | d | e |
+---+---+---+---+---+
| 2 | 5 | 2 | 9 | 4 |
+---+---+---+---+---+
1 row in set (0.00 sec)

总结：查询使用的是主键时，只需要在主键值对应的那一条数据上加锁即可；

• 查询使用唯一索引
  删除上述案例中的联合索引abcindex；drop index abcindex on abc;
  建立b列的唯一索引mysql> create unique index b_index on abc(b);

session1

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

mysql> select * from abc where b='1' for update;
+---+---+---+---+---+
| a | b | c | d | e |
+---+---+---+---+---+
| 1 | 1 | 1 | 9 | 1 |
+---+---+---+---+---+
1 row in set (0.00 sec)

session2：

mysql> select * from abc where b='1' for update;
^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted
mysql> select * from abc where b='2' for update;
+---+---+---+---+---+
| a | b | c | d | e |
+---+---+---+---+---+
| 2 | 2 | 2 | 9 | 4 |
+---+---+---+---+---+
1 row in set (0.00 sec)

总结：查询时使用的是唯一索引，只需要对查询值所对应的唯一索引记录项和对应的聚集索引上的项加锁即可。

• 查询使用普通索引
  session1

mysql> select * from abc where e='4' for update;
+---+---+---+---+---+
| a | b | c | d | e |
+---+---+---+---+---+
| 2 | 5 | 2 | 9 | 4 |
| 3 | 2 | 2 | 9 | 4 |
| 4 | 2 | 5 | 9 | 4 |
+---+---+---+---+---+
3 rows in set (0.00 sec)

session2

mysql> select * from abc where a='1' for update;
+---+---+---+---+---+
| a | b | c | d | e |
+---+---+---+---+---+
| 1 | 1 | 1 | 9 | 1 |
+---+---+---+---+---+
1 row in set (0.00 sec)

mysql> select * from abc where a='4' for update;
^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted
mysql> select * from abc where a='5' for update;
+---+---+---+---+----+
| a | b | c | d | e  |
+---+---+---+---+----+
| 5 | 2 | 3 | 9 | 10 |
+---+---+---+---+----+
1 row in set (0.00 sec)

总结：查询使用的是普通索引时，会对满足条件的索引记录加上锁，同时对这些索引记录对应的聚集索引上的项也加上锁。

• 查询没有用到索引时

总结：查询的时候没有走索引，也只会对满足条件的记录加锁。

repeatable read

• 查询使用主键和RC级别一样；
• 使用唯一索引和RC级别一样；
• 使用普通索引：

session1:

session2:

mysql> select * from abc where e='4' for update;  --阻塞
^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted
mysql> select * from abc where e='1' for update;  --非阻塞
+---+---+---+---+---+
| a | b | c | d | e |
+---+---+---+---+---+
| 1 | 1 | 1 | 9 | 1 |
+---+---+---+---+---+
1 row in set (0.00 sec)
mysql> insert into abc (b,c,d,e) values(3,1,1,4); --阻塞
^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted
mysql> insert into abc (b,c,d,e) values(3,1,1,5); --非阻塞
Query OK, 1 row affected (0.00 sec)

总结：可以解决幻读问题，解决方式就是加了GAP锁；被查询出来的数据中间被夹了间隙锁，所以不能插入后或删除记录。

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;
+-----------------+-------------+-----------+-----------+--------------+------------+------------+-----------+----------+-----------+
| lock_id         | lock_trx_id | lock_mode | lock_type | lock_table   | lock_index | lock_space | lock_page | lock_rec | lock_data |
+-----------------+-------------+-----------+-----------+--------------+------------+------------+-----------+----------+-----------+
| 102607:350:6:10 | 102607      | X,GAP     | RECORD    | `test`.`abc` | eindex     |        350 |         6 |       10 | '5', 8    |
| 102606:350:6:10 | 102606      | X,GAP     | RECORD    | `test`.`abc` | eindex     |        350 |         6 |       10 | '5', 8    |
+-----------------+-------------+-----------+-----------+--------------+------------+------------+-----------+----------+-----------+

• 查询没有用到索引

session1：

mysql> select * from abc where c='2' for update;
+----+----+---+---+---+
| a  | b  | c | d | e |
+----+----+---+---+---+
|  2 | 2  | 2 | 9 | 4 |
| 15 | 13 | 2 | 1 | 4 |
+----+----+---+---+---+
2 rows in set (0.00 sec)

session2:

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

mysql> select * from abc where c='2' for update;
^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted
mysql> select * from abc where c='5' for update;
^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted
mysql> select * from abc where a=4  for update;
^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted
mysql> select * from abc where a=11  for update;
^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted

总结：查询的时候没有走索引，会对表中所有记录以及间隙加锁。

表锁

表级别的S锁、X锁

在对某个表执行alter、drop这些DDL语句时，如果有其它事务对这个表执行select、insert等语句会发生阻塞。这个郭传给你是通过元数据锁来实现的，并不是使用的表级别的S锁和X锁。

• lock tables t1 read;
• lock tables t1 write;

IS锁、IX锁

• IS：意向共享锁，当事务准备在某条记录上加S锁时，需要现在表级别上加一个IS锁；
• IX：意向排它锁，当事务准备在某条记录上加X锁时，需要现在表级别上加一个IX锁；

它们的提出仅仅为了在之后加表级锁时可以快速判断表中的记录是否被上锁，以避免用遍历的方式来查看表中有没有上锁的记录。

auto_inc锁

• 在执行插入语句时就在表级别加一个auto_inc锁，然后为每条待插入的auto_increment修饰的列分配递增的值。在该语句结束后，再把auto_inc锁释放掉。这样一个事务在持有auto-inc锁的过程中，其他事务插入语句都要被阻塞，可以保证一个语句中分配的递增值是连续的。
• 采用了一个轻量级锁，在为插入语句生成auto_increment修饰的列的值时获取一下这个轻量级锁。然后生成本次插入语句需要用到auto_increment列的值之后，就把该轻量级锁释放掉。并不需要等到整个插入语句执行完才释放。

系统变量innodb_autoinc_lock_mode： innodb_autoinc_lock_mode值为0：采用AUTO-INC锁。 innodb_autoinc_lock_mode值为2：采用轻量级锁。
当innodb_autoinc_lock_mode值为1：当插入记录数不确定是采用AUTO-INC锁，当插入记录数确 定时采用轻量级锁。

各种场景下的加锁情况

在InnoDB索引下，查询操作通过MVCC多版本并发控制来实现，事务修改数据后就会产生一个数据版本，版本之间使用指针形成版本链，存在一个ReadView主要包含当前系统中未提交的事务id列表、当前未提交事务的最小id、分配给下一个事务的id、还有生成该ReadView的事务id；对于读未提交隔离级别每次只读最新版本的数据；对于串行化隔离级别使用加锁的方式来访问记录；对于读已提交每次读数据都会生成一个ReadView，查看当前未提交事务列表中是否存在要访问的事务id，如果存在则不能访问；对于可重复读，只在一个事务内第一次查询数据的时候生成一个ReadView，以后查询只访问第一个版本。

因为对于隔离级别的读的概念有误解，以为读就只是查询，其实读包含select、insert、update等等，所以MVCC又可分为快照读和当前读：

快照读就是可以读取历史版本；

当前读是只能读取最新的版本；

对于普通的select语句来说在RU、RC、RR隔离级别下都是快照读，在串行化级别下select属于当前读需要加锁。而对于像insert、update、delete、select…for update、select…lock in share mode这种语句语句来说都是当前读；并且读取之后防止防止记录被修改，除了最后一个加S锁，其余都要加X锁；对于串行化读已经从基于mvcc控制退化成基于lock锁来控制；

对于这种写操作或加锁操作来说是基于加锁来控制隔离级别的。在判断一条语句加了什么锁的时候需要知道：当前隔离级别、语句涉及的范围是全文扫描还是索引扫描、语句的条件是主键索引、唯一索引还是非唯一索引。

案例delete from t1 where id=10 对于RC和RR级别下有以下组合：

场景	加锁情况
RC+id主键	会对id=10这条记录加上X锁
RC+id唯一索引	会在id索引记录上加上X锁，并且对id=10对应主键对应的记录上也加上X锁
RC+id非唯一索引	会对所有满足id=10的索引记录上加X锁，并且对其所对应的主键对应的所有记录上加X锁
RC+id无索引	会进行全表扫描，全表所有的记录上都加上X锁，但是mysql做了优化，对于不满足条件的记录上的锁会释放
RR+id主键、RR+id唯一索引，由于这两个条件都只查询出一条记录，而RR级别都是针对幻读，所以这两种情况下与RC情况下一样
RR+id非唯一索引	与RC级别不同的是，RR级别下不仅会对满足的记录加上X锁，而且对满足的记录范围内还会加上gap间隙锁，用来防止幻读
RR+id无索引	所有记录都加上X锁，并且加上间隙锁，不过mysql也做了优化可以释放不满足条件记录上的锁，但是全表所有记录加锁这个过程是有的

形成死锁的原因以及解决方法

事务之间对资源访问顺序的交替

• 出现原因：
  一个用户A需要访问A表和B表，用户B也需要访问A表和B表。用户A先访问A表，加上锁，用户B先访问B表加上锁，用户A访问完A表发现B锁已经被锁住了，然后就等待(未释放锁)。用户B访问完B表(未释放锁)准备访问A表，也发现被锁住了，同样继续等待。这样就形成了死锁。

• 解决方法
  修改程序代码，按顺序访问，必须都先访问A表，或都先访问B表。尽量避免同时锁定两个资源。

并发修改同一记录

• 发生原因：等待释放
  有两个事务A、B开始都在查询表T，所以都获得了资源上的S锁。这时其中一个事务想要修改数据，所以想将S锁上升为X锁，而这时又有事务B在共享S锁。所以事务A一直等待B释放S锁之后，再上升。但不巧的是，事务B并没有释放锁，而是同样的也想修改数据，将S锁上升为X锁，但是事务A也在共享S锁，事务B就进入等待状态。这时A、B事务都在等待对方释放共享锁，发生了死锁。

• 解决方法

1. 使用乐观锁进行控制；
   即在数据库表中增加一个version字段来实现，每修改一次数据，对应的version就加1。将提交数据版本数据与数据库表对应记录的当前版本信息进行对比，如果提交的数据版本号大于数据库表当前版本号，则予以更新，否则认为是过期数据。

优点：避免了长事务中的数据库加锁开销
缺点：由于乐观锁是在系统中实现的，来自外部系统的用户更新不受系统控制，有可能会造成总数据被更新到数据库。

2. 使用悲观锁进行控制；
   悲观锁大多数依靠数据库的锁机制实现，悲观锁理念是保证最大程度的独占性，但随之而来的就是数据库性能的大量开销。

为解决死锁，SQLserver引入更新锁，特征如下：
a. 加锁的条件：一个事务执行update语句的时候，数据库系统为事务分配一把更新锁；
b. 解锁的条件：当读取数据完毕，执行更新操作时，会把更新所升级为独占锁。
c. 与其他锁的兼容性：更新锁和共享锁时兼容的，一个资源可以同时放置更新锁和共享锁，但是最多一把更新锁。这样，当多个事务更新相同的数据时，只有一个事物能获得更新锁，然后再把更新锁升级为独占锁，其他事物必须等到前一个事务结束后，才能取得更新锁，这就避免了死锁。

个人理解：加上更新锁意味着，你们读都是可以一起读的，但是如果想写只能一个接着一个来，不能一起来。

索引不当导致全表扫描

• 出现原因
  如果在事务中执行了一条不满足条件的语句，执行全表扫描，把行级锁上升为表级锁，多个这样的事务执行后，就很容易产生死锁和阻塞。类似的情况，如果索引建的不合适，也会发生全表扫描，最终系统会越来越慢发生阻塞或死锁。

• 解决方法
  SQL语句中不要使用太复杂的关联多表查询，对有全表扫描的语句，建立相应的索引进行优化。

排查死锁的步骤

• 查看死锁日志，查看死锁日志的命令show engine innodb status;
• 根据当前各个事务执行SQL分析出加锁类型及顺序
• 逆向推断出如何形成循环等待。

避免死锁

• 以固定的顺序访问表和行；
• 大事务拆小，大事务更容易产生死锁；
• 在同一个事务中，尽可能做到一次锁定所需要的所有资源，减少死锁概率；
• 降低隔离级别；
• 为表添加合理索引。