MySQL-锁机制和事务

一、简介

1）InnoDB存储引擎支持行级锁，其大类可以细分为共享锁和排它锁两类

  • 共享锁(S)：允许拥有共享锁的事务读取该行数据。当一个事务拥有一行的共享锁时，另外的事务可以在同一行数据也获得共享锁，但另外的事务无法获得同一行数据上的排他锁
  • 排它锁(X)：允许拥有排它锁的事务修改或删除该行数据。当一个事务拥有一行的排他锁时，另外的事务在此行数据上无法获得共享锁和排它锁，只能等待第一个事务的锁释放

2）除了共享锁和排他锁之外，InnoDB也支持意图锁。该锁类型是属于表级锁，表明事务在后期会对该表的行施加共享锁或者排它锁。所以对意图锁也有两种类型：

  • 共享意图锁(IS)：事务将会对表的行施加共享锁
  • 排他意图锁(IX)：事务将会对表的行施加排它锁

3）举例来说select … lock in share mode语句就是施加了共享意图锁，而select … for update语句就是施加了排他意图锁

4）这四种锁之间的相互共存和排斥关系如下：

image

5）所以决定一个事务请求为数据加锁时能否立即施加上锁，取决于该数据上已经存在的锁是否和请求的锁可以共存还是排斥关系，当相互之间是可以共存时则立即施加锁，当相互之间是排斥关系时则需要等待已经存在的锁被释放才能施加

二、InnoDB锁相关系统表

2.1、Information_schema.innodb_trx表

Information_schema.innodb_trx记录了InnoDB中每一个正在执行的事务，包括该事务获得的锁信息，事务开始时间，事务是否在等待锁等信息

Column name Description
TRX_ID InnODB内部标示每个事务的ID
TRX_WEIGHT 表示该事务的权重，近似等于事务锁的行记录数。当发生死锁时，InnoDB会根据此值选择最小的值作为牺牲品.
TRX_STATE 事务当前状态，包括RUNNING, LOCK WAIT, ROLLING BACK, and COMMITTING.
TRX_STARTED 事务开始时间
TRX_REQUESTED_LOCK_ID当事务状态为lock_wait时，表示需要等待的事务的锁ID，对应innodb_locks表里的lock_id；如果是其他值则为NULL
TRX_WAIT_STARTED 当事务状态为LOCK WAIT则代表事务等待锁的开始时间；如果是其他值则为NULL.
TRX_MYSQL_THREAD_ID MySQL线程ID，对应show processlist里的值
TRX_QUERY 事务当前执行的语句
TRX_OPERATION_STATE 事务当前执行的语句类型，不执行则为NULL
TRX_TABLES_IN_USE 执行当前语句需要涉及到几个InnoDB表
TRX_TABLES_LOCKED 当前语句执行施加的行锁对应了几个表
TRX_LOCK_STRUCTS 当前事务保留的锁个数
TRX_LOCK_MEMORY_BYTES 当前事务的锁信息所占用的内存byte数
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当值为1时表明事务为只读事务
TRX_AUTOCOMMIT_NON_LOCKING当值为1代表事务中只有一个普通select语句，不会施加任何锁，且由于autocommit是开启的，所以事务只会包含该select语句

2.2、Information_schema.innodb_locks表

Information_schema.innodb_locks记录了InnoDB中事务在申请但目前还没有获取到的每个锁信息，以及当前事务的锁正在阻止其他事务获得锁

Column nameDescription
LOCK_IDInnoDB内部标示每个锁的ID
LOCK_TRX_ID表示持有该锁的事务ID，对应innodb_trx表中的事务ID
LOCK_MODE表示该锁的模式。可以是S[,GAP], X[,GAP], IS[,GAP], IX[,GAP], AUTO_INC, UNKNOWN. 除了AUTO_INC andUNKNOWN的锁模式，其他的锁模式都暗含是GAP间隔锁
LOCK_TYPE表示锁类型，可以是RECORD表示行锁, TABLE表示表锁
LOCK_TABLE该锁涉及的表名
LOCK_INDEX当为行锁时，则代表该锁涉及的索引名，否则为NULL.
LOCK_SPACE当为行锁时，则表示被锁记录所在的表空间ID；否则为NULL
LOCK_PAGE当为行锁时，则表示被锁记录所在的数据页数量；否则为NULL
LOCK_REC事务锁定行的数量，若是表锁则该值为NULL
LOCK_DATA事务锁定记录主键值，若是表锁则该值为NULL

