一、一条update语句
二、Mysql锁介绍
按照锁的粒度来说，Mysql主要包含三种类型的锁定机制：全局锁、表级锁、行级锁
全局锁：锁的是整个database，由Mysql的Sql layer层实现。
表级锁：锁的是整张表，由Mysql的Sql layer层实现。
行级锁：锁的是某行数据，也可能锁定行之间的间隙，由存储引擎实现。
按照锁的功能来分，可以分为：共享锁和排他锁
共享锁（Shared Locks）：称为S锁，加了S锁，允许其他事务再加S锁，但是不允许其他事物加X锁；加锁方式：select ...... lock in share mode
排他锁（Exclusive Locks）：称为X锁，加了X锁的记录，不允许其他事物加S锁或X锁，加锁方式：select ...... for update
三、全局锁
全局锁就是对整个数据库实例加锁，加锁后整个实例就处于只读状态。后续的MDL、DDL语句，已经更新操作的事务提交语句都将被阻塞，其典型的使用场景就是做全库的逻辑备份，对所有的表进行锁定，从而获取一致性视图，保证数据的完整性。
加全局锁和释放全局锁的命令：
flush tables with read lock;
unlock tables;
或者断开加锁的session连接后，也可以自动释放全局锁。
但是全局锁用于备份的这个问题，还是非常危险的，因为如果在主库上加全局锁，则整个数据库不能进行写入操作，数据备份期间影响业务，如果在从库上家全局锁，会导致不能执行主库上同步过来的操作，造成主从延迟。
对于innodb这种支持事务的存储引擎，使用mysqldump备份时，可以使用--single-transaction参数，利用mvcc提供的一致性视图，而不是用全局锁，不影响业务的正常进行；但是对于MyISAM这种不支持事务的表，就只能通过全局锁获得一致性视图，对应的mysqldump参数为--lock-all-tables。
四、Mysql表级锁
Mysql的表级锁有表读写锁、元数据锁、意向锁和自增锁。
1、表读写锁
mysql实现的表级锁相关的定义变量查询：
mysql> show status like 'table%'; +----------------------------+-------+ | Variable_name | Value | +----------------------------+-------+ | Table_locks_immediate | 210 | | Table_locks_waited | 0 | | Table_open_cache_hits | 0 | | Table_open_cache_misses | 0 | | Table_open_cache_overflows | 0 | +----------------------------+-------+
其中比较重要的是前两个，Table_locks_immediate：产生表级锁定的次数；Table_locks_waited：产生表级锁定争用而发生等待的次数。
表锁有两种表现形式：表共享读锁（Table Read Lock）、表独占写锁（Table Write Lock）
手动增加表锁：lock table tablename write/read ；
查看表锁情况：show open tables；
删除表锁：unlock table；
mysql> lock table city read,film write,actor read; Query OK, 0 rows affected (0.00 sec) mysql> show open tables; +--------------------+----------------------------+--------+-------------+ | Database | Table | In_use | Name_locked | +--------------------+----------------------------+--------+-------------+ | sakila | sales_by_store | 0 | 0 | | sakila | actor_info | 0 | 0 | | sakila | film_actor | 0 | 0 | | sakila | staff | 0 | 0 | | sakila | country | 0 | 0 | | sakila | address | 0 | 0 | | sakila | category | 0 | 0 | | sakila | staff_list | 0 | 0 | | sakila | sales_by_film_category | 0 | 0 | | sakila | rental | 0 | 0 | | sakila | language | 0 | 0 | | sakila | store | 0 | 0 | | sakila | actor | 1 | 0 | | sakila | film_text | 3 | 0 | | sakila | film_category | 0 | 0 | | sakila | film | 1 | 0 | | sakila | inventory | 0 | 0 | | sakila | nicer_but_slower_film_list | 0 | 0 | | sakila | city | 1 | 0 | | sakila | film_list | 0 | 0 | | sakila | customer_list | 0 | 0 | | sakila | customer | 0 | 0 | | performance_schema | session_status | 0 | 0 | | sakila | payment | 0 | 0 | +--------------------+----------------------------+--------+-------------+ 24 rows in set (0.00 sec) mysql> unlock tables; Query OK, 0 rows affected (0.00 sec)
读锁说明：获得读锁的session，不能查询其他没有加读锁的表，当前session对锁定的表只能读，不能写（会报错）；其他session可以独写没有加读锁的表，对于加读锁的表，其他session只能读，写入会阻塞。
写锁说明：获得写锁的session，对于锁定的表可以进行读写操作，但是其他session查询锁定的表，会被阻塞。
2、元数据锁
MDL（元数据锁）不需要显式的使用，在访问一个表的时候会被自动加上，MDL的作用是保证读写的正确性，主要就是防止在做增删改查时其他session对表结构进行变更，因此在Mysql5.5版本中引入了MDL，当一个表做增删改操作时，加MDL读锁，当要对表结构做变更时，加MDL写锁。
3、意向锁
InnoDB也实现了表级锁，也就是意向锁，意向锁是mysql内部使用的，不需要用户干预，意向锁和行锁可以共存，意向锁的主要作用是为了全表更新数据时的性能提升，否则在进行全表数据更新时，需要先检索该范围中是否有数据存在行锁。
意向锁表明某个事物正在某些行持有锁，或者事务准备去持有锁
意向锁的存在是为了协调行锁和表锁的关系，支持多粒度的锁并存
举个栗子，事务A修改user表中的某一行数据，会对这条数据加一个行锁，同时会给user表加一个意向排他锁，当其他事务要给user表加排他锁时，就会被阻塞。
通过意向锁，实现了行锁与表锁共存且满足事务隔离性的要求。
意向锁分为意向排他锁（IS锁，事务在请求S锁前，需要先获得IS锁）和意向共享锁（IX锁，事务在请求X锁前，需要先获取IX锁）
意向锁、共享锁、排他锁的兼容关系如下所示：
| 是否兼容 | 当事务A上了IS锁 | IX锁 | S锁 | X锁 |
| 当事务B上了IS锁 | 是 | 是 | 是 | 否 |
| IX锁 | 是 | 是 | 否 | 否 |
| S锁 | 是 | 否 | 是 | 否 |
| X锁 | 否 | 否 | 否 | 否 |
对上面的表格做一下说明：意向锁相互兼容，i那位IX、IS只是表明申请更低层次级别元素的X、S操作，因为上了表级S锁后，不许允其他事物加X锁，因此表级S锁和X、IX不兼容；上了表级X锁后，会修改数据，所以表级X锁和IS、IX、S、X不兼容。
4、自增锁
AUTO_INC锁是一种特殊的表级锁，发生涉及AUTO_INCREMENT列的事务性插入操作时。
五、Mysql行级锁
Mysql的行级锁，是由存储引擎来实现的，这里主要说明InnoDB的行级锁。
InnoDB行锁是通过给索引上的索引项加锁来实现的，因此InnoDB这种行锁实现的特点意味着，只有通过索引条件检索的数据，InnoDB才会使用行级锁，否则，InnoDB将使用表级锁。
InnoDB的行级锁，按照功能来划分，可以分为：共享锁（select ...... lock in mode share）和排他锁（select ...... for update）
InnoDB的行锁分为：记录锁、间隙锁、临键锁、插入意向锁；
1、记录锁（Record Locks）
记录锁是锁定索引中的一条记录；
记录锁锁定的永远是索引，而非记录本身，即使表中没有创建索引，InnoDB也会创建一个隐藏的聚簇主键索引，然后锁定该索引。所以说当一条sql没有走任何索引时，那么将会在每一条聚合索引后面加X锁，这个类似表锁，但是原理上和表锁应该是完全不同的。
2、间隙锁（Gap Locks）
间隙锁要么锁定索引记录中间的值，要么锁定第一个索引记录前面的值或者最后一个索引后面的值；也就是说，间隙锁锁定的是一个区间（开区间）。
间隙锁可以方式幻读，保证索引间不会有数据插入。
3、临键锁（Next-Key Locks）
临键锁是索引记录上的记录锁和间隙锁的组合；间隙锁是一个开区间，而临键锁由于是间隙锁和记录锁的集合，因此就是一个左开右闭的区间。
默认情况下，innodb使用next-key locks来锁定记录，select ...... for update，但是当查询的索引含有唯一属性的时候，Next-key Lock就会被优化，将其降级为记录锁。
临键锁在不同场景下会发生退化，例如：（1）在使用唯一索引（unique index）精确匹配（=）且存在记录时，退化为记录锁；（2）在使用唯一索引（unique index）精确匹配（=）且不存在记录时，退化为间隙锁；（3）在使用唯一索引（unique index）范围匹配（>或<），不会退化。
例如：当前数据库中数据如下
CREATE TABLE `t1_simple` ( `id` int(11) NOT NULL, `pubtime` int(11) DEFAULT NULL, PRIMARY KEY (`id`) USING BTREE, KEY `idx_pu` (`pubtime`) USING BTREE ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
session1使用sql：select * from t1_simple where pubtime = 20 for update; 时由于puttime是索引，但是非唯一索引，且命中了20的值，因此其使用间隙锁（10,20],(20,100]
session2使用不同的sql时，执行结果如下：
insert into t1_simple values (16, 19); --阻塞 select * from t1_simple where pubtime = 20 for update; --阻塞 insert into t1_simple values (16, 50); --阻塞 insert into t1_simple values (16, 101); --成功
那么对于行锁的加入规则如下：
| 索引类型 | 查询类型 | 是否命中数据 | 加锁类型 |
| 主键索引 | 等值查询 | 命中纪录 | 记录锁 |
| 未命中记录 | 间隙锁 | ||
| 范围查询 | 命中纪录 | 临键锁 | |
| 未命中记录 | 间隙锁 | ||
| 辅助索引 | 等值查询 | 命中纪录 | 命中记录的辅助索引项+主键索引项加记录锁，辅助索引两侧加间隙锁 |
| 未命中记录 | 间隙锁 | ||
| 范围查询 | 命中纪录 | 辅助索引加临键锁，命中记录的主键索引加记录锁 | |
| 未命中记录 | 间隙锁 |
4、插入意向锁（Insert Intention Locks）
是做insert操作时添加对记录ID的锁。
插入意向锁是一种间隙锁，而不是意向锁，在Insert时产生，在多事务同时写入不同数据到同一索引间隙的时候，并不需要等待其他事务完成，不会发生锁等待。例如一个记录索引包含4和7，然后两个事务分别要向里面插入5和6，每个事务都会在4~7之间加入一个插入意向锁，获取在插入行之间的意向锁，但是这两个事务并不会发生锁冲突，因为插入的两个数据行并不一样。
插入意向锁并不会阻止任何锁，对于插入的记录会持有一个记录锁
5、行锁的相关参数
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_row_lock_current_waits：当前正在等待锁定的数量；Innodb_row_lock_time：从系统启动到现在锁定的总时长；Innodb_row_lock_time_avg：从系统启动到现在每次平均等待花费的时长； Innodb_row_lock_time_max：从系统启动到现在等待最长一次花费的时间；Innodb_row_lock_waits：从系统启动到现在总共等待的次数。
这5个状态变量，比较重要的是等待总时长、等待次数和平均等待时长，尤其当等待次数很高且每次等待的时长也不小的时候，就需要分析系统中为什么会有如此多的等待，然后根据分析结果制定优化计划。
查看事务和锁的sql：
select * from information_schema.innodb_locks; select * from information_schema.innodb_lock_waits; select * from information_schema.innodb_trx;
六、行锁原理分析
上面对锁做了描述，那么对于SQL是否加锁，加什么锁，我们要学会分析，比如说单纯的查询，那么肯定不加锁，但是例如下面sql，是否加锁？加什么锁呢？
delete from t1 where id = 10;
对于是否加锁这个问题，可以肯定的回答，肯定加锁，但是，到底加什么锁，这个还需要根据不同的事务隔离级别和id是否是索引，是否是主键索引来分析。
1、RC-读已提交&id是主键
结论：加排他记录锁。
这个组合只需要在主键id=10的记录上加X锁就可以了，如图：
2、RC-读已提交&辅助索引&唯一索引
结论：分别在id列的索引上和主键索引上加两个X锁。
由于id是唯一索引，因此在唯一索引查询到的记录上加上排他行锁，然后需要回主键索引上对主键索引对应的数据也加一个排他记录锁。
为什么主键索引也要加排他行锁：因为有可能其他事务根据其他的索引对数据进行变更，就会绕过id的锁，但实际上是对同一条数据做的修改。
3、RC-读已提交&辅助索引&非唯一索引
结论：对于满足条件的id索引都会加排他记录锁，同时这些记录对应的主键索引也会加排他记录所。
非唯一索引和唯一索引唯一的区别就是条件可能命中多条记录，其余的情况都一样。
4、RC-读已提交&辅助索引&非索引
结论：SQL会走聚簇索引的全表扫描进行过滤，由于过滤是由Mysql Server层面进行的，因此每条记录，无论是否满足，都会被加上X锁，但是为了效率考虑，Mysql做了优化，对于不满足条件的数据，会在SQL Server过滤后释放锁，最终持有锁的都是满足条件的记录，但是不满足条件的数据加锁和解锁过程仍然存在。
5、RR-可重复读&id是主键
结论：加排他记录锁，与 RC-读已提交&id是主键 情况一致
6、RR-可重复读&辅助索引&唯一索引
结论：分别在id列的索引上和主键索引上加两个X锁，与 RC-读已提交&id是唯一索引 情况一致
7、RR-可重复读&辅助索引&非唯一索引
结论：在id索引值上加记录锁，然后还需要加上间隙锁，主键索引加记录锁。
由于可重复读需要保证再一次事务内，读取到的数据前后必须是一致的，那么就是要保证在该事务可能影响的范围内，不能有其他事物对数据做变更；同时由于id是非唯一索引，因此可能产生有多个id=10的数据，如上图所示，那么就需要将所有id=10的id索引加记录锁，然后在加上区间的间隙锁，以保证不会有数据在可影响范围插入数据，例如在第一个间隙锁中加入10a，第二个间隙锁中加入10c，第三个间隙锁中加入10f，其都会影响id=10的查询结果。然后对于主键索引加记录锁就无须多说了，和上面一样。
这里要说明一下，为什么唯一索引不需要加间隙锁，而非唯一索引就需要加：对于非唯一索引为什么要加间隙锁，上面已经说明，那么唯一索引为什么不需要加间隙所呢？是因为唯一索引不会出现重复数据，其影响数据只会有一条，因此只加记录锁就可以了。
8、RR-可重复读&辅助索引&非索引
结论：如果id非索引，那么就会使用全表扫，也就是获取所有的聚簇索引，然后查找数据。在可重复读隔离级别下那么就会锁上所有的记录，对所有的记录加记录锁，同时对表中的所有主键间隙加间隙锁，以防止所有的数据变更操作。
我们可以通过触发semi-consistentread来缓解加锁开销和并发影响，但是semi-consistentread也有其他问题，不建议使用。
9、Serializable-串行化
结论：在InnoDB中，所谓的都不加锁不适用所有的隔离级别，对于串行化，就会加锁。
由于可重复读隔离级别下都是快照读，不加锁，因此可能存在各种加锁情况，但是在串行化隔离级别下，都是当前读，对于select查询语句也会加锁。
七、一条复杂SQL的加锁分析
接下来分析一下下图的delete语句会加什么锁？
在读已提交隔离级别下：
如果在没有ICP的情况下（Mysql5.6之前），组合索引中第一列pubtime使用了范围查询，因此只有第一列有效，第二列userid不在生效，同时该索引为非主键索引、非唯一索引，那么会在isx_t1_pu索引中对数据3，5，10加行锁，然后在主键索引上，id为4，8，1的数据上加行锁；
如果存在ICP的情况下（Mysql5.6及以后），会发生索引下沉，因此在InnoDB中就会对不满足的数据进行过滤，因此在isx_t1_pu索引中对数据5，10加行锁，主键索引的记录锁也变为8，1
在可重复读的情况下：
如果在没有ICP的情况下（Mysql5.6之前），组合索引中第一列pubtime使用了范围查询，因此只有第一列有效，第二列userid不在生效，同时该索引为非主键索引、非唯一索引，那么会在isx_t1_pu索引中对数据3，5，10加行锁，在1~3，3~5，5~10，10~20之间加间隙锁，然后在主键索引上，id为4，8，1的数据上加行锁；
如果存在ICP的情况下（Mysql5.6及以后），会发生索引下沉，因此在InnoDB中就会对不满足的数据进行过滤，因此在isx_t1_pu索引中对数据5，10加行锁，然后间隙锁为1~5，5~10，10~20，主键索引的记录锁也变为8，1
其结果如下图所示，在有ICP的情况下，红色部分不再有行锁，没有ICP的情况下，红色部分也会有。
八、死锁原理与分析
对于锁的学习，在开发过程中，主要可以帮我们写出不发生死锁的sql，同时如果出现死锁，那么可以定位出产生死锁的原因，并加以优化。
以下是比较典型的两种死锁产生的情况
1、两个session的两条SQL产生死锁
这个很好理解，也是常见的死锁，每个事务执行两条SQL，分别持有了一把锁，然后加另一把锁，产生死锁
2、两个session的一条SQL产生死锁
这种情况，就是由于使用了不同的索引列进行数据变更，但是不同的索引列刚好对应了相同的两条主键索引，并且其使用的索引列的顺序对应主键索引的顺序刚好是相反的，就产生了死锁。
说了常见的死锁产生的原因，那么如何避免死锁呢：
（1）注意程序的逻辑：需要避免交叉更新
（2）保持事务轻量：在一个事务中尽量少的持有锁，这样发生死锁的可能性就越小
（3）提高运行速度：尽量避免使用子查询，尽量使用主键等。
（4）尽早提交事务，减少持有锁的时间，如果在spring中，在service层需要处理大量逻辑处理，建议将数据组装和事务处理拆分开，在同一个Service中方法调用开启事务：((xxxService) AopContext.currentProxy()).doSome()