MySQL锁
MySQL 锁（Lock）是数据库保证并发操作时数据一致性的核心机制，通过控制多个事务对资源的访问顺序，避免出现脏读、数据冲突等问题。合理理解和使用锁机制，对提升数据库并发性能至关重要。锁级别锁类型描述适用场景存储引擎支持全局锁FTWRL锁定整个数据库实例全库备份所有引擎表级锁表锁锁定整张表批量操作所有引擎元数据锁保护表结构DDL操作MySQL 5.5+行级锁记录锁锁定索引记录精确更新InnoDB间隙锁锁定索引间隙防止幻读InnoDB临键锁记录锁+间隙锁范围查询InnoDB意向锁IS锁意向共享锁行级S锁前InnoDBIX锁意向排他锁行级X锁前InnoDB1、全局锁
1.1 FLUSH TABLES WITH READ LOCK (FTWRL)
-- 加全局读锁
FLUSH TABLES WITH READ LOCK;
-- 解锁
UNLOCK TABLES;特点：
[*]整个数据库处于只读状态
[*]阻塞所有写操作和DDL操作
[*]常用于全库逻辑备份
1.2 备份替代方案
-- InnoDB引擎使用--single-transaction参数
mysqldump --single-transaction -u root -p database > backup.sql
-- 其他引擎使用锁表备份
LOCK TABLES table1 READ, table2 READ;
-- 备份操作...
UNLOCK TABLES;2、表级锁
特点：
[*]锁定整个表
[*]实现简单，开销小
[*]并发度低，容易成为瓶颈
[*]MyISAM 存储引擎主要使用表级锁
2.1 显式表锁
-- 加读锁（表共享读锁）对指定表加了读锁，不会影响其他客户端的读，但是会阻塞其他客户端的写。
LOCK TABLES orders READ;
-- 加写锁（表独占写锁）对指定表加了写锁，会阻塞其他客户端的读和写。
LOCK TABLES orders WRITE;
-- 解锁
UNLOCK TABLES;2.2 元数据锁 (MDL)
meta data lock, 元数据锁，简写MDL。
[*]MDL加锁过程是系统自动控制，无需显式使用，在访问一张表的时候会自动加上。
[*]MDL锁主要作用是维护表元数据的数据一致性，在表上有活动事务的时候，不可以对元数据进行写入操作。
[*]为了避免DML与DDL冲突，保证读写的正确性。这里的元数据，大家可以简单理解为就是一张表的表结构。 也就是说，某一张表涉及到未提交的事务时，是不能够修改这张表的表结构的。
自动加锁机制：
[*]访问表时自动加 MDL 读锁
[*]修改表结构时加 MDL 写锁
问题场景
-- 会话1
START TRANSACTION;
SELECT * FROM orders; -- 加MDL读锁
-- 会话2
ALTER TABLE orders ADD COLUMN new_col INT; -- 阻塞，等待MDL写锁
-- 会话3
SELECT * FROM orders; -- 阻塞，等待MDL读锁3、行级锁 (InnoDB)
行级锁，每次操作锁住对应的行数据。锁定粒度最小，发生锁冲突的概率最低，并发度最高。应用在InnoDB存储引擎中。
InnoDB的数据是基于索引组织的，行锁是通过对索引上的索引项加锁来实现的，而不是对记录加的锁。
3.1 记录锁 (Record Lock)
锁定单个行记录的锁，防止其他事务对此行进行update和delete。在RC、RR隔离级别下都支持。
[*]锁定索引项，非记录本身
[*]主键索引：直接锁定主键记录
[*]二级索引：锁定二级索引+主键索引
-- 锁定索引记录
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 隔离级别生效
-- 锁定范围间隙
SELECT * FROM accounts
WHERE balance BETWEEN 1000 AND 5000
FOR UPDATE;3.3 临键锁 (Next-Key Lock)
行锁和间隙锁组合，同时锁住数据，并锁住数据前面的间隙Gap。在RR隔离级别下支持。
[*]记录锁 + 间隙锁组合
[*]InnoDB 默认行锁算法
[*]解决幻读问题
-- 锁定记录和间隙
SELECT * FROM accountsWHERE 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 账户转账事务
START TRANSACTION;
-- 锁定转入账户
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;
-- 锁定转出账户
SELECT * FROM accounts WHERE id = 2 FOR UPDATE;
-- 执行转账
UPDATE accounts SET balance = balance - 100 WHERE id = 2;
UPDATE accounts SET balance = balance + 100 WHERE id = 1;
COMMIT;5.2 防止超卖
START TRANSACTION;
-- 锁定商品库存
SELECT stock FROM products WHERE id = 100 FOR UPDATE;
-- 检查库存
IF stock > 0 THEN
UPDATE products SET stock = stock - 1 WHERE id = 100;
INSERT INTO orders (product_id, quantity) VALUES (100, 1);
END IF;
COMMIT;
来源：程序园用户自行投稿发布，如果侵权，请联系站长删除
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！
页:
[1]