2.3、Information_schema.innodb_lock_waits表

Information_schema.innodb_lock_waits记录了InnoDB中事务之间相互等待锁的信息

Column name Description
REQUESTING_TRX_ID 请求锁被阻止的事务ID
REQUESTED_LOCK_ID 请求锁被阻止的锁ID
BLOCKING_TRX_ID 阻止上述事务获得锁的事务ID
BLOCKING_LOCK_ID 阻止事务对应的的锁ID

三、各种锁

3.1、行级锁

1）行级锁是施加在索引行数据上的锁，比如SELECT c1 FROM t WHERE c1 = 10 FOR UPDATE语句是在t.c1=10的索引行上增加锁，来阻止其他事务对对应索引行的insert/update/delete操作。

2）当一个InnoDB表没有任何索引时，则行级锁会施加在隐含创建的聚簇索引上，所以说当一条sql没有走任何索引时，那么将会在每一条聚集索引后面加X锁，这个类似于表锁，但原理上和表锁应该是完全不同的

创建表：create table temp(id int,name varchar(10));

插入数据：insert into temp values(1,'a'),(2,'b'),(3,'c');

链接1 链接2

##update加排他锁
mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)
mysql> update temp set name='aa' where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

##update等待第一个锁释放
mysql> update temp set name='bb' where id=2;

##commit释放锁
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
##等待结束，释放锁 Query OK, 1 row affected (6.84 sec) Rows matched: 1 Changed: 1 Warnings: 0

##释放自己的锁
Commit;

image

image

3）当给表添加主键后

alter table temp add primary key(id);

链接1 链接2

##update加排他锁
mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)
mysql> update temp set name='aa' where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

#修改第二行不会锁住

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

##update等待第一个锁释放

mysql> update temp set name='bb' where id=1;

##commit释放锁
mysql> commit;
Query OK, 0 rows affected (0.00 sec)

##等待结束，释放锁
Query OK, 1 row affected (6.84 sec)
Rows matched: 1 Changed: 1 Warnings: 0

##释放自己的锁
Commit;

3.2、间隔锁gaplock

3.2.1、gaplock介绍

1）当 我们用范围条件而不是相等条件检索数据，并请求共享或排他锁时，InnoDB会给符合条件 的已有数据记录的索引项加锁；对于键值在条件范围内但并不存在的记录，叫做“间隙（GAP)”，InnoDB也会对这个“间隙”加锁

2）间隔锁是施加在索引记录之间的间隔上的锁，锁定一个范围的记录、但不包括记录本身,比如SELECT c1 FROM t WHERE c1 BETWEEN 10 and 20 FOR UPDATE语句，尽管有可能对c1字段来说当前表里没有=15的值，但还是会阻止=15的数据的插入操作，是因为间隔锁已经把索引查询范围内的间隔数据也都锁住了

3）间隔锁的使用只在部分事务隔离级别才是生效的

4）间隔锁只会阻止其他事务的插入操作

3.2.2、gap lock的前置条件

1）事务隔离级别为REPEATABLE-READ，innodb_locks_unsafe_for_binlog参数为0，且sql走的索引为非唯一索引（无论是等值检索还是范围检索）

2）事务隔离级别为REPEATABLE-READ，innodb_locks_unsafe_for_binlog参数为0，且sql是一个范围的当前读操作，这时即使不是非唯一索引也会加gap lock

创建测试表：

CREATE TABLE `temp` ( `id` int(11) NOT NULL, `name` varchar(10) DEFAULT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=latin1

插入测试数据：

insert into temp values(1,'a'),(2,'b'),(3,'c');

链接1 链接2

##update加排他锁和间隔锁
mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)
mysql> update temp set name='abc' where id between 4 and 6;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0 Changed: 0 Warnings: 0

##update等待第一个锁释放
mysql> insert into temp values(4,'d');

##commit释放锁
mysql> commit;
Query OK, 0 rows affected (0.00 sec)

##等待结束，释放锁
Query OK, 1 row affected (6.84 sec)
Rows matched: 1 Changed: 1 Warnings: 0
##释放自己的锁 Commit;

3）如果链接1的update语句是update temp set name=‘abc’ where id >4; 而链接2的插入数据的id=4时也会被阻止，是因为记录中的3~4之间也算是间隔

