面试题：PostgreSQL锁机制与并发性能

Question

在高并发场景下，PostgreSQL的行级锁和表级锁对系统性能有何影响？如何优化锁争用以提升并发性能？请结合实际案例说明。

Answer 1

行级锁对系统性能的影响

• 优点：
  • 并发度高：在高并发场景下，行级锁只锁定特定的行数据。例如在一个电商订单表中，多个用户同时下单不同商品，每个订单对应表中的不同行。此时行级锁允许这些并发操作各自锁定自己对应的行，从而避免相互干扰，大大提高了并发处理能力。
  • 资源消耗相对低：由于锁定范围小，数据库系统为管理锁所消耗的内存等资源相对较少，减少了额外开销。
• 缺点：
  • 锁管理开销：虽然资源消耗相对低，但由于锁数量可能较多，管理众多行级锁本身需要一定的开销。例如在频繁更新的大表中，行级锁的频繁申请、释放操作会增加系统的CPU负载。

表级锁对系统性能的影响

• 优点：
  • 锁管理简单：在一些简单场景或特定操作下，表级锁的管理相对简单。例如执行全表扫描操作时，对整个表加锁可以减少锁的申请频率，避免频繁的锁操作带来的开销。
  • 适合批量操作：当需要对表中的大量数据进行统一操作（如批量插入、删除）时，表级锁可以保证操作的一致性，减少锁争用的复杂性。
• 缺点：
  • 并发度低：表级锁会锁定整个表，在锁持有期间，其他事务无法对该表进行读写操作。例如在一个论坛帖子表中，如果对表加锁进行维护操作，那么其他用户在这段时间内既不能发表新帖子（写操作），也不能查看帖子（读操作），严重降低了系统的并发性能。
  • 资源浪费：即使只需要操作表中的少数几行数据，表级锁也会锁定整个表，导致其他不必要的数据也被锁定，造成资源浪费。

优化锁争用以提升并发性能的方法

1. 合理设计事务：
   • 缩短事务长度：尽量减少事务持有锁的时间。例如在电商订单处理中，将订单创建、库存扣减等操作放在一个事务内，但要尽快完成这些操作并提交事务。如果事务中包含一些不必要的长时间运行的查询或计算，应将其移出事务。
   • 按照相同顺序访问资源：多个事务如果需要访问多个表或行，应按照相同的顺序进行操作，避免死锁。比如在一个涉及订单表和用户表的业务中，所有事务都先访问订单表，再访问用户表。
2. 选择合适的锁粒度：
   • 根据业务场景：对于并发读写操作较多且数据操作较为分散的场景，优先使用行级锁。如社交平台的用户动态表，不同用户发布动态相互之间关联性不大，适合行级锁。而对于一些批量操作且对并发度要求不高的场景，如每月对会员信息进行统一更新，可以考虑使用表级锁。
3. 优化查询语句：
   • 避免全表扫描：通过添加合适的索引，使查询能够快速定位到所需数据，减少锁的范围。例如在员工信息表中，经常根据员工ID查询，为员工ID字段添加索引后，查询时就可以直接定位到特定行，而不是全表扫描加表级锁。
4. 使用MVCC（多版本并发控制）：
   • 原理：PostgreSQL的MVCC机制允许事务在读取数据时不需要获取锁，而是读取数据的一个历史版本。这大大提高了读操作的并发性能，同时写操作也不会阻塞读操作。例如在一个新闻网站的文章展示系统中，大量用户同时读取文章，MVCC可以保证这些读操作不会因为写操作（如编辑文章）而被阻塞。

实际案例

假设我们有一个在线票务系统，其中有一个ticket表记录了各个场次的票务信息，包括座位号、价格、是否已售出等字段。

1. 未优化前：
   • 问题：在演出开始前的购票高峰期，大量用户同时购票，由于系统默认使用表级锁，导致并发性能极低。一个用户购票操作（对某一行数据进行更新，标记座位已售出）会锁定整个ticket表，其他用户只能等待，造成大量用户等待超时。
2. 优化措施：
   • 使用行级锁：通过调整代码，对购票操作使用行级锁。在执行购票SQL语句时，使用SELECT... FOR UPDATE语句锁定特定的座位行。例如：

BEGIN;
SELECT * FROM ticket WHERE seat_number = 'A1' AND is_sold = false FOR UPDATE;
UPDATE ticket SET is_sold = true WHERE seat_number = 'A1';
COMMIT;

- **优化查询**：为`seat_number`和`is_sold`字段添加联合索引，加快查询速度，减少锁的等待时间。
- **缩短事务长度**：将原来购票事务中一些不必要的操作（如查询用户积分信息，且该操作不影响购票核心逻辑）移出事务，缩短事务持有锁的时间。

3. 优化后： - 效果：经过优化，系统的并发性能大幅提升，在购票高峰期能够处理更多的并发请求，用户等待超时的情况明显减少。