MySQL锁

锁分类-

• 按照使用方式划分：乐观锁、悲观锁

• 按照锁级别划分：共享锁、排他锁

• 按照锁粒度划分：表级锁、行级锁、页级锁

• 按照操作划分：DML锁、DDL锁

• 按照加锁方式划分：自动锁、显示锁

乐观锁和悲观锁

乐观锁：读多写少

乐观锁定在操作数据时非常乐观，认为别人不会同时修改数据。 因此，乐观锁定不会被锁定，只需在执行更新期间判断其他人是否修改了数据即可。 如果其他人修改了数据，则放弃操作，否则执行操作。

• 乐观锁假设认为数据一般情况下不会造成冲突，所以在数据进行提交更新的时候，才会正式对数据的冲突与否进行检

• 在更新数据的时候需要比较程序中的库存量与数据库中的库存量是否相等，如果相等则进行更新。

• 反之程序重新获取库存量，再次进行比较，直到两个库存量的数值相等才进行数据更新。

乐观锁实现对商品-1操作

• 每次获取商品时，不对该商品加锁。
• 在更新数据的时候需要比较程序中的库存量与数据库中的库存量是否相等，如果相等则进行更新
• 反之程序重新获取库存量，再次进行比较，直到两个库存量的数值相等才进行数据更新。

#### 乐观锁实现加一操作代码
# 我们可以看到，只有当对数量-1操作时才会加锁，只有当程序中值和数据库中的值相等时才正真执行。
'''
//不加锁
select id,name,stock where id=1;
//业务处理
begin;
update shop set stock=stock-1 where id=1 and stock=stock;
commit;
'''

乐观锁实现方式

版本号方式

• 在表中增加一个版本号字段，每次更新时版本号加1，同时将当前版本号作为更新条件，如果版本号与更新时的版本号一致，则更新成功，否则更新失败。该方式需要在数据库中使用乐观锁来控制并发操作

时间戳方式

• 在数据表中增加一个时间戳字段，每次更新数据时将时间戳更新为当前时间戳，同时将当前时间戳作为更新条件，如果当前时间戳与更新时间戳一致，则更新成功，该方式用于需要精确控制数据更新时间的场景

序列号方式

• 在数据库中增江一个序列号字段，每次更新时将序列号加1，同时将当前序列号作为更新条件，如果当前序列号与更新时的序列号一支，则更新成功，否则更行失败

版本号方式

• 才才会正式对数据的冲突与否进行检

乐观锁实现原理

• 乐观锁的实现分为以下几步

  1. 读取数据版本号或者时间戳

     • 在更新前，先读取数据的版本号或者时间戳等标识符，作为当前数据的状态

  2. 更新数据操作

     • 执行更新操作时，需要将当前数据的状态与更新数据时的状态进行比较
     • 如果一致，说明没有被修改过，可以直接更新
     • 如果不一致，说明被其他线程修改过，需要进行回滚或者重试操作

  3. 版本号或者时间戳更新

     • 在数据更新时，需要将版本号或者时间戳更新，以标记数据被修改过

  4. 锁竞争处理

     • 在并发操作时，可能会出现多个线程同时更新一份数据的情况，此时需要进行锁竞争处理，避免数据的冲突和不一致的情况出现

悲观锁：写多读少

悲观锁定在数据操作时很悲观，认为别人会同时修改数据。 因此，操作数据时直接锁定数据，在操作完成之前不解除锁定； 上锁时其他人无法修改数据

• 悲观锁假设认为数据冲突的情况很多，所以需要在读写阶段就要加锁

• MySQL会对查询结果集中每行数据都添加排他锁，其他线程对该记录的更新与删除操作都会阻塞

• 申请前提：没有线程对该结果集中的任何行数据使用排他锁或共享锁，否则申请会阻塞。

• 适用场景：悲观锁适合写入频繁的场景。

对商品数量-1操作，悲观锁实现

• 每次获取商品时，对该商品加排他锁
• 也就是在用户A获取获取 id=1 的商品信息时对该行记录加锁，期间其他用户阻塞等待访问该记录。

#### 悲观锁实现加一操作代码
# 我们可以看到，首先通过begin开启一个事物，在获得shop信息和修改数据的整个过程中都对数据加锁，保证了数据的一致性。
'''
begin;
select id,name,stock as old_stock from shop  where id=1 for update;
update shop set stock=stock-1 where id=1 and stock=old_stock;
commit
'''

行级锁和表级锁

• 行级锁（写多读少）开销大，加锁慢，锁定粒度最小，发生锁冲突概率最低，并发度最高
• 表级锁（适合大量的读操作）开销小，加锁快，锁定粒度大、发生锁冲突最高，并发度最低
• MySQL 常用存储引擎的锁机制
  • MyISAM 和 Memory 采用表级锁（table-level locking）
  • InnoDB 支持行级锁（row-level locking）和表级锁，默认为行级锁。
  • BDB 采用页级锁（page-level locking）或表级锁，默认为页级锁