3.3、下一键锁next-key  lock

3.3.1、next-key  lock介绍

1）在默认情况下，mysql的事务隔离级别是可重复读，并且innodb_locks_unsafe_for_binlog参数为0，这时默认采用next-key locks。所谓Next-Key Locks，就是记录锁和间隔锁的结合，即除了锁住记录本身，还要再锁住索引之间的间隙。

3.4、插入意图锁

3.4.1、介绍

1）插入意图锁是在插入数据时首先获得的一种间隔锁，对这种间隔锁只要不同的事务插入的数据位置是不一样的，虽然都是同一个间隔，也不会产生互斥关系

2）比如有一个索引有4和7两个值，如果两个事务分别插入5和6两个值时，虽然两个事务都会在索引4和7之间施加间隔锁，但由于后续插入的数值不一样，所以两者不会互斥

3）比如下例中事务A对索引>100的值施加了排他间隔锁，而事务B在插入数据之前就试图先施加插入意图锁而必须等待

#事务A：
mysql> CREATE TABLE child (id int(11) NOT NULL, PRIMARY KEY(id)) ENGINE=InnoDB;
mysql> INSERT INTO child (id) values (90),(102);
mysql> START TRANSACTION;
mysql> SELECT * FROM child WHERE id > 100 FOR UPDATE;
+-----+
| id  |
+-----+
| 102 |
+-----+

#事务B：
mysql> START TRANSACTION;
mysql> INSERT INTO child (id) VALUES (101);  #等待

#可以通过show engine innodb status命令查看插入意向锁被阻止
mysql> show engine innodb status;
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1136, 2 row lock(s)
MySQL thread id 4, OS thread handle 140143307765504, query id 338 localhost root update
INSERT INTO child (id) VALUES (101)
------- TRX HAS BEEN WAITING 17 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 150 page no 3 n bits 72 index PRIMARY of table `locktest`.`child` trx id 417643 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 80000066; asc    f;;
 1: len 6; hex 000000065f65; asc     _e;;
 2: len 7; hex cc0000018d011d; asc        ;;

3.5、自增锁

1）自增锁是针对事务插入表中自增列时施加的一种特殊的表级锁，即当一个事务在插入自增数据时，另一个事务必须等待前一个事务完成插入，以便获得顺序的自增值

2）参数innodb_autoinc_lock_mode可以控制自增锁的使用方法

四、InnoDB锁相关系统变量

4.1、tx_isolation

查看当前系统隔离级别

mysql> show variables like 'tx_isolation';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| tx_isolation  | REPEATABLE-READ |
+---------------+-----------------+

4.2、autocommit

查看是否开启自动提交

mysql> show variables like 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | ON    |
+---------------+-------+

4.3、innodb_table_locks

mysql> show variables like 'innodb_table_locks';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| innodb_table_locks | ON    |
+--------------------+-------+

4.4、innodb_lock_wait_timeout

查看innodb事务等待事务的超时时间（秒）

mysql> show variables like 'innodb_lock_wait_timeout';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| innodb_lock_wait_timeout | 50    |
+--------------------------+-------+

测试：

链接1：
Mysql> set autocommit=0;
mysql> update temp set name='abc' where id>4;

链接2：
Mysql> set autocommit=0;
mysql> insert into temp values(4,'abc');  #等待50秒后超时，事务回滚
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

4.5、innodb_locks_unsafe_for_binlog

innodb_locks_unsafe_for_binlog参数用来控制innodb中是否允许间隔锁，默认是OFF代表允许间隔锁，设置成ON则代表不使用间隔锁，只是使用行级锁

mysql> show variables like 'innodb_locks_unsafe_for_binlog';
+--------------------------------+-------+
| Variable_name                  | Value |
+--------------------------------+-------+
| innodb_locks_unsafe_for_binlog | OFF   |
+--------------------------------+-------+

测试：

在my.cnf中配置innodb_locks_unsafe_for_binlog=on选项
[root@db01 ~]# cat /etc/my.cnf
[mysqld]
innodb_locks_unsafe_for_binlog = on

链接1：
mysql> set autocommit=0;
mysql> update temp set name='abc' where id>=4;

链接2：
mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into temp values(4,'abc'); ##未出现等待的情况
Query OK, 1 row affected (0.00 sec)

