mysql数据库锁关键字_MySQL——锁

锁是数据库系统区别于文件系统的一个关键特性。锁机制用于管理对共享资源的并发访问。

关键词InnoDB的锁类型多粒度锁不同粒度锁的比较

InnoDB 行锁实现方式——记录锁

共享锁与独占锁

意向锁

Record Lock

间隙锁

下一键锁

自增锁

InnoDB加锁隐式加锁

显式加锁共享锁(S)

排他锁(X)

锁的使用X锁的使用场景

S锁使用场景

性能影响

死锁死锁的产生、检测和恢复

InnoDB避免死锁

一些优化锁性能的建议

1.InnoDB的锁类型

1.1多粒度锁

MySQL 不同的存储引擎支持不同的锁机制，所有的存储引擎都以自己的方式显现了锁机制，服务器层完全不了解存储引擎中的锁实现：InnoDB 存储引擎既支持行级锁(row-level locking)，也支持表级锁，但默认情况下是采用行级锁。

默认情况下，表锁和行锁都是自动获得的， 不需要额外的命令。

但是在有的情况下， 用户需要明确地进行锁表或者进行事务的控制， 以便确保整个事务的完整性，这样就需要使用事务控制和锁定语句来完成。

1.1.1不同粒度锁的比较表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。这些存储引擎通过总是一次性同时获取所有需要的锁以及总是按相同的顺序获取表锁来避免死锁。

表级锁更适合于以查询为主，并发用户少，只有少量按索引条件更新数据的应用

行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。最大程度的支持并发，同时也带来了最大的锁开销。

在 InnoDB 中，除单个 SQL 组成的事务外，锁是逐步获得的，这就决定了在 InnoDB 中发生死锁是可能的。

行级锁只在存储引擎层实现，而Mysql服务器层没有实现。 行级锁更适合于有大量按索引条件并发更新少量不同数据，同时又有并发查询的应用，如一些在线事务处理(OLTP)系统

1.1.2InnoDB 行锁实现方式——记录锁InnoDB 行锁是通过给索引上的索引项加锁来实现的，这一点 MySQL 与 Oracle 不同，后者是通过在数据块中对相应数据行加锁来实现的。InnoDB 这种行锁实现特点意味着：只有通过索引条件检索数据，InnoDB 才使用行级锁，否则，InnoDB 将使用表锁！

不论是使用主键索引、唯一索引或普通索引，InnoDB 都会使用行锁来对数据加锁。

只有执行计划真正使用了索引，才能使用行锁

由于 MySQL 的行锁是针对索引加的锁，不是针对记录加的锁，所以虽然多个session是访问不同行的记录， 但是如果是使用相同的索引键， 是会出现锁冲突的(后使用这些索引的session需要等待先使用索引的session释放锁后，才能获取锁)

1.2共享锁与独占锁

InnoDB 实现了以下两种类型的行锁：共享锁(S 读锁)：允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁。

排他锁(X 写锁)：允许获得排他锁的事务更新数据，阻止其他事务取得相同数据集的共享读锁和排他写锁。

1.3意向锁

为了允许行锁和表锁共存，实现多粒度锁机制，InnoDB 还有两种内部使用的意向锁(Intention Locks)，这两种意向锁都是表锁：意向共享锁(IS)：事务打算给数据行加行共享锁，事务在给一个数据行加共享锁前必须先取得该表的 IS 锁。

意向排他锁(IX)：事务打算给数据行加行排他锁，事务在给一个数据行加排他锁前必须先取得该表的 IX 锁。

锁模式的兼容情况：锁模式的兼容情况

1.4Record Lock

单个行记录上的锁。

1.5间隙锁

间隙锁是锁定索引记录中的间隙，或锁定第一个索引和最后一个索引之间的间隙。

间隙锁的唯一目的是防止其他事务插入间隙中，不同事务的冲突间隙锁可以共存，S间隙锁和X间隙锁没有什么区别，他们执行相同的功能。

使用唯一索引搜索唯一的值的语句不需要间隙锁，如这条SqlSELECT * FROM child WHERE id = 100;只会锁定id这一行，如果id未编入索引或有非唯一索引

如果将事务隔离级别设置为已提交读可以明确禁用间隙锁。

1.6下一键锁

下一键锁是索引上的记录锁和索引之前的间隙锁的组合。

对查询加下一键锁时，加X锁范围为：

对于二级索引单值查询：锁定区间 (上一个索引的值,下一个索引的值](左开右闭)

范围查询：型如的范围为 > 上一个索引的值

对于主键索引或唯一索引单值查询：不加下一键锁，仅锁定这一行(降级为Record Lock)

范围查询：型如的范围为 >= 上一个索引的值

1.7自增锁

MySQL会为自增列设置一个表级锁,可配置innodb_autoinc_lock_mode控制并发度

为了提高插入性能，锁不是在一个事务完成后才释放，而是在完成对自增长值插入的SQL语句后立即释放。

2.InnoDB加锁

InnoDB在事务执行过程中，使用两阶段封锁协议：

随时都可以执行锁定，InnoDB会根据隔离级别在需要的时候自动加锁；

锁只有在执行commit或者rollback的时候才会释放，并且所有的锁都是在同一时刻被释放。

2.1隐式加锁意向锁是 InnoDB 自动加的， 不需用户干预。

对于 UPDATE、 DELETE 和 INSERT 语句， InnoDB会自动给涉及数据集加排他锁(X)；

2.2显式加锁

对于普通 SELECT 语句，InnoDB 不会加任何锁；事务可以通过以下语句显式给记录集加共享锁或排他锁：

