mysql记录锁与互斥锁区别_MySQL的各种锁认知

一、相关名词

|--表级锁(锁定整个表)

|--页级锁(锁定一页)

|--行级锁(锁定一行)

|--共享锁(S锁，MyISAM 叫做读锁)

|--排他锁(X锁，MyISAM 叫做写锁)

|--悲观锁(抽象性，不真实存在这个锁)

|--乐观锁(抽象性，不真实存在这个锁)

二、InnoDB与MyISAM

Mysql 在5.5之前默认使用 MyISAM 存储引擎，之后使用 InnoDB 。查看当前存储引擎：

show variables like '%storage_engine%';

MyISAM 操作数据都是使用的表锁，你更新一条记录就要锁整个表，导致性能较低，并发不高。当然同时它也不会存在死锁问题。

而 InnoDB 与 MyISAM 的最大不同有两点：一是 InnoDB 支持事务；二是 InnoDB 采用了行级锁。也就是你需要修改哪行，就可以只锁定哪行。

在 Mysql 中，行级锁并不是直接锁记录，而是锁索引。索引分为主键索引和非主键索引两种，如果一条sql 语句操作了主键索引，Mysql 就会锁定这条主键索引；如果一条语句操作了非主键索引，MySQL会先锁定该非主键索引，再锁定相关的主键索引。

InnoDB 行锁是通过给索引项加锁实现的，如果没有索引，InnoDB 会通过隐藏的聚簇索引来对记录加锁。也就是说：如果不通过索引条件检索数据，那么InnoDB将对表中所有数据加锁，实际效果跟表锁一样。因为没有了索引，找到某一条记录就得扫描全表，要扫描全表，就得锁定表。

三、共享锁与排他锁

1.首先说明：数据库的增删改操作默认都会加排他锁，而查询不会加任何锁。

|--共享锁：对某一资源加共享锁，自身可以读该资源，其他人也可以读该资源(也可以再继续加共享锁，即 共享锁可多个共存)，但无法修改。要想修改就必须等所有共享锁都释放完之后。语法为：

select * from table lock in share mode

|--排他锁：对某一资源加排他锁，自身可以进行增删改查，其他人无法进行任何操作。语法为：

select * from table for update --增删改自动加了排他锁