五、InnoDB事务隔离级别

5.1、简介

1）InnoDB存储引擎提供了四种事务隔离级别，分别是：

  • READ UNCOMMITTED：读取未提交内容
  • READ COMMITTED：读取提交内容
  • REPEATABLE READ：可重复读，默认值。
  • SERIALIZABLE：串行化

2）可以通过--transaction-isolation参数设置实例级别的事务隔离级别，也可以通过set [session/global] transaction isolation level语句修改当前数据库链接或者是后续创建的所有数据库链接的事务隔离级别。每个事务隔离级别所对应的锁的使用方法都有所不同 

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

5.2、REPEATABLE READ：可重复读

REPEATABLE READ：可重复读，默认值。

1）表明对同一个事务来说第一次读数据时会创建快照，在事务结束前的其他读操作(不加锁)会获得和第一次读相同的结果。

2）当读操作是加锁的读语句(select … for update或者lock in share mode),或者update和delete语句时，加锁的方式依赖于语句是否使用唯一索引访问唯一值或者范围值

  • 当访问的是唯一索引的唯一值时，则InnoDB会在索引行施加行锁
  • 当访问唯一索引的范围值时，则会在扫描的索引行上增加间隔锁或者next-key锁以防止其他链接对此范围的插入

5.3、READ COMMITTED：读取提交内容

1）意味着每次读都会有自己最新的快照。对于加锁读语句(select … for update和lock in share mode)，或者update，delete语句会在对应的行索引上增加锁，但不像可重复读一样会增加间隔锁，因此其他的事务执行插入操作时如果是插入非索引行上的数值，则不影响插入。

2）由于该隔离级别是禁用间隔锁的，所以会导致幻读的情况

3）如果是使用此隔离级别，就必须使用行级别的二进制日志

4）此隔离级别还有另外的特点：

对于update和delete语句只会在约束条件对应的行上增加锁。对update语句来说，如果对应的行上已经有锁，则InnoDB会执行半一致读的操作，来确定update语句对应的行在上次commit之后的数据是否在锁的范围，如果不是，则不影响update操作，如果是，则需要等待对应的锁解开

链接1 链接二

Mysql> set session transaction isolation level read committed;
mysql> set autocommit=0;
mysql> update temp set name='aaa' where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> set session transaction isolation level read committed;
mysql> set autocommit=0;
mysql> select * from temp;
+----+------+
| id | name |
+----+------+
| 1 | bb |
| 2 | bb |
| 3 | abc |
| 4 | abc |

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

mysql> select * from temp; ##同一个事务中能看到另一个事务已经提交的数据
+----+------+
| id | name |
+----+------+
| 1 | aaa |
| 2 | bb |
| 3 | abc |
| 4 | abc |
Commit;

5.4、READ UNCOMMITTED：读取未提交内容

READ UNCOMMITTED：读取未提交内容，所读到的数据可能是脏数据

链接 链接2

mysql> set session transaction isolation level read uncommitted;
Query OK, 0 rows affected (0.00 sec)
mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)
mysql> update temp set name='bb';
Query OK, 3 rows affected (0.00 sec)
Rows matched: 4 Changed: 3 Warnings: 0

mysql> set session transaction isolation level read uncommitted;
Query OK, 0 rows affected (0.00 sec)
mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from temp; ##已经可以看到未提交的数据
+----+------+
| id | name |
+----+------+
| 1 | bb |
| 2 | bb |
| 3 | bb |
| 4 | bb |
+----+------+
4 rows in set (0.00 sec)

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

5.5、SERIALIZABLE：串行化

此隔离级别更接近于可重复读这个级别，只是当autocommit功能被禁用后，InnoDB引擎会将每个select语句隐含的转化为select … lock in share mode

链接1 链接2

mysql> set session transaction isolation level SERIALIZABLE;
Query OK, 0 rows affected (0.00 sec)
mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)
mysql> update temp set name='bb';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 4 Changed: 0 Warnings: 0

mysql> set session transaction isolation level SERIALIZABLE;
Query OK, 0 rows affected (0.00 sec)
mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from temp; ##锁等待

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

mysql> select * from temp;
+----+------+
| id | name |
+----+------+
| 1 | aa |
| 2 | aa |
| 3 | aa |
| 4 | aa |
+----+------+
Commit;

六、Autocommit/commit/rollback