行级锁

• 行级锁是 MySQL 中锁定粒度最细的一种锁，表示只针对当前操作的行进行加锁。
• 行级锁能大大减少数据库操作的冲突，其加锁粒度最小，但加锁的开销也最大。
• 行级锁分为共享锁和排他锁。
• 特点
  • 开销大，加锁慢；
  • 会出现死锁；
  • 锁定粒度最小，发生锁冲突的概率最低，并发度也最高

表级锁

• 表级锁是 MySQL 中锁定粒度最大的一种锁，表示对当前操作的整张表加锁
• 它实现简单，资源消耗较少，被大部分 MySQL 引擎支持。
• 最常使用的 MyISAM 与 InnoDB 都支持表级锁定。
• 表级锁定分为表共享读锁（共享锁）与表独占写锁（排他锁）。
• 特点
  • 开销小，加锁快；不会出现死锁；锁定粒度大，发出锁冲突的概率最高，并发度最低。

页级锁

• 页级锁是 MySQL 中锁定粒度介于行级锁和表级锁中间的一种锁。
• 表级锁速度快，但冲突多，行级冲突少，但速度慢。
• 因此，采取了折衷的页级锁，一次锁定相邻的一组记录。
• BDB 支持页级锁。
• 特点
  • 开销和加锁时间界于表锁和行锁之间；
  • 会出现死锁；
  • 锁定粒度界于表锁和行锁之间，并发度一般。

mysql行级锁实现原理

• mysql行级锁实现原理：
  • 1、InnoDB行锁是通过给索引项加锁来实现的，这一点mysql和oracle不同；
  • 2、InnoDB这种行级锁决定，只有通过索引条件来检索数据，才能使用行级锁，否则，直接使用表级锁。
• 行级锁是mysql中粒度最细的一种锁机制，表示只对当前所操作的行进行加锁
  • 扫描过的所有索引记录，都会加锁
• 行级锁发生冲突的概率很低，其粒度最小，但是加锁的代价最大。
• 行级锁分为共享锁和排他锁。
• InnoDB这种行级锁决定，只有通过索引条件来检索数据，才能使用行级锁，否则，直接使用表级锁

加锁的方式：自动加锁

• 对于UPDATE、DELETE和INSERT语句，InnoDB会自动给涉及数据集加排他锁；
• 对于普通SELECT语句，InnoDB不会加任何锁；
• 当然我们也可以显示的加锁

共享锁：select * from tableName where … + lock in share more
排他锁：select * from tableName where … + for update

行级锁与死锁

• MyISAM 中是不会产生死锁的，因为 MyISAM 总是一次性获得所需的全部锁，要么全部满足，要么全部等待。
• 而在 InnoDB 中，锁是逐步获得的，就造成了死锁的可能。
• 在 MySQL 中，行级锁并不是直接锁记录，而是锁索引。
• 索引分为主键索引和非主键索引两种，如果一条 SQL 语句操作了主键索引，MySQL 就会锁定这条主键索引；
• 如果一条 SQL 语句操作了非主键索引，MySQL 就会先锁定该非主键索引，再锁定相关的主键索引。
• 在进行UPDATE、DELETE操作时，MySQL 不仅锁定WHERE条件扫描过的所有索引记录，而且会锁定相邻的键值，即所谓的next-key locking.
• 当两个事务同时执行，一个锁住了主键索引，在等待其他相关索引；
• 另一个锁定了非主键索引，在等待主键索引，这样就会发生死锁。
• 发生死锁后，InnoDB 一般都可以检测到，并使一个事务释放锁回退，另一个获取锁完成事务。
• 避免死锁的方法
  • 1.如果不同程序会并发存取多个表，尽量约定以相同的顺序访问表，可以大大降低发生死锁的可能性；
  • 2.在同一个事务中，尽可能做到一次锁定所需要的所有资源，减少死锁产生概率；
  • 3.对于非常容易产生死锁的业务部分，可以尝试使用升级锁定颗粒度，通过表级锁定来减少死锁产生的概率。

手动给表加锁

手动给表加读锁

• 可以并发读，但是不能并发写，读锁期间，没释放锁之前不能进行写操作。
• 使用场景：
  • 读取结果集的最新版本，同时防止其他事务产生更新该结果集
  • 主要用在需要数据依存关系时确认某行记录是否存在
  • 并确保没有人对这个记录进行UPDATE 或者 DELETE 操作

lock table user read; 

unlock tables;

img

手动给表加写锁

• 只有锁表的用户可以进行读写操作 （并发下对商品库存的操作）
• 其他用户对表不可读，不可写

lock table user write; 

unlock tables;