mysql 共享锁和排他锁

基础概念

MySQL中的锁机制用于控制多个事务对数据的并发访问。共享锁（Shared Locks）和排他锁（Exclusive Locks）是两种主要的锁类型。

共享锁（Shared Locks）：

允许多个事务同时读取同一数据行，但阻止其他事务获取该行的排他锁。
共享锁通常用于读取操作。

排他锁（Exclusive Locks）：

只允许一个事务获取该数据行的排他锁，阻止其他事务获取共享锁或排他锁。
排他锁通常用于写入操作。

类型

共享锁（S锁）：

表示事务正在读取数据行。

排他锁（X锁）：

表示事务正在修改数据行。

应用场景

共享锁的应用场景：

当多个事务需要读取同一数据行时，使用共享锁可以提高并发性能。

排他锁的应用场景：

当事务需要修改数据行时，使用排他锁可以确保数据的一致性和完整性。

常见问题及解决方法

问题1：死锁

原因：两个或多个事务互相等待对方释放锁，导致无法继续执行。
解决方法：
- 使用事务隔离级别来减少死锁的可能性。
- 优化事务的执行顺序，尽量减少锁的持有时间。
- 使用SHOW ENGINE INNODB STATUS命令查看死锁信息，并根据信息调整事务逻辑。

问题2：锁等待超时

原因：事务等待获取锁的时间超过了设定的超时时间。
解决方法：
- 增加锁等待超时时间，通过设置innodb_lock_wait_timeout参数。
- 优化事务逻辑，减少锁的持有时间。
- 使用SELECT ... FOR UPDATE或SELECT ... LOCK IN SHARE MODE语句显式地指定锁类型。

示例代码

-- 获取共享锁
START TRANSACTION;
SELECT * FROM table_name WHERE id = 1 LOCK IN SHARE MODE;
-- 执行读取操作
COMMIT;

-- 获取排他锁
START TRANSACTION;
SELECT * FROM table_name WHERE id = 1 FOR UPDATE;
-- 执行写入操作
UPDATE table_name SET column1 = 'value' WHERE id = 1;
COMMIT;