1）当设置autocommit属性开启时，每个SQL语句都会隐含成为独立的事务。

2）默认情况下autocommit属性是开启的，也就意味着当每个SQL语句最后执行结果不返回错误时都会执行commit语句，当返回失败时会执行rollback语句

3）而当autocommit属性开启时，可以通过执行start transaction或者begin语句来显示的开启一个事务，而事务里可以包含多个SQL语句，最终事务的结束是由commit或者rollback来终结

4）而当在数据库链接里执行set autocommit=0代表当前数据库链接禁止自动提交，事务的终结由commit或者rollback决定，同时也意味着下一个事务的开始

5）如果一个事务在autocommit=0的情况下数据库链接退出而没有执行commit语句，则这个事务会回滚

6）一些特定的语句会隐含的终结事务，就好比是执行了commit语句

7）commit语句代表将此事务的数据修改永久化，并对其他事务可见，而rollback则代表将此事务的数据修改回滚。

8）commit和rollback都会把当前事务执行所施加的锁释放

9）当使用多语句事务时，如果全局的autocommit属性是开启的，则开始此事务的方式可以使set autocommit=0将当前链接的属性关闭，最后执行commit和rollback；或者是显示的使用start transaction语句开启事务

七、一致读

1）在默认的隔离级别下一致读是指InnoDB在多版本控制中在事务的首次读时产生一个镜像，在首次读时间点之前其他事务提交的修改可以读取到，而首次读时间点之后其他事务提交的修改或者是未提交的修改都读取不到

2）唯一例外的情况是在首次读时间点之前的本事务未提交的修改数据可以读取到

3）在读取提交数据隔离级别下，一致读的每个读取操作都会有自己的镜像

4）一致读操作不会施加任何的锁，所以就不会阻止其他事务的修改动作

image

5）一致读在某些DDL语句下不生效：

  • 碰到drop table语句时，由于InnoDB不能使用被drop的表，所以无法实现一致读
  • 碰到alter table语句时，也无法实现一致读

6）当碰到insert into… select, update … select和create table … select语句时，在默认的事务隔离级别下，语句的执行更类似于在读取提交数据的隔离级别下

八、加锁读

8.1、加锁读介绍

1）当在一个事务中在读操作结束后会执行insert和update操作时，普通的读操作无法阻止其他事务对相同数据执行修改操作，所以InnoDB提供了两种在读操作时就增加锁的方式

2）select … lock in share mode：在读取的行数据上施加共享锁，其他的事务可以读相同的数据但无法修改；如果在执行此语句时有其他事务对相同的数据已经施加了锁，则需要等待事务完结释放锁

3）select … for update：和update操作一样，在涉及的行上施加排他锁，并阻止任何其他事务对涉及行上的修改操作、以及加锁读操作，但不会阻止对涉及行上的一般读（不加锁）操作

4）同样，锁的释放也是在事务提交或者回滚之后

8.2、加锁读应用

1）比如在子表中插入一行数据，要确保对应的列在父表中有值，通过一般的读操作先查父表有值然后再插入的方法是不保险的，因为在读操作和插入操作之间就有可能其他事务会将父表的数据修改掉。那保险的做法是在查询父表是用加锁读的方式

SELECT * FROM parent WHERE NAME = 'Jones' LOCK IN SHARE MODE;

创建测试表：

mysql> create table temp(id int primary key,name varchar(10));

mysql> create table temp_child(id int primary key,temp_id int,constraint f_1 foreign key (temp_id) references temp(id));

mysql> insert into temp values(1,'a'),(2,'b'),(3,'c'),(4,'d');

链接1 链接2

mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from temp;
+----+------+
| id | name |
+----+------+
|  1 | a    |
|  2 | b    |
|  3 | c    |
|  4 | d    |
+----+------+

mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)

mysql> delete from temp where id=3;
Query OK, 1 row affected (0.00 sec)

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

mysql> select * from temp;
+----+------+
| id | name |
+----+------+
|  1 | a    |
|  2 | b    |
|  3 | c    |
|  4 | d    |
+----+------+

mysql> insert into temp_child values(1,3);  #报外键冲突
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`locktest`.`temp_child`, CONSTRAINT `f_1` FOREIGN KEY (`temp_id`) REFERENCES `temp` (`id`))
mysql> rollback;