2.下面援引例子说明(援自：http://blog.csdn.net/samjustin1/article/details/52210125)：

这里用T1代表一个数据库执行请求，T2代表另一个请求，也可以理解为T1为一个线程，T2 为另一个线程。

例1：-------------------------------------------------------------------------------------------------------------------------------------

T1:select * from table lock in share mode(假设查询会花很长时间，下面的例子也都这么假设)

T2:update table set column1='hello'

过程：

T1运行(并加共享锁)

T2运行

If T1还没执行完

T2等......

else锁被释放

T2执行

endif

T2 之所以要等，是因为 T2 在执行 update 前，试图对 table 表加一个排他锁，而数据库规定同一资源上不能同时共存共享锁和排他锁。所以 T2 必须等 T1 执行完，释放了共享锁，才能加上排他锁，然后才能开始执行 update 语句。

例2：-------------------------------------------------------------------------------------------------------------------------------------

T1:select * from table lock in share mode

T2:select * from table lock in share mode

这里T2不用等待T1执行完，而是可以马上执行。

分析：

T1运行，则 table 被加锁，比如叫lockAT2运行，再对 table 加一个共享锁，比如叫lockB两个锁是可以同时存在于同一资源上的(比如同一个表上)。这被称为共享锁与共享锁兼容。这意味着共享锁不阻止其它人同时读资源，但阻止其它人修改资源。

例3：-------------------------------------------------------------------------------------------------------------------------------------

T1:select * from table lock in share mode

T2:select * from table lock in share mode

T3:update table set column1='hello'

T2 不用等 T1 运行完就能运行，T3 却要等 T1 和 T2 都运行完才能运行。因为 T3 必须等 T1 和 T2 的共享锁全部释放才能进行加排他锁然后执行 update 操作。

例4：(死锁的发生)-----------------------------------------------------------------------------------------------------------------

T1:begin transelect * from table lock in share modeupdate table set column1='hello'

T2:begin transelect * from table lock in share modeupdate table set column1='world'

假设 T1 和 T2 同时达到 select，T1 对 table 加共享锁，T2 也对 table 加共享锁，当 T1 的 select 执行完，准备执行 update 时，根据锁机制，T1 的共享锁需要升级到排他锁才能执行接下来的 update.在升级排他锁前，必须等 table 上的其它共享锁(T2)释放，同理，T2 也在等 T1 的共享锁释放。于是死锁产生了。

例5：-------------------------------------------------------------------------------------------------------------------------------------

T1:begin tranupdate table set column1='hello' where id=10

T2:begin tranupdate table set column1='world' where id=20

这种语句虽然最为常见，很多人觉得它有机会产生死锁，但实际上要看情况

|--如果id是主键(默认有主键索引)，那么T1会一下子找到该条记录(id=10的记录)，然后对该条记录加排他锁，T2，同样，一下子通过索引定位到记录，然后对id=20的记录加排他锁，这样T1和T2各更新各的，互不影响。T2也不需要等。

|--如果id是普通的一列，没有索引。那么当T1对id=10这一行加排他锁后，T2为了找到id=20，需要对全表扫描。但因为T1已经为一条记录加了排他锁，导致T2的全表扫描进行不下去(其实是因为T1加了排他锁，数据库默认会为该表加意向锁，T2要扫描全表，就得等该意向锁释放，也就是T1执行完成)，就导致T2等待。

死锁怎么解决呢？一种办法是，如下：

例6：-------------------------------------------------------------------------------------------------------------------------------------

T1:begin transelect * from table for updateupdate table set column1='hello'

T2:begin transelect * from table for updateupdate table set column1='world'

这样，当 T1 的 select 执行时，直接对表加上了排他锁，T2 在执行 select 时，就需要等 T1 事物完全执行完才能执行。排除了死锁发生。但当第三个 user 过来想执行一个查询语句时，也因为排他锁的存在而不得不等待，第四个、第五个 user 也会因此而等待。在大并发情况下，让大家等待显得性能就太友好了。

所以，有些数据库这里引入了更新锁(如Mssql，注意：Mysql不存在更新锁)。

例7：-------------------------------------------------------------------------------------------------------------------------------------

T1:begin transelect * from table (加更新锁)update table set column1='hello'

T2:begin transelect * from table (加更新锁)update table set column1='world'

更新锁其实就可以看成排他锁的一种变形，只是它也允许其他人读(并且还允许加共享锁)。但不允许其他操作，除非我释放了更新锁。T1 执行 select，加更新锁。T2 运行，准备加更新锁，但发现已经有一个更新锁在那儿了，只好等。当后来有 user3、user4...需要查询 table 表中的数据时，并不会因为 T1 的 select 在执行就被阻塞，照样能查询，相比起例6，这提高了效率。

后面还有意向锁和计划锁：意向锁即是：某行修改时，自动加上了排他锁，同时会默认给该表加意向锁，表示里面有记录正被锁定，这时，其他人就不可以对该表加表锁了。如果没有意向锁这个类似指示灯的东西存在，其他人加表锁之前就得扫描全表，查看是否有记录正被锁定，效率低下。而计划锁这些，和程序员关系不大，就没去了解了。

悲观锁(Pessimistic Lock), 顾名思义，就是很悲观，每次去拿数据的时候都认为别人会修改，所以每次在拿数据的时候都会上锁，这样别人想拿这个数据就会block直到它拿到锁。传统的关系型数据库里边就用到了很多这种锁机制，比如行锁，表锁等，读锁，写锁等，都是在做操作之前先上锁。

乐观锁(Optimistic Lock), 顾名思义，就是很乐观，每次去拿数据的时候都认为别人不会修改，所以不会上锁，但是在更新的时候会判断一下在此期间别人有没有去更新这个数据，可以使用版本号等机制。乐观锁适用于多读的应用类型，这样可以提高吞吐量，像数据库如果提供类似于write_condition机制的其实都是提供的乐观锁。

两种锁各有优缺点，不可认为一种好于另一种，像乐观锁适用于写比较少的情况下，即冲突真的很少发生的时候，这样可以省去了锁的开销，加大了系统的整个吞吐量。但如果经常产生冲突，上层应用会不断的进行retry，这样反倒是降低了性能，所以这种情况下用悲观锁就比较合适。

扩展：

本质上，数据库的乐观锁做法和悲观锁做法主要就是解决下面假设的场景，避免丢失更新问题： 一个比较清楚的场景 下面这个假设的实际场景可以比较清楚的帮助我们理解这个问题： 假设当当网上用户下单买了本书，这时数据库中有条订单号为001的订单，其中有个status字段是’有效’,表示该订单是有效的； 后台管理人员查询到这条001的订单，并且看到状态是有效的 用户发现下单的时候下错了，于是撤销订单，假设运行这样一条SQL: update order_table set status = ‘取消’ where order_id = 001; 后台管理人员由于在b这步看到状态有效的，这时，虽然用户在c这步已经撤销了订单，可是管理人员并未刷新界面，看到的订单状态还是有效的，于是点击”发货”按钮，将该订单发到物流部门，同时运行类似如下SQL，将订单状态改成已发货:update order_table set status = ‘已发货’ where order_id = 001

观点1：只有冲突非常严重的系统才需要悲观锁；“所有悲观锁的做法都适合于状态被修改的概率比较高的情况，具体是否合适则需要根据实际情况判断。”，表达的也是这个意思，不过说法不够准确；的确，之所以用悲观锁就是因为两个用户更新同一条数据的概率高，也就是冲突比较严重的情况下，所以才用悲观锁。

观点2：最后提交前作一次select for update检查，然后再提交update也是一种乐观锁的做法，的确，这符合传统乐观锁的做法，就是到最后再去检查。但是wiki在解释悲观锁的做法的时候，’It is not appropriate for use in web application development.’， 现在已经很少有悲观锁的做法了，所以我自己将这种二次检查的做法也归为悲观锁的变种，因为这在所有乐观锁里面，做法和悲观锁是最接近的，都是先select for update，然后update

在实际应用中我们在更新数据的时候，更严谨的做法是带上更新前的“状态”，如

update order_table set status = ‘取消’ where order_id = 001 and status = ‘待支付’ and ..........;

update order_table set status = ‘已发货’ where order_id = 001 and status = ‘已支付’ and ..........;

然后在业务逻辑代码里判断更新的记录数，为0说明数据库已经被更新了，否则是正常的。

行锁,表锁

InnoDB存储引擎中有行锁以及表锁,行锁是InnoDB中默认的锁。

表锁：对整张表进行加锁，在同一时刻整张表的所有记录都被锁住。

行锁：只对表中的某一行记录进行加锁，表的其余行不会被占用，但是可能会出现死锁。

关闭事务自动提交

查看一下表数据

接着我们更新一条数据

执行成功之后我们并没有提交事务，这个时候这一条记录已经是加了锁的，所以我们在另外一个客户端更新同样的行记录。

自然就报错了，直接就等待超时了。这里证明已经加锁了，接着我们来证明是行锁还是表锁。

当我们执行update的时候，是update 字段a=1的 所以我们在update字段a=2的时候，虽然没有提交事务但是还是可以执行的，这里证明了InnoDB是行锁的。

注意：行锁必须有索引才能实现，否则就会自动锁住全表，也就是表锁，而InnoDB当有主键的时候，自动就会创建主键索引。

行锁与表锁的区别

行锁

优点 ：粒度小， 因为加锁的只是一行数据。

缺点 ：获取、释放所需要做的工作更多，并且容易发生死锁。

锁的优化：

合理设计索引

减少基于范围的数据检索过滤条件

尽量控制事务的大小，尽量使用较低的事务隔离级别

尽可能让所有的数据检索都通过索引来完成。

表锁

优点：获取跟释放快，能避免死锁(当执行update语句的时候，把整个表锁住了，其他的sql无法执行，所以不会造成死锁)

缺点：粒度太大，并发不够高，当并发量较多的时候，锁表会让进程无法继续执行sql。

死锁

死锁出现在行锁中，假设现在有一个T1的session线程去update一个数据库表table1 ，而且有一个T2的session线程去update一个数据库表table2。

在没有提交事务的时候，table1跟table2都已经进行了加锁，这个时候,T1去操作了table2，那么这个时候因为table2的记录加了锁，那么T1会一直在等待，接着T2又同样的去操作table1的表记录，也同样在等待，就造成了死锁。

InnoDB 支持多粒度锁(multiple granularity locking)，它允许行级锁与表级锁共存，而意向锁就是其中的一种表锁。

意向锁(Intention Locks)

需要强调一下，意向锁是一种不与行级锁冲突的表级锁，这一点非常重要。意向锁分为两种：

意向共享锁 (intention shared lock, IS)：事务有意向对表中的某些行加 共享锁 (S锁) -- 事务要获取某些行的 S 锁，必须先获得表的 IS 锁。 SELECT column FROM table ... LOCK IN SHARE MODE;

意向排他锁 (intention exclusive lock, IX)：事务有意向对表中的某些行加 排他锁 (X锁) -- 事务要获取某些行的 X 锁，必须先获得表的 IX 锁。 SELECT column FROM table ... FOR UPDATE;

即：意向锁是有数据引擎自己维护的，用户无法手动操作意向锁，在为数据行加共享 / 排他锁之前，InooDB 会先获取该数据行所在在数据表的对应意向锁。

意向锁要解决的问题

我们先来看一下百度百科上对意向锁存在意义的描述：

如果另一个任务试图在该表级别上应用共享或排它锁，则受到由第一个任务控制的表级别意向锁的阻塞。第二个任务在锁定该表前不必检查各个页或行锁，而只需检查表上的意向锁。

设想这样一张 users 表：MySql，InnoDB，Repeatable-Read：users(id PK，name)

事务 A 获取了某一行的排他锁，并未提交：

SELECT * FROM users WHERE id = 6 FOR UPDATE;

事务 B 想要获取 users 表的表锁：

LOCK TABLES users READ;

因为共享锁与排他锁互斥，所以事务 B 在视图对 users 表加共享锁的时候，必须保证：

当前没有其他事务持有 users 表的排他锁。

当前没有其他事务持有 users 表中任意一行的排他锁 。

为了检测是否满足第二个条件，事务 B 必须在确保 users表不存在任何排他锁的前提下，去检测表中的每一行是否存在排他锁。很明显这是一个效率很差的做法，但是有了意向锁之后，情况就不一样了：

意向锁的兼容互斥性

意向锁是怎么解决这个问题的呢？首先，我们需要知道意向锁之间的兼容互斥性：

即意向锁之间是互相兼容的，emmm......那你存在的意义是啥？

虽然意向锁和自家兄弟互相兼容，但是它会与普通的排他 / 共享锁互斥：

注意：这里的排他 / 共享锁指的都是表锁！！！意向锁不会与行级的共享 / 排他锁互斥！！！

现在我们回到刚才 users 表的例子：

事务 A 获取了某一行的排他锁，并未提交：

SELECT * FROM users WHERE id = 6 FOR UPDATE;

此时 users 表存在两把锁：users 表上的意向排他锁与 id 为 6 的数据行上的排他锁。

事务 B 想要获取 users 表的共享锁：

LOCK TABLES users READ;

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

意向锁的并发性

这就牵扯到我前面多次强调的一件事情：

意向锁不会与行级的共享 / 排他锁互斥！！！意向锁不会与行级的共享 / 排他锁互斥！！！意向锁不会与行级的共享 / 排他锁互斥！！！

重要的话要加粗说三遍，正因为如此，意向锁并不会影响到多个事务对不同数据行加排他锁时的并发性(不然我们直接用普通的表锁就行了)。

最后我们扩展一下上面 users 表的例子来概括一下意向锁的作用(一条数据从被锁定到被释放的过程中，可能存在多种不同锁，但是这里我们只着重表现意向锁)：

事务 A 先获取了某一行的排他锁，并未提交：

SELECT * FROM users WHERE id = 6 FOR UPDATE;

事务 A 获取了 users 表上的意向排他锁。

事务 A 获取了 id 为 6 的数据行上的排他锁。

之后事务 B 想要获取 users 表的共享锁：

LOCK TABLES users READ;

事务 B 检测到事务 A 持有 users 表的意向排他锁。

事务 B 对 users 表的加锁请求被阻塞(排斥)。

最后事务 C 也想获取 users 表中某一行的排他锁：

SELECT * FROM users WHERE id = 5 FOR UPDATE;

事务 C 申请 users 表的意向排他锁。

事务 C 检测到事务 A 持有 users 表的意向排他锁。

因为意向锁之间并不互斥，所以事务 C 获取到了 users 表的意向排他锁。

因为id 为 5 的数据行上不存在任何排他锁，最终事务 C 成功获取到了该数据行上的排他锁。

总结

InnoDB 支持多粒度锁，特定场景下，行级锁可以与表级锁共存。

意向锁之间互不排斥，但除了 IS 与 S 兼容外，意向锁会与 共享锁 / 排他锁 互斥。

IX，IS是表级锁，不会和行级的X，S锁发生冲突。只会和表级的X，S发生冲突。

意向锁在保证并发性的前提下，实现了行锁和表锁共存且满足事务隔离性的要求。