2.2.1共享锁(S)

SELECT ... LOCK IN SHARE MODE。 其他 session 仍然可以查询记录，并也可以对该记录加 share mode 的共享锁。但是如果当前事务需要对该记录进行更新操作，则很有可能造成死锁。

2.2.2排他锁(X)

SELECT ... FOR UPDATE。其他 session 可以查询该记录，但是不能对该记录加共享锁或排他锁，而是等待获得锁。

3.锁的使用

3.1X锁的使用场景

为了让自己查到的数据确保是最新数据，并且查到后的数据只允许自己来修改的时候，需要用到 for update 子句。

3.2S锁使用场景

为了确保自己查到的数据没有被其他的事务正在修改，也就是说确保查到的数据是最新的数据，并且不允许其他人来修改数据。但是自己不一定能够修改数据，因为有可能其他的事务也对这些数据 使用了 in share mode 的方式上了 S 锁。

3.3性能影响

select for update 语句，相当于一个 update 语句。在业务繁忙的情况下，如果事务没有及时的commit或者rollback 可能会造成其他事务长时间的等待，从而影响数据库的并发使用效率。

select lock in share mode 语句是一个给查找的数据上一个共享锁(S 锁)的功能，它允许其他的事务也对该数据上S锁，但是不能够允许对该数据进行修改。如果不及时的commit 或者rollback 也可能会造成大量的事务等待。

4.死锁

4.1死锁的产生、检测和恢复死锁产生：死锁是指两个或多个事务在同一资源上相互占用，并请求锁定对方占用的资源，从而导致恶性循环。

当事务试图以不同的顺序锁定资源时，就可能产生死锁。多个事务同时锁定同一个资源时也可能会产生死锁。

锁的行为和顺序和存储引擎相关。以同样的顺序执行语句，有些存储引擎会产生死锁有些不会——死锁有双重原因：真正的数据冲突；存储引擎的实现方式。

检测死锁：数据库系统实现了各种死锁检测和死锁超时的机制。InnoDB存储引擎能检测到死锁的循环依赖并立即返回一个错误。

死锁恢复：死锁发生以后，只有部分或完全回滚其中一个事务，才能打破死锁，InnoDB目前处理死锁的方法是，将持有最少行级排他锁的事务进行回滚。所以事务型应用程序在设计时必须考虑如何处理死锁，多数情况下只需要重新执行因死锁回滚的事务即可。

外部锁的死锁检测：发生死锁后，InnoDB 一般都能自动检测到，并使一个事务释放锁并回退，另一个事务获得锁，继续完成事务。但在涉及外部锁，或涉及表锁的情况下，InnoDB 并不能完全自动检测到死锁， 这需要通过设置锁等待超时参数 innodb_lock_wait_timeout 来解决

死锁影响性能：死锁会影响性能而不是会产生严重错误，因为InnoDB会自动检测死锁状况并回滚其中一个受影响的事务。在高并发系统上，当许多线程等待同一个锁时，死锁检测可能导致速度变慢。 有时当发生死锁时，禁用死锁检测(使用innodb_deadlock_detect配置选项)可能会更有效，这时可以依赖innodb_lock_wait_timeout设置进行事务回滚。

4.2InnoDB避免死锁为了在单个InnoDB表上执行多个并发写入操作时避免死锁，可以在事务开始时通过为预期要修改的每个元祖(行)使用SELECT ... FOR UPDATE语句来获取必要的锁，即使这些行的更改语句是在之后才执行的。

在事务中，如果要更新记录，应该直接申请足够级别的锁，即排他锁，而不应先申请共享锁、更新时再申请排他锁，因为这时候当用户再申请排他锁时，其他事务可能又已经获得了相同记录的共享锁，从而造成锁冲突，甚至死锁

如果事务需要修改或锁定多个表，则应在每个事务中以相同的顺序使用加锁语句。 在应用中，如果不同的程序会并发存取多个表，应尽量约定以相同的顺序来访问表，这样可以大大降低产生死锁的机会

通过SELECT ... LOCK IN SHARE MODE获取行的读锁后，如果当前事务再需要对该记录进行更新操作，则很有可能造成死锁。

改变事务隔离级别

如果出现死锁，可以用 SHOW INNODB STATUS 命令来确定最后一个死锁产生的原因。返回结果中包括死锁相关事务的详细信息，如引发死锁的 SQL 语句，事务已经获得的锁，正在等待什么锁，以及被回滚的事务等。据此可以分析死锁产生的原因和改进措施。

5.一些优化锁性能的建议尽量使用较低的隔离级别；

精心设计索引， 并尽量使用索引访问数据， 使加锁更精确， 从而减少锁冲突的机会

选择合理的事务大小，小事务发生锁冲突的几率也更小

给记录集显示加锁时，最好一次性请求足够级别的锁。比如要修改数据的话，最好直接申请排他锁，而不是先申请共享锁，修改时再请求排他锁，这样容易产生死锁

不同的程序访问一组表时，应尽量约定以相同的顺序访问各表，对一个表而言，尽可能以固定的顺序存取表中的行。这样可以大大减少死锁的机会

尽量用相等条件访问数据，这样可以避免间隙锁对并发插入的影响

不要申请超过实际需要的锁级别

除非必须，查询时不要显示加锁。 MySQL的MVCC可以实现事务中的查询不用加锁，优化事务性能；MVCC只在COMMITTED READ(读提交)和REPEATABLE READ(可重复读)两种隔离级别下工作

对于一些特定的事务，可以使用表锁来提高处理速度或减少死锁的可能