2）比如当表中有一个行数计数字段时，使用一致读和lock in share mode都有可能导致重复错误数据出现，因为有可能两个事务会读到相同的值，在这种情况下就要使用select … for update语句保证一个事务在读时，另一个事务必须等待

SELECT counter_field FROM child_codes FOR UPDATE;
UPDATE child_codes SET counter_field = counter_field + 1;

九、SQL语句对应的锁

1）加锁读，修改和删除SQL语句都会在索引扫描过的每一行增加锁，也就是说不光是在where条件限制的索引行上增加锁，也会对扫描到的间隔增加间隔锁

2）如果SQL语句是使用二级索引查找数据而且施加的是排他锁，则InnoDB也会在对应的聚簇索引行上施加锁

3）如果SQL语句没有任何索引可以使用，则MySQL需要扫描全表数据，而每行数据都会被施加锁，所以一个良好的习惯是为InnoDB添加合适的索引

4）针对不同的语句，InnoDB会施加不同的锁：

5）Select…from语句属于一致性读，在默认情况下不施加任何的锁，除非在可串行化隔离级别下，会施加共享next-key锁在扫描的索引行上，当碰到使用唯一索引查找唯一值时只在唯一值上施加锁

6）Select…lock in share mode语句会在索引扫描行上施加共享next-key锁，除非是当碰到使用唯一索引查找唯一值时只在唯一值上施加锁

7）Select…for update语句会对扫描索引的行上施加排他next-key锁，除非是当碰到使用唯一索引查找唯一值时只在唯一值上施加锁

8）Update语句会对扫描索引的行上施加排他next-key锁，除非是当碰到使用唯一索引查找唯一值时只在唯一值上施加锁。

9）Delete语句会对扫描索引的行上施加排他next-key锁，除非是当碰到使用唯一索引查找唯一值时只在唯一值上施加锁

10）Insert语句会对索引扫描的行上施加锁，但不是next-key锁，所以不会阻止其他事务对该行值前的间隔上插入数据

11）Insert into T select…from S语句会对插入到T表的行施加排他锁（非间隔锁），而在默认隔离级别下会对访问的S表上的行施加共享next-key锁

12）当表上有外键约束时，对任何的insert,update和delete操作都会在需要检查外键约束的行上施加共享行锁

13）Lock table语句是施加表级锁

十、幻读

1）幻读问题发生在同一个事务中当相同的读操作在前后两次读数据时返回不同的结果集。

2）比如在表的ID字段上有一个索引，当希望对ID>100的数据进行后续修改时，我们会使用如下的语句： SELECT * FROM child WHERE id > 100 FOR UPDATE，而如果表里目前只有90和102两个值时，如果没有间隔锁锁住90到102之间的间隔，则其他的事务会插入比如101这个值，这样的话在第二次读数据时就会返回三行记录而导致幻读

3）为了阻止幻读情况的发生，InnoDB使用了一种方法next-key锁将索引行锁和间隔锁合并在一起。InnoDb会在索引扫描的行上施加行级共享锁或者排他锁，而next-key锁也会在每个索引行之前的间隔上施加锁，会导致其他的session不能在每个索引之前的间隔内插入新的索引值

4）间隔锁会施加在索引读碰到的行数据上，所以对上例来说为了阻止插入任何>100的值，也会将最后扫描的索引值102之前的间隔锁住

十一、InnoDB锁性能监控

mysql> show status like '%innodb_row_lock%';
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| Innodb_row_lock_current_waits | 0     | #当前等待锁的数量 
| Innodb_row_lock_time          | 0     | #系统启动到现在、锁定的总时间长度 
| Innodb_row_lock_time_avg      | 0     | #每次平均锁定的时间 
| Innodb_row_lock_time_max      | 0     | #最长一次锁定时间
| Innodb_row_lock_waits         | 0     | #系统启动到现在、总共锁定次数
+-------------------------------+-------+

十二、InnoDB死锁

12.1、死锁介绍

1）死锁的情况发生在不同的的事务相互之间拥有对方需要的锁，而导致相互一直无限等待

2）死锁可能发生在不同的事务都会对多个相同的表和相同的行上施加锁，但事务对表的操作顺序不相同

3）为了减少死锁的发生，要避免使用lock table语句，要尽量让修改数据的范围尽可能的小和快速；当不同的事务要修改多个表或者大量数据时，尽可能的保证修改的顺序在事务之间要一致

