MySQL乐观锁、共享锁、排他锁、行锁、表锁区别和使用方法

数据库锁分类

一、乐观锁和悲观锁

1.乐观锁介绍

乐观锁（ Optimistic Locking ） 相对悲观锁而言，乐观锁假设认为数据一般情况下不会造成冲突，所以在数据进行提交更新的时候，才会正式对数据的冲突与否进行检测，如果发现冲突了，则让返回用户错误的信息，让用户决定如何去做。那么我们如何实现乐观锁呢，一般来说有以下2种方式：

2.使用方法

版本号控制

版本号的实现方式有两种，一个是数据版本机制，一个是时间戳机制。具体如下。

a.使用数据版本（Version）记录机制实现，这是乐观锁最常用的一种实现方式。何谓数据版本？即为数据增加一个版本标识，一般是通过为数据库表增加一个数字类型的 “version” 字段来实现。当读取数据时，将version字段的值一同读出，数据每更新一次，对此version值加一。当我们提交更新的时候，判断数据库表对应记录的当前版本信息与第一次取出来的version值进行比对，如果数据库表当前版本号与第一次取出来的version值相等，则予以更新，否则认为是过期数据。

3.代码实现

数据库表新增字段version，更新修改操作时，需要比较当前版本值是否相等，相等的话，每次修改值时，将版本号version+1，同一版本的数据只能有一次提交成功，其他提交时会因为version不一致导致修改失败

二、属性锁

MySQL常用引擎有MyISAM和InnoDB，而InnoDB是mysql默认的引擎。MyISAM不支持事务，InnoDB支持事务。MyISAM不支持行锁，而InnoDB支持行锁和表锁。MyISAM在执行查询语句（SELECT）前，会自动给涉及的所有表加读锁，在执行更新操作（UPDATE、DELETE、INSERT等）前，会自动给涉及的表加写锁，这个过程并不需要用户干预，因此用户一般不需要直接用LOCK TABLE命令给MyISAM表显式加锁

1.共享锁(读锁)

共享 (S) 用于不更改或不更新数据的操作（只读操作），多个事务对同一数据共享一把锁。 如 SELECT 语句。如果事务T对数据A加上共享锁后，则其他事务只能对A再加共享锁，不能加排他锁。获准共享锁的事务只能读数据，不能修改数据。

显示加锁：

select * from t_activity where id=1 lock in share mode；

2.排他锁（写锁）

用于数据修改操作，例如 INSERT、UPDATE 或 DELETE。确保不会同时同一资源进行多重更新。如果事务T对数据A加上排他锁后，则其他事务不能再对A加任何类型的锁，包括共享锁和排它锁。获准排他锁的事务既能读数据，又能修改数据。类似于资源锁，只允许一个请求进来。

select * from t_activity where id=1  for update；

如下所示：innodb引擎对select开启事务，id为1的数据使用排它锁，事务没有结束时，其他事务不能获取到排它锁，提交之后就可以了

(1)可视化界面事务中使用排它锁，命令行事物对同一行数据也获取排它锁

(2)事务中使用排它锁，其他事物对同一行数据获取共享锁

(3)事务中使用排它锁，提交之后，其他事物对同一行数据也获取排它锁

mysql InnoDB引擎默认的修改数据语句，update,delete,insert都会自动给涉及到的数据加上排他锁，select语句默认不会加任何锁类型

三、范围锁

行锁和表锁其实是排它锁的两种实现，主要用于update,delete,insert这些语句，区别在于是对是否命中索引

1.行锁

行锁就是锁一行或者多行记录，mysql的行锁是基于索引加载的，所以行锁是要加在索引响应的行上，即命中索引

这里的表t_activity中id是主键索引，在事务中没有结束的情况下，命中索引，进行修改操作，其他事务对同一张表中的相同数据不能进行修改，必须等待事务提交完毕，释放了行锁，才能执行。

t_activity表数据如下所示，id为主键索引，credit未添加索引

可视化界面中开启事务，修改表t_activity 的id为6的credit为10，命令行中一事物修改id为6的credit为8，因为是同一行数据，事务未提交，行锁没有释放，无法进行修改 。

可视化界面中开启事务，修改表t_activity中id=1的credit字段，不关闭事务，命令行一事物修改id=2的credit为8，成功，两者互不影响，因为行锁只锁定了id=1的这一行，id为2的这一行没有锁住，可以正常执行

2.表锁

表锁就是一锁锁一整张表，在表被锁定期间，其他事务不能对该表进行操作，必须等当前表的锁被释放后才能进行操作。表锁响应的是非索引字段，即全表扫描，全表扫描时锁定整张表，sql语句可以通过执行计划看出扫描了多少条记录。

这里在可视化界面事务中对t_activity表中非索引字段进行修改，命令行事务中任意修改表数据，会导致修改失败，因为锁定了整张表

事务提交完毕后，释放了表锁，其他事物就可以修改表中任意数据了