mysql常见锁实验 锁测试 mysql测试 - 01 mysql锁 dba

在MySQL中，最常见的锁类型包括表级锁（Table Lock）和行级锁（Row Lock）。其中，行级锁在使用InnoDB存储引擎时最为常见，因为InnoDB支持事务（Transaction）和外键，并且大多数情况下，行级锁能够提供更高的并发性能。

常见的行级锁：
行级锁通常在执行诸如SELECT … FOR UPDATE、UPDATE、DELETE等操作时自动加锁，以确保数据的一致性和完整性。

实际案例：
下面是一个具体的案例，展示了如何在MySQL中使用InnoDB存储引擎，并通过模拟一个并发更新的场景来复现行级锁的行为。

创建实验所需表：
sql
CREATE DATABASE IF NOT EXISTS lock_demo;
USE lock_demo;

CREATE TABLE accounts (
id INT AUTO_INCREMENT PRIMARY KEY,
account_name VARCHAR(50) NOT NULL,
balance DECIMAL(10, 2) NOT NULL
) ENGINE=InnoDB;
插入实验数据：
sql
INSERT INTO accounts (account_name, balance) VALUES (‘Alice’, 1000.00);
INSERT INTO accounts (account_name, balance) VALUES (‘Bob’, 1000.00);
复现行级锁的SQL语句脚本：
在这个例子中，我们将使用两个会话（Session）来模拟并发更新操作，并观察行级锁的行为。

启动第一个会话（Session 1）：
sql
– 在第一个会话中，启动一个事务并锁定Alice的账户
START TRANSACTION;
SELECT * FROM accounts WHERE account_name = ‘Alice’ FOR UPDATE;
此时，第一个会话已经持有了Alice账户的行级锁。

启动第二个会话（Session 2）：
sql
– 在第二个会话中，尝试更新Alice的账户
UPDATE accounts SET balance = balance - 100 WHERE account_name = ‘Alice’;
你会发现，第二个会话在这里会被阻塞，因为它在尝试获取Alice账户的行级锁时，发现该锁已经被第一个会话持有。

回到第一个会话（Session 1）并提交事务：
sql
– 在第一个会话中，提交事务
COMMIT;
提交事务后，第一个会话释放了Alice账户的行级锁。

观察第二个会话（Session 2）：
此时，第二个会话会自动继续执行，并且更新操作成功完成。
sql
– 在第二个会话中，再次查看Alice的账户余额
SELECT * FROM accounts WHERE account_name = ‘Alice’;
你应该会看到Alice的账户余额已经被减少了100。

总结：
在这个案例中，通过SELECT … FOR UPDATE语句，第一个会话对Alice的账户行加上了行级锁。第二个会话在尝试更新同一个行时，必须等待第一个会话释放锁，这就是行级锁的作用。行级锁可以确保数据的一致性和完整性，同时提高了并发性能。

你可以通过MySQL的SHOW PROCESSLIST命令来观察这些会话的状态，进一步理解锁的行为。