4）默认情况下InnoDB下的死锁自动侦测功能是开启的，当InnoDB发现死锁时，会将其中的一个事务作为牺牲品回滚。

5）通过innodb_lock_wait_timeout参数配置自动侦测功能是否开启，如果关闭的话，InnoDB就会使用innodb_lock_wait_timeout参数来自动回滚等待足够时间的事务

6）可以通过show engine innodb status语句查看最后一次发生死锁的情况

比如以下例子产生的死锁：
链接1：
mysql> CREATE TABLE t (i INT) ENGINE = InnoDB;
mysql> INSERT INTO t (i) VALUES(1);
mysql> START TRANSACTION;
mysql> SELECT * FROM t WHERE i = 1 LOCK IN SHARE MODE; ##在i=1记录上加共享锁
+------+
| i    |
+------+
| 1    |

链接2：
mysql> START TRANSACTION;
mysql> DELETE FROM t WHERE i = 1; ##请求在i=1的记录上增加排他锁，但被链接1的事务阻止


链接1：
mysql> DELETE FROM t WHERE i = 1;
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
这个死锁发生是因为链接1试图施加排他锁，但因为链接2上的事务已经在请求排他锁，而这个锁的释放必须要等待链接1上的事务释放共享锁，而链接1上原本的共享锁由于顺序的原因也无法升级为排它锁，所以就导致了死锁的发生。

7）默认情况下死锁检测功能是开启的，当死锁发生时InnoDB会自动检测到并牺牲(回滚)其中的一个或者几个事务，以便让其他的事务继续执行下去。InnoDB选择牺牲的事务往往是代价比较小的事务，其代价计算是根据事务insert,update, delete的数据行规模决定

8）如果事务中的某个语句因为错误而回滚，则这个语句上的锁可能还会保留，是因为InnoDB仅会存储行锁信息，而不会存储行锁是由事务中的哪个语句产生的

9）如果在一个事务中，select语句调用了函数，而函数中的某个语句执行失败，则那个语句会回滚，如果在整个事务结束时执行rollback，则整个事务回滚

10）可以通过innodb_deadlock_detect 参数关闭死锁检测功能，而仅仅用innodb_lock_wait_timeout的功能来释放锁等待

12.2、减少死锁发生的方法

在事务性数据库中，死锁是个经典的问题，但只要发生的频率不高则死锁问题不需要太过担心

查看死锁的方法有两种：

  • 通过show engine innodb status命令可以查看最后一个死锁的情况
  • 通过innodb_print_all_deadlocks参数配置可以将所有死锁的信息都打印到MySQL的错误日志中

减少死锁发生的方法：

  1. 尽可能的保持事务小型化，减少事务执行的时间可以减少发生影响的概率
  2. 及时执行commit或者rollback，来尽快的释放锁
  3. 可以选用较低的隔离级别，比如如果要使用select... for update和select...lock in share mode语句时可以使用读取提交数据隔离级别
  4. 当要访问多个表数据或者要访问相同表的不同行集合时，尽可能的保证每次访问的顺序是相同的。比如可以将多个语句封装在存储过程中，通过调用同一个存储过程的方法可以减少死锁的发生
  5. 增加合适的索引以便语句执行所扫描的数据范围足够小
  6. 尽可能的少使用锁，比如如果可以承担幻读的情况，则直接使用select语句，而不要使用select...for update语句
  7. 如果没有其他更好的选择，则可以通过施加表级锁将事务执行串行化，最大限度的限制死锁发生
SET autocommit=0;
LOCK TABLES t1 WRITE, t2 READ, ...;
... do something with tables t1 and t2 here ...
COMMIT;
UNLOCK TABLES;

12.3、死锁监控

show engine innodb statusG

mysql> show variables like '%deadlock%';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| innodb_deadlock_detect     | ON    |
| innodb_print_all_deadlocks | OFF   |
+----------------------------+-------+

vim /etc/my.cnf  #记录错误日志
innodb_print_all_deadlocks = 1

十三、补充：锁的监控及处理

13.1、模拟锁等待定位过程

## 所等待定位过程：
##所等待模拟
## tx1:
UPDATE t1 SET k1='av' WHERE id=10;

## tx2:
UPDATE t1 SET k1='az' WHERE id=10;

# 监控锁状态
## 1. 看有没有锁等待
SHOW  STATUS LIKE 'innodb_row_lock%';

