mysql数据库怎么锁一行数据_MYSQL数据库锁知识盘点

InnoDB支持表、行(默认)级锁，而MyISAM支持表级锁，InnoDB还支持事物等特性.现在大部分的互联网应用都是使用InnoDB类型

数据库锁定机制简单来说，就是数据库为了保证数据的一致性，而使各种共享资源在被并发访问变得有序所设计的一种规则。

锁定机制

MySQL各存储引擎使用了三种类型(级别)的锁定机制表级锁定(table-level)

开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低

行级锁定(row-level)

开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高

页级锁定(page-level)

开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。

MyISAM采用表级锁

InnoDB支持行级锁和表级锁，默认为行级锁

BDB引擎的页级锁不在本文章讨论范围内，不常见，如果真的面试官问的话那就太教条主义了。

锁的类别

从锁的类别上来讲，有共享锁和排他锁

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

SELECT ... LOCK IN SHARE MODE;

排他锁: eXclusive Lock 又叫做写锁。当用户要进行数据的写入时，对数据加上排他锁。排他锁只可以加一个，他和其他的排他锁，共享锁都相斥。

SELECT ... FOR UPDATE;

在查询语句后面增加FOR UPDATE，Mysql会对查询结果中的每行都加排他锁，当没有其他线程对查询结果集中的任何一行使用排他锁时，可以成功申请排他锁，否则会被阻塞。

共享锁和排他锁是mysql系统实现的功能，下面的乐观锁和悲观锁是我们在利用系统的锁实现一种用户级别的锁机制。

乐观锁：Optimistic Lock

假设认为数据一般情况下不会造成冲突，所以在数据进行提交更新的时候，才会正式对数据的冲突与否进行检测，如果发现冲突了，则让返回用户错误的信息，让用户决定如何去做。

实现数据版本有两种方式，第一种是使用版本号，第二种是使用时间戳。

版本号的实现方式如下

update order

set status=2,version=version+1

where id=#{id} and version=#{version};乐观并发控制相信事务之间的数据竞争并发的概率是比较小的，因此尽可能做下去，直到提交的时候才去锁定，所以不会产生任何锁和死锁。例如两个事务同一时间并发都读取了数据库的某一行，经过修改以后写回数据库就会出现问题。

悲观锁：Pessimistic Lock

在整个数据处理过程中，将数据处于锁定状态。悲观锁的实现，往往依靠数据库提供的锁机制 (也只有数据库层提供的锁机制才能真正保证数据访问的排他性，否则，即使在本系统中实现了加锁机制，也无法保证外部系统不会修改数据)

如何使用悲观锁：

//0.开始事务

begin;

//1.查询出商品信息

select status from goods where id=1 for update;

//2.根据商品信息生成订单

insert into orders (id,goods_id) values (null,1);

//3.修改商品status为2

update goods set status=2;

//4.提交事务

commit;

可以见到悲观锁的概念就是不管什么情况先加上个排他锁先。然后万事大吉安逸的处理业务等业务提交后释放排他锁。这期间其他任何争用锁都会报错。

表级锁定MyISAM存储引擎使用的锁定机制完全是由MySQL提供的表级锁定实现

MySQL的表级锁有两种模式：表共享读锁(Table Read Lock)和表独占写锁(Table Write Lock)。

锁模式的兼容性：对MyISAM表的读操作，不会阻塞其他用户对同一表的读请求，但会阻塞对同一表的写请求；

对MyISAM表的写操作，则会阻塞其他用户对同一表的读和写操作；

MyISAM表的读操作与写操作之间，以及写操作之间是串行的。当一个线程获得对一个表的写锁后，只有持有锁的线程可以对表进行更新操作。其他线程的读、写操作都会等待，直到锁被释放为止。

行级锁定行级锁定不是MySQL自己实现的锁定方式，而是由其他存储引擎自己所实现的，如广为大家所知的InnoDB存储引擎

InnoDB的行级锁定同样分为两种类型，共享锁和排他锁，而在锁定机制的实现过程中为了让行级锁定和表级锁定共存，InnoDB也同样使用了意向锁(表级锁定)的概念，也就有了意向共享锁和意向排他锁这两种。

InnoDB行锁实现方式是InnoDB行锁是通过给索引上的索引项加锁来实现的，只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁

以下情况也是面试过程中大概率会被问到的场景在不通过索引条件查询的时候，InnoDB确实使用的是表锁，而不是行锁。

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

当表有多个索引的时候，不同的事务可以使用不同的索引锁定不同的行，另外，不论是使用主键索引、唯一索引或普通索引，InnoDB都会使用行锁来对数据加锁。

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

InnoDB存储引擎的锁的算法

有三种

- Record lock：单个行记录上的锁

- Gap lock：间隙锁，锁定一个范围，不包括记录本身

- Next-key lock：record+gap 锁定一个范围，包含记录本身innodb对于行的查询使用next-key lock

Next-locking keying为了解决Phantom Problem幻读问题

当查询的索引含有唯一属性时，将next-key lock降级为record key

Gap锁设计的目的是为了阻止多个事务将记录插入到同一范围内，而这会导致幻读问题的产生

有两种方式显式关闭gap锁：(除了外键约束和唯一性检查外，其余情况仅使用record lock) A. 将事务隔离级别设置为RC B. 将参数innodb_locks_unsafe_for_binlog设置为1