MySQL锁

MySQL 锁（Lock）是数据库保证并发操纵时数据一致性的焦点机制，通过控制多个事务对资源的访问顺序，克制出现脏读、数据冲突等问题。公道理解和使用锁机制，对提升数据库并发性能至关重要。
锁级别锁范例描述适用场景存储引擎支持全局锁FTWRL锁定整个数据库实例全库备份全部引擎表级锁表锁锁定整张表批量操纵全部引擎元数据锁保护表结构DDL操纵MySQL 5.5+行级锁记载锁锁定索引记载精确更新InnoDB间隙锁锁定索引间隙防止幻读InnoDB临键锁记载锁+间隙锁范围查询InnoDB意向锁IS锁意向共享锁行级S锁前InnoDBIX锁意向排他锁行级X锁前InnoDB1、全局锁

1.1 FLUSH TABLES WITH READ LOCK (FTWRL)

1. -- 加全局读锁
2. FLUSH TABLES WITH READ LOCK;
4. -- 解锁
5. UNLOCK TABLES;

复制代码

特点：

• 整个数据库处于只读状态
  
• 阻塞全部写操纵和DDL操纵
  
• 常用于全库逻辑备份
  

1.2 备份替代方案

1. -- InnoDB引擎使用--single-transaction参数
2. mysqldump --single-transaction -u root -p database > backup.sql
4. -- 其他引擎使用锁表备份
5. LOCK TABLES table1 READ, table2 READ;
6. -- 备份操作...
7. UNLOCK TABLES;

复制代码

2、表级锁

特点：

• 锁定整个表
  
• 实现简单，开销小
  
• 并发度低，轻易成为瓶颈
  
• MyISAM 存储引擎主要使用表级锁
  

2.1 显式表锁

1. -- 加读锁（表共享读锁）对指定表加了读锁，不会影响其他客户端的读，但是会阻塞其他客户端的写。
2. LOCK TABLES orders READ;
4. -- 加写锁（表独占写锁）对指定表加了写锁，会阻塞其他客户端的读和写。
5. LOCK TABLES orders WRITE;
7. -- 解锁
8. UNLOCK TABLES;

复制代码

2.2 元数据锁 (MDL)

meta data lock, 元数据锁，简写MDL。

• MDL加锁过程是系统自动控制，无需显式使用，在访问一张表的时候会自动加上。
  
• MDL锁主要作用是维护表元数据的数据一致性，在表上有活动事务的时候，不可以对元数据进行写入操纵。
  
• 为了克制DML与DDL冲突，保证读写的正确性。这里的元数据，大家可以简单理解为就是一张表的表结构。 也就是说，某一张表涉及到未提交的事务时，是不能够修改这张表的表结构的。
  

自动加锁机制：

• 访问表时自动加 MDL 读锁
  
• 修改表结构时加 MDL 写锁
  

问题场景

1. -- 会话1
2. START TRANSACTION;
3. SELECT * FROM orders; -- 加MDL读锁
5. -- 会话2
6. ALTER TABLE orders ADD COLUMN new_col INT; -- 阻塞，等待MDL写锁
8. -- 会话3
9. SELECT * FROM orders; -- 阻塞，等待MDL读锁

复制代码

3、行级锁 (InnoDB)

行级锁，每次操纵锁住对应的行数据。锁定粒度最小，发生锁冲突的概率最低，并发度最高。应用在InnoDB存储引擎中。
InnoDB的数据是基于索引组织的，行锁是通过对索引上的索引项加锁来实现的，而不是对记载加的锁。
3.1 记载锁 (Record Lock)

锁定单个行记载的锁，防止其他事务对此行进行update和delete。在RC、RR隔离级别下都支持。

• 锁定索引项，非记载自己
  
• 主键索引：直接锁定主键记载
  
• 二级索引：锁定二级索引+主键索引
  

1. -- 锁定索引记录
2. SELECT * FROM accounts WHERE id = 100 FOR UPDATE;

复制代码

• 共享锁（S）：允许一个事务去读一行，制止其他事务得到相同数据集的排它锁。
  
• 排他锁（X）：允许获取排他锁的事务更新数据，制止其他事务得到相同数据集的共享锁和排他锁。
  

兼容性

• 共享锁：共享锁兼容，排他锁冲突
  
• 排他锁：共享锁冲突，排他锁冲突
  

常见的SQL语句，在执行时，所加的行锁如下：
SQL行锁范例阐明INSERT ...排他锁自动加锁UPDATE ...排他锁自动加锁DELETE ...排他锁自动加锁SELECT (正常)不加任何 铺SELECT ... LOCK IN SH共享锁需要手动在SELECT之后加LOCK IN SHARE MODESELECT ... FOR UPDATE排他锁需要手动在SELECT之后加FOR UPDATE3.2 间隙锁 (Gap Lock)

锁定索引记载间隙（不含该记载），确保索引记载间隙稳定，防止其他事务在这个间隙进行insert，产生幻读。在RR隔离级别下都支持。

• 锁定索引记载之间的间隙
  
• 防止其他事务在范围内插入新记载
  
• 只在 REPEATABLE READ 隔离级别收效
  

1. -- 锁定范围间隙
2. SELECT * FROM accounts
3. WHERE balance BETWEEN 1000 AND 5000
4. FOR UPDATE;

复制代码

3.3 临键锁 (Next-Key Lock)

行锁和间隙锁组合，同时锁住数据，并锁住数据前面的间隙Gap。在RR隔离级别下支持。

• 记载锁 + 间隙锁组合
  
• InnoDB 默认行锁算法
  
• 解决幻读问题
  

1. -- 锁定记录和间隙
2. SELECT * FROM accounts  WHERE age > 30 FOR UPDATE;

复制代码

4、意向锁 (Intention Lock)

表级锁，用于标记 “某个事务将要对表中的行加锁”，减少表锁和行锁的冲突检查开销。

• 意向共享锁(IS): 由语句select ... lock in share mode添加。与表锁共享锁(read)兼容，与表锁排他锁(write)互斥。
  
• 意向排他锁(IX): 由insert、update、delete、select...for update添加 。与表锁共享锁(read)及排他锁(write)都互斥，意向锁之间不会互斥。
  

一旦事务提交了，意向共享锁、意向排他锁，都会自动开释。
兼容性：意向锁之间互不冲突
意向共享锁(IS)：排他锁冲突，共享锁兼容
意向排他锁(IX)：排他锁冲突，共享锁冲突
5、锁的实战应用

5.1 账户转账事务

1. START TRANSACTION;
3. -- 锁定转入账户
4. SELECT * FROM accounts WHERE id = 1 FOR UPDATE;
6. -- 锁定转出账户
7. SELECT * FROM accounts WHERE id = 2 FOR UPDATE;
9. -- 执行转账
10. UPDATE accounts SET balance = balance - 100 WHERE id = 2;
11. UPDATE accounts SET balance = balance + 100 WHERE id = 1;
13. COMMIT;

复制代码

5.2 防止超卖

1. START TRANSACTION;
3. -- 锁定商品库存
4. SELECT stock FROM products WHERE id = 100 FOR UPDATE;
6. -- 检查库存
7. IF stock > 0 THEN
8.     UPDATE products SET stock = stock - 1 WHERE id = 100;
9.     INSERT INTO orders (product_id, quantity) VALUES (100, 1);
10. END IF;
12. COMMIT;

复制代码

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。