## 2. 查看那个事务在等待(被阻塞了)
USE information_schema
SELECT * FROM information_schema.INNODB_TRX WHERE trx_state='LOCK WAIT';

trx_id : 事务ID号
trx_state : 当前事务的状态
trx_mysql_thread_id:连接层的,连接线程ID(SHOW PROCESSLIST ===>Id或trx_id )
trx_query : 当前被阻塞的操作(一般是要丢给开发的)

## 3. 查看锁源,谁锁的我!

SELECT * FROM sys.innodb_lock_waits;     ## ====>被锁的和锁定它的之间关系

locked_table : 哪张表出现的等待 
waiting_trx_id: 等待的事务(与上个视图trx_id 对应)
waiting_pid   : 等待的线程号(与上个视图trx_mysql_thread_id)
blocking_trx_id : 锁源的事务ID 
blocking_pid    : 锁源的线程号

## 4. 找到锁源的thread_id 
SELECT * FROM performance_schema.threads WHERE processlist_id=15;

## 5. 找到锁源的SQL语句
-- 当前在执行的语句
SELECT * FROM performance_schema.`events_statements_current` WHERE thread_id=41;
-- 执行语句的历史
SELECT * FROM performance_schema.`events_statements_history` WHERE thread_id=41;

#一键获得以上信息,请写出具体的SQL语句
SELECT b.waiting_trx_id,a.trx_query,b.waiting_pid,b.blocking_trx_id,b.blocking_pid,c.thread_id,d.sql_text FROM 
information_schema.innodb_trx a 
INNER JOIN 
sys.`innodb_lock_waits` b 
ON a.trx_id=b.waiting_trx_id
INNER JOIN 
performance_schema.threads c
ON c.processlist_id=b.blocking_pid
INNER JOIN performance_schema.`events_statements_current` d
ON c.thread_id=d.thread_id
;

13.2、优化项目:锁的监控及处理

1. 背景: 
硬件环境: DELL R720,E系列16核,48G MEM,SAS*900G*6,RAID10
在例行巡检时,发现9-11点时间段的CPU压力非常高(80-90%)

2. 项目的职责
	2.1 通过top详细排查,发现mysqld进程占比达到了700-800%
	2.2 其中有量的CPU是被用作的SYS和WAIT,us处于正常
	2.3 怀疑是MySQL 锁 或者SQL语句出了问题
	2.4 经过排查slowlog及锁等待情况,发现有大量锁等待及少量慢语句
	
	(1) pt-query-diagest 查看慢日志
	
	(2) 锁等待有没有?
	db03 [(none)]>show status like 'innodb_row_lock%';
	+-------------------------------+-------+
	| Variable_name                 | Value |
	+-------------------------------+-------+
	| Innodb_row_lock_current_waits | 0     |
	| Innodb_row_lock_time          | 0     |
	| Innodb_row_lock_time_avg      | 0     |
	| Innodb_row_lock_time_max      | 0     |
	| Innodb_row_lock_waits         | 0     |
	+-------------------------------+-------+
	情况一:
			有100多个current_waits,说明当前很多锁等待情况
	情况二:
			1000多个lock_waits,说明历史上发生过的锁等待很多
	2.5 查看那个事务在等待(被阻塞了)
	2.6 查看锁源事务信息(谁锁的我)
	2.7 找到锁源的thread_id 
	2.8 找到锁源的SQL语句
3. 找到语句之后,和应用开发人员进行协商	
	(1)
	开发人员描述,此语句是事务挂起导致
	我们提出建议是临时kill 会话,最终解决问题
	(2) 
	开发人员查看后,发现是业务逻辑问题导致的死锁,产生了大量锁等待
	临时解决方案,将阻塞事务的会话kill掉.
	最终解决方案,修改代码中的业务逻辑
	
项目结果:
	经过排查处理,锁等待的个数减少80%.解决了CPU持续峰值的问题.
	

	锁监控设计到的命令:
	show status like 'innodb_rows_lock%'
	select * from information_schema.innodb_trx;
	select * from sys.innodb_lock_waits;
	select * from performance_schema.threads;
	select * from performance_schema.events_statements_current;
	select * from performance_schema.events_statements_history;
【推广】 免费学中医，健康全家人
原文地址：https://www.cnblogs.com/hujinzhong/p/11643442.html