面试题：MySQL 锁机制中，共享锁（S锁）和排他锁（X锁）有何区别？

Question

请阐述共享锁（S锁）和排他锁（X锁）在 MySQL 中的应用场景、加锁规则以及对数据并发访问的影响。

Answer 1

共享锁（S锁）

1. 应用场景：
   • 适用于多个事务需要同时读取同一数据的场景。例如，在电商系统中，多个用户同时查看商品库存数量，这种只读操作可以使用共享锁，多个事务可以同时持有共享锁，从而保证数据一致性的同时，提高并发读性能。
2. 加锁规则：
   • 当事务对数据对象加共享锁时，其他事务也可以对该对象加共享锁，但不能加排他锁。例如，事务T1对数据行r加了共享锁，事务T2也可以对r加共享锁。只有当所有持有共享锁的事务释放锁后，其他事务才能对该数据对象加排他锁。
   • 可以通过 SELECT... LOCK IN SHARE MODE 语句来对查询结果集加共享锁。例如：SELECT * FROM products WHERE product_id = 1 LOCK IN SHARE MODE;
3. 对数据并发访问的影响：
   • 提高了并发读的效率，允许多个事务同时读取数据，不会产生阻塞等待。但如果长时间持有共享锁，会导致写操作（需要排他锁）等待，可能造成写操作的饥饿现象，影响写性能。

排他锁（X锁）

1. 应用场景：
   • 用于对数据进行修改的操作场景。例如，在银行转账操作中，需要对账户余额进行修改，为了保证数据的一致性，防止其他事务同时修改该账户余额，就需要使用排他锁。
2. 加锁规则：
   • 当一个事务对数据对象加了排他锁，其他任何事务都不能再对该对象加共享锁或排他锁，直到持有排他锁的事务释放锁。例如，事务T1对数据行r加了排他锁，事务T2尝试对r加共享锁或排他锁都会被阻塞，直到T1释放锁。
   • 可以通过 SELECT... FOR UPDATE 语句来对查询结果集加排他锁，也可以在执行 INSERT、UPDATE、DELETE 操作时，MySQL会自动对涉及的数据对象加排他锁。例如：SELECT * FROM accounts WHERE account_id = 1 FOR UPDATE;
3. 对数据并发访问的影响：
   • 保证了数据修改的原子性和一致性，避免了并发写操作导致的数据冲突。但由于排他锁会阻塞其他事务的读写操作，所以会降低系统的并发性能，在高并发场景下，如果锁的粒度控制不当，可能会导致大量的锁等待和死锁问题。