mysql锁

mysql中的锁

两种思想：悲观锁 VS 乐观锁

至于悲观锁和乐观锁，也并不是 MySQL 或者数据库中独有的概念，而是并发编程的基本概念。主要区别在于，操作共享数据时：

“悲观锁”即认为数据出现冲突的可能性更大,每次操作时都会对数据进行锁定；

而“乐观锁”则是认为大部分情况不会出现冲突，不用每次都对数据上锁。

乐观锁的版本号机制

在表中设计一个版本字段 version，第一次读的时候，会获取 version 字段的取值。然后对数据进行更新或删除操作时，会执行UPDATE … SET version=version+1 WHERE version=version。此时如果已经有事务对这条数据进行了更改，修改就不会成功。

这种方式类似我们熟悉的 SVN、CVS 版本管理系统，当我们修改了代码进行提交时，首先会检查当前版本号与服务器上的版本号是否一致，如果一致就可以直接提交，如果不一致就需要更新服务器上的最新代码，然后再进行提交。

乐观锁的时间戳机制

时间戳和版本号机制一样，也是在更新提交的时候，将当前数据的时间戳和更新之前取得的时间戳进行比较，如果两者一致则更新成功，否则就是版本冲突。

小结

• 乐观锁使用的是CAS机制，并不会对数据加锁，而是通过对比数据的时间戳或者版本号来决定是否进行数据操作
• 悲观锁对数据被其他事务的修改持保守态度，会通过数据库自身的锁机制来实现，从而保证数据操作的排它性。

锁粒度划分

锁用来对数据进行锁定，我们可以从锁定对象的粒度大小来对锁进行划分，分别为行锁、页锁和表锁。

• 行锁：按照行的粒度对数据进行锁定。锁定粒度度小，发生锁冲突概率较低，并发度高，但是对于锁的获取和释放更加频繁，开销比较大，加锁会比较慢，容易出现死锁情况。

• 页锁：对数据页进行锁定，锁定的数据资源比行锁要多，因为一个页中可以有多个行记录。页锁的开销介于表锁和行锁之间，会出现死锁。锁定粒度介于表锁和行锁之间，并发度一般。

• 表锁: 对数据表进行锁定，锁定粒度很大，同时发生锁冲突的概率也会较高，数据访问的并发度低。不过好处在于对锁的使用开销小，加锁会很快。

不同的数据库和存储引擎支持的锁粒度不同，InnoDB 和 Oracle 支持行锁和表锁。而 MyISAM 只支持表锁，MySQL 中的 BDB 存储引擎支持页锁和表锁。SQL Server 可以同时支持行锁、页锁和表锁，如下表所示：

这里需要说明下，每个层级的锁数量是有限制的，因为锁会占用内存空间，锁空间的大小是有限的。当某个层级的锁数量超过了这个层级的阈值时，就会进行锁升级。锁升级就是用更大粒度的锁替代多个更小粒度的锁，比如 InnoDB 中行锁升级为表锁，这样做的好处是占用的锁空间降低了，但同时数据的并发度也下降了。

行锁实现

InnoDB行锁是通过给索引上的索引项加锁来实现的，这一点MySQL与Oracle不同，后者是通过在数据块中对相应数据行加锁来实现的。

• InnoDB这种行锁实现特点意味着：只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁！在实际应用中，要特别注意InnoDB行锁的这一特性，不然的话，可能导致大量的锁冲突，从而影响并发性能。

• 由于MySQL的行锁是针对索引加的锁，不是针对记录加的锁，所以虽然是访问不同行的记录，但是如果是使用相同的索引键，是会出现锁冲突的

• 当表有多个索引的时候，不同的事务可以使用不同的索引锁定不同的行，另外，不论是使用主键索引、唯一索引或普通索引，InnoDB都会使用行锁来对数据加锁。如果不同的索引碰巧都落到了同一个行上，那么同样会阻塞。

• 即便在条件中使用了索引字段，但是否使用索引来检索数据是由MySQL通过判断不同执行计划的代价来决定的，如果MySQL认为全表扫描效率更高，比如对一些很小的表，它就不会使用索引，这种情况下InnoDB将使用表锁，而不是行锁。因此，在分析锁冲突时，别忘了检查SQL的执行计划，以确认是否真正使用了索引。

行级锁与死锁

MyISAM中是不会产生死锁的，因为MyISAM总是一次性获得所需的全部锁，要么全部满足，要么全部等待。而在InnoDB中，锁是逐步获得的，就造成了死锁的可能。

在MySQL中，行级锁并不是直接锁记录，而是锁索引。索引分为主键索引和非主键索引两种，如果一条sql语句操作了主键索引，MySQL就会锁定这条主键索引；如果一条语句操作了非主键索引，MySQL会先锁定该非主键索引，再锁定相关的主键索引。 REPEATABLE READ隔离级别下，在UPDATE、DELETE操作时，MySQL不仅锁定WHERE条件扫描过的所有索引记录，而且会锁定相邻的键值，即所谓的next-key locking。

当两个事务同时执行，一个锁住了主键索引，在等待其他相关索引。另一个锁定了非主键索引，在等待主键索引。这样就会发生死锁。

发生死锁后，InnoDB一般都可以检测到，并使一个事务释放锁回退，另一个获取锁完成事务。

避免死锁的方式

1、如果不同程序会并发读写多个表，尽量约定以相同的顺序访问表，可以大大降低发生死锁的概率。

2、在同一个事务中，尽可能做到一次锁定所需要的所有资源，减少死锁产生概率；

3、对于非常容易产生死锁的业务部分，可以尝试使用升级锁定颗粒度，通过表级锁定来减少死锁产生的概率；

锁定模式

共享锁(Share Lock)

共享锁 又称 读锁，是读取操作创建的锁。其他用户线程可以并发读取锁定的数据，但任何事务都不能对数据进行修改（获取数据上的排他锁），直到数据上所有共享锁已释放。

如果事务T对数据A加上共享锁后，则其他事务只能对A再加共享锁，不能加排他锁。获区共享锁的事务只能读数据，不能修改数据。

用法

SELECT ... LOCK IN SHARE MODE;

在查询语句后面增加LOCK IN SHARE MODE，Mysql会对查询结果中的每行都加共享锁，当其他线程对查询结果集中的任何一行使用排他锁时，都会被阻塞，直到所有数据行上的共享锁被释放。

排他锁（eXclusive Lock）

排他锁 又称 写锁，如果事务T对数据A加上排他锁后，则其他事务不能再对数据A加任任何类型的锁。获取排他锁的事务既能读数据，又能修改数据。

用法

SELECT ... FOR UPDATE;

在查询语句后面增加FOR UPDATE，Mysql会对查询结果中的每行都加排他锁，当其他线程对查询结果集中的任何一行使用排他锁时，都会被阻塞，直到所有数据行上的排他锁被释放。

所以我们说S锁和S锁是兼容的，S锁和X锁是不兼容的，X锁和X锁也是不兼容的，画个表表示一下就是这样：

兼容性	S	X
S	兼容	冲突
X	冲突	冲突

如果事务T1首先获取了一条记录的X锁之后，那么不管事务T2接着想获取该记录的S锁还是X锁都会被阻塞，直到事务T1提交，释放排他锁。

小结

对于insert、update、delete，InnoDB会自动给涉及的数据加排他锁（X），防止其他事务对该数据行进行更改操作；

对于一般的Select语句，InnoDB不会加任何锁，事务可以通过以下语句给显示加共享锁或排他锁。

• 共享锁：SELECT ... LOCK IN SHARE MODE;

• 排他锁：SELECT ... FOR UPDATE;

事务T对数据集A加上排他锁后，其他事务对数据A的任何一行加锁时，都会被阻塞，直到事务T提交释放排他锁。

两阶段锁协议,排他锁（X）会在执行 commit 语句的时候释放。

参考文章：

极客时间