门户网站设计要点免费的室内设计网站

一、前言（以下均为读完 高性能Mysql第四版 后的个人理解，建议阅读，挺不错的）

在写锁机制前先简单贴出mysql InnoDB引擎中的事务特性与隔离级别：

1. 事务的ACID标准

(1)原子性-atomicity：一个事务作为一个不可分割的工作单元，要么全部执行提交成功，要么全部执行失败回滚。

(2)一致性-consistency：数据库总是从一个一致性状态转换到下一个一致性状态，如若一个事务没有被提交成功，那么该事务所作的所有修改都不会保存到数据库中。

(3)隔离性-isolation：一个事务在最终提交之前，其所作的修改对其他事务是不可见的。

(4)持久性-durability：一个事务一旦提交，其所做的修改将永久保存到数据库，即使系统奔溃，数据也不会丢失。

2. 隔离级别

(1)READ UNCOMMITTED-读未提交：该隔离级别，事务中可以读取到其他事务未提交的修改，可能产生脏读。

(2)READ COMMITTED-读已提交：该隔离级别，事务只能读取到其他事务提交后的修改，属于不可重复读，可能产生在同一事务中同一条查询语句产生两种不同的结果，且可能产生幻读。

(3)READ COMMITTED-可重复读(InnoDB默认隔离级别)：该隔离级别解决了 读已提交 隔离级别的不可重复读问题，保证在同一个事务中多次读取相同行数据的结果是一样的，但可能产生幻读。

(4)SERIALIZABLE-可串行化：强制事务按顺序执行，解决事务之间的冲突，属于加锁读。

二、共享锁与排他锁

1.共享锁，简称S锁，也称读锁，多事务可共享一把锁，可读不可写（实践证明先获得共享锁的事务可修改数据，后续其他事务不可修改）。

申请行级共享锁示例：select id from table1 where id = 1 lock in share mode

2.排他锁，简称X锁，也称写锁，排他锁不可与其他锁共存，包括共享锁和排他锁，如事务1获取到某记录的排他锁且未释放时，那么其他事务在获取该记录的共享锁和排他锁时将被阻塞。

申请行级排他锁示例：select id from table1 where id = 1 for update

updat delete insert语句均会自动申请排他锁

3.普通select语句会不会申请共享锁或排他锁？

不会，普通select语句属于快照读，没有任何锁机制。

三、间隙锁

上面说过，在InnoDB默认隔离级别可重复读级别下，会产生幻读问题，实际上InnoDB默认使用了间隙锁策略来防止幻读产生。

什么是幻读：当某个事务在读取某个范围内的记录时，另外一个事务又在该范围内插入了新的记录，当之前的事务再次读取该范围的记录时，会产生幻行，此时就会产生幻读。

简介：

(1)行锁:锁直接加在索引记录上面，锁住的是key。

(2)间隙锁:锁定索引记录间隙，确保索引记录的间隙不变。间隙锁是针对事务隔离级别为可重复读或以上级别。

(3)行锁和间隙锁组合起来叫Next-Key Lock。

默认情况下，InnoDB工作在可重复读隔离级别下，并且会以Next-Key Lock的方式对数据行进行加锁，这样可以有效防止幻读的发生。Next-Key Lock是行锁和间隙锁的组合，当InnoDB扫描索引记录的时候，会对索引记录加上行锁，再对索引记录两边的间隙加上间隙锁。加上间隙锁之后，其他事务就不能在这个间隙修改或者插入记录。

间隙锁在InnoDB的作用就是防止其他事务的插入操作，以此防止幻读的发生。

(4)间隙锁产生条件：

当使用范围条件去检索唯一索引列，未加索引列，非唯一索引列，并申请共享/排他锁时，会申请间隙锁。

当使用等值条件去检索未加索引列，非唯一索引列，并申请共享/排他锁时，会申请间隙锁。

注意：若检索条件未加索引，sql可能会走聚簇索引的全表扫描进行过滤，这时表中每条记录都会加上排他锁，但是对于不满足条件的记录会在加锁后立马释放锁，最终持有锁的是满足条件的记录。不满足条件的记录会有上锁又释放锁的过程。

四、意向锁

InnoDB支持多粒度锁，表锁和行锁可同时存在，意向锁为表锁中的一种。

1.意向锁分为意向共享锁和意向排他锁：

(1)：意向共享锁-事务有意向给表中某行申请共享锁。

(2)：意向排他锁-事务有意向给表中某行申请排他锁。

示例：

该事务申请某些行的共享锁之前，必须先申请该表的意向共享锁
select id from table1 where id = 1 lock in share mode

该事务申请某些行的排他锁之前，必须先申请该表的意向排他锁
select id from table1 where id = 1 for update

2.意向锁的互斥性：

(1)首先意向锁是表级锁，不与行级锁互斥，也就是说意向锁不会与行级的共享/排他锁互斥，可以共同存在。

(2)意向共享锁与表级的共享锁互相兼容，可共同存在，但与表级排他锁互斥。

(3)意向排他锁与表级的共享/排他锁均互斥

示例：

事务A申请了id=1的记录的排他锁，并未提交事务，此时表table1存在两把锁，即表table1上的意向排他锁和id=1记录上的行级排他锁
select id from table1 where id = 1 for update

事务B获取表table1的表级共享锁，
LOCK TABLES table1 READ;

此时事务 B 检测事务 A 持有 table1 表的意向排他锁，就可以得知事务 A 必然持有该表中某些数据行的排他锁，那么事务 B 对 table1 表的加锁请求就会被阻塞，而无需去检测表中的每一行数据是否存在排他锁，极大提高效率。

五、死锁

1.死锁是指两个或多个事务相互持有和请求相同资源上的锁，产生了循环依赖。当多个事务试图以不同的顺序锁定资源时会导致死锁。当多个事务锁定相同的资源时，也可能会发生死锁。例如，设想运行下面两个针对主键为（stock_id，date）的StockPrice表的事务：

事务A

事务B

当两个事务同时执行完第一条sql，此时互相持有id=3和id=4的数据行的排他锁，而锁并没有被释放，当执行第二条sql时，双方均永远等待对方释放锁，这就导致死锁的产生。

2.如何解决：

(1)：InnoDB实现了死锁检测和锁超时机制，一般来说都能自动检测到，并使一个或多个事务释放锁并回滚，剩下的事务获得锁，继续完成事务。

(2)：

查看表是否在使用
show open tables where in_use > 0 
如果查询结果不为空，继续。查看数据库当前进程，看一下有无正在执行的慢SQL线程
show processlist当前运行的所有事务
SELECT * FROM information_schema.INNODB_TRX当前出现的锁
SELECT * FROM information_schema.INNODB_LOCKs锁等待的对应关系
SELECT * FROM information_schema.INNODB_LOCK_waits看事务表INNODB_TRX，里面是否有正在锁定的事务线程，看看ID（表INNODB_TRX的trx_mysql_thread_id字段）是否在show processlist里面的sleep线程中，如果是，就证明这个sleep的线程事务一直没有commit或者rollback而是卡住了，需要手动kill掉。