PostgreSQL锁机制与Java应用实践

1. PostgreSQL锁机制概述在现代数据库系统中并发控制是确保数据一致性和系统性能的核心机制。作为一名长期使用PostgreSQL的开发者我深刻理解锁机制在数据库系统中的重要性。PostgreSQL作为一款功能强大的开源关系型数据库提供了丰富而精细的锁机制来协调多个事务对共享资源的访问。1.1 数据库锁的基本概念在多用户并发访问数据库的环境中多个事务可能同时读取或修改同一数据行。如果没有适当的协调机制就可能导致脏读Dirty Read、不可重复读Non-Repeatable Read或幻读Phantom Read等一致性问题。数据库锁就是为了解决这些问题而设计的。锁本质上是一种同步原语用于控制对数据库对象如表、行、页等的并发访问。通过加锁数据库可以确保事务以一种有序、安全的方式执行从而维护ACID原子性、一致性、隔离性、持久性特性。在PostgreSQL中锁机制分为多个层次表级锁Table-level locks行级锁Row-level locks页级锁Page-level locks内部使用用户通常不直接接触数据库级锁Database-level locks事务级锁Transaction-level locks1.2 PostgreSQL锁的分类PostgreSQL中的锁可以分为两大类共享锁和排他锁。共享锁允许多个事务同时读取同一资源而排他锁则用于写操作确保同一时间只有一个事务可以修改数据。在实际应用中我们还会遇到意向锁Intention Locks这是一种特殊的表级锁用于表示事务打算在表的某些行上获取特定类型的行级锁。意向锁的主要作用是提高锁管理的效率和避免死锁。2. 共享锁Shared Locks深度解析2.1 共享锁的基本特性共享锁也称为读锁Read Lock允许多个事务同时读取同一资源但阻止任何事务对该资源进行写操作。这是实现读-读并发的关键机制。在PostgreSQL中共享锁具有以下特性多个事务可以同时持有同一资源的共享锁持有共享锁的资源不能被其他事务获取排他锁Exclusive Lock共享锁通常在SELECT语句中自动获取取决于隔离级别可以通过SELECT ... FOR SHARE显式请求共享锁2.2 共享锁的使用场景共享锁适用于以下几种典型场景只读查询当多个用户需要同时查看相同的数据时引用完整性检查在外键约束检查时PostgreSQL会自动获取父表的共享锁显式并发控制应用程序需要确保在读取数据期间数据不会被其他事务修改2.3 PostgreSQL中的共享锁类型PostgreSQL提供了多种共享锁类型主要包括锁类型SQL命令兼容性用途ACCESS SHARESELECT高普通SELECT查询ROW SHARESELECT FOR UPDATE/SHARE中行级锁定的表级意向锁SHARELOCK TABLE ... IN SHARE MODE低整表共享锁SHARE ROW EXCLUSIVECREATE INDEX CONCURRENTLY最低创建索引时使用2.3.1 普通SELECT与ACCESS SHARE锁-- 会话1 BEGIN; SELECT * FROM products WHERE id 1; -- 此时在products表上持有ACCESS SHARE锁 -- 会话2 BEGIN; SELECT * FROM products WHERE id 2; -- 这个查询可以立即执行因为ACCESS SHARE锁相互兼容2.3.2 显式共享锁示例-- 会话1 BEGIN; SELECT * FROM products WHERE id 1 FOR SHARE; -- 在products表上持有ROW SHARE锁在特定行上持有行级共享锁 -- 会话2 BEGIN; UPDATE products SET price 100 WHERE id 1; -- 这个UPDATE会被阻塞直到会话1提交或回滚3. 意向锁Intention Locks深度解析3.1 意向锁的基本概念意向锁是一种表级锁用于表示事务打算在表的某些行上获取特定类型的行级锁。意向锁本身并不锁定任何数据它只是表明我打算在表的某些行上加锁。在PostgreSQL中意向锁的概念主要体现在以下两种表级锁中ROW SHARE表示事务打算在表的某些行上获取共享锁或排他锁ROW EXCLUSIVE表示事务打算在表的某些行上获取排他锁3.2 为什么需要意向锁想象一下如果没有意向锁当一个事务想要对整个表加排他锁比如DROP TABLE时数据库需要检查表中的每一行是否被其他事务锁定。这在大表中会非常低效。有了意向锁后数据库只需要检查表级意向锁即可快速判断是否可以安全地对整个表加锁。这种机制大大提高了锁管理的效率。3.3 意向锁的兼容性矩阵理解意向锁的关键在于掌握其兼容性规则。以下是PostgreSQL中主要锁模式的兼容性矩阵请求的锁模式 \ 已持有的锁模式ACCESS SHAREROW SHAREROW EXCLUSIVESHARESHARE ROW EXCLUSIVEEXCLUSIVEACCESS EXCLUSIVEACCESS SHARE兼容兼容兼容兼容兼容兼容不兼容ROW SHARE兼容兼容兼容兼容兼容不兼容不兼容ROW EXCLUSIVE兼容兼容兼容不兼容不兼容不兼容不兼容SHARE兼容兼容不兼容兼容不兼容不兼容不兼容SHARE ROW EXCLUSIVE兼容兼容不兼容不兼容不兼容不兼容不兼容EXCLUSIVE兼容不兼容不兼容不兼容不兼容不兼容不兼容ACCESS EXCLUSIVE不兼容不兼容不兼容不兼容不兼容不兼容不兼容从兼容性矩阵可以看出ACCESS SHARE和ROW SHARE具有最高的并发性ACCESS EXCLUSIVE与所有其他锁模式都不兼容用于最严格的独占操作4. Java应用中的锁机制实践在Java应用程序中正确使用PostgreSQL的锁机制对于构建高性能、高可靠性的系统至关重要。下面我将通过具体代码示例展示如何在不同场景下使用共享锁和意向锁。4.1 基础环境设置首先我们需要设置基本的数据库连接和实体类// Product.java public class Product { private Long id; private String name; private BigDecimal price; private Integer stock; // 构造函数、getter、setter省略 } // DatabaseConfig.java import javax.sql.DataSource; import com.zaxxer.hikari.HikariConfig; import com.zaxxer.hikari.HikariDataSource; public class DatabaseConfig { public static DataSource createDataSource() { HikariConfig config new HikariConfig(); config.setJdbcUrl(jdbc:postgresql://localhost:5432/mydb); config.setUsername(postgres); config.setPassword(password); config.setMaximumPoolSize(20); return new HikariDataSource(config); } }4.2 安全的库存查询共享锁假设我们有一个电商应用需要在下单前检查商品库存。为了确保在检查库存到实际扣减库存之间库存数量不会被其他事务修改我们可以使用共享锁。import java.sql.*; import java.math.BigDecimal; public class InventoryService { private final DataSource dataSource; public InventoryService(DataSource dataSource) { this.dataSource dataSource; } /** * 安全地查询商品库存使用共享锁防止并发修改 */ public Product getInventoryWithLock(Long productId) throws SQLException { String sql SELECT id, name, price, stock FROM products WHERE id ? FOR SHARE; try (Connection conn dataSource.getConnection(); PreparedStatement stmt conn.prepareStatement(sql)) { conn.setAutoCommit(false); // 开启事务 stmt.setLong(1, productId); ResultSet rs stmt.executeQuery(); if (rs.next()) { Product product new Product(); product.setId(rs.getLong(id)); product.setName(rs.getString(name)); product.setPrice(rs.getBigDecimal(price)); product.setStock(rs.getInt(stock)); // 注意不要在这里提交事务 // 保持事务打开直到完成后续操作 return product; } conn.rollback(); throw new IllegalArgumentException(Product not found: productId); } } /** * 扣减库存在同一个事务中 */ public void deductStock(Long productId, int quantity) throws SQLException { String sql UPDATE products SET stock stock - ? WHERE id ? AND stock ?; try (Connection conn dataSource.getConnection(); PreparedStatement stmt conn.prepareStatement(sql)) { conn.setAutoCommit(false); stmt.setInt(1, quantity); stmt.setLong(2, productId); stmt.setInt(3, quantity); int updated stmt.executeUpdate(); if (updated 0) { conn.rollback(); throw new IllegalStateException(Insufficient stock for product: productId); } conn.commit(); } } }在这个例子中FOR SHARE子句确保了在事务提交之前其他事务不能修改该商品的库存信息。4.3 订单处理中的意向锁模式在处理订单时我们通常需要先检查多个商品的库存然后批量扣减。这时可以利用PostgreSQL的意向锁机制来优化并发性能。public class OrderService { private final DataSource dataSource; private final InventoryService inventoryService; public OrderService(DataSource dataSource, InventoryService inventoryService) { this.dataSource dataSource; this.inventoryService inventoryService; } /** * 处理订单使用意向锁模式 */ public void processOrder(ListOrderItem items) throws SQLException { Connection conn null; try { conn dataSource.getConnection(); conn.setAutoCommit(false); // 步骤1: 检查所有商品的库存获取ROW SHARE锁 for (OrderItem item : items) { String checkSql SELECT stock FROM products WHERE id ? FOR SHARE; try (PreparedStatement stmt conn.prepareStatement(checkSql)) { stmt.setLong(1, item.getProductId()); ResultSet rs stmt.executeQuery(); if (!rs.next()) { throw new IllegalArgumentException(Product not found: item.getProductId()); } int currentStock rs.getInt(stock); if (currentStock item.getQuantity()) { throw new IllegalStateException(Insufficient stock for product: item.getProductId()); } } } // 步骤2: 扣减库存升级为ROW EXCLUSIVE锁 for (OrderItem item : items) { String updateSql UPDATE products SET stock stock - ? WHERE id ?; try (PreparedStatement stmt conn.prepareStatement(updateSql)) { stmt.setInt(1, item.getQuantity()); stmt.setLong(2, item.getProductId()); stmt.executeUpdate(); } } // 步骤3: 创建订单记录 createOrderRecord(conn, items); conn.commit(); } catch (SQLException e) { if (conn ! null) { conn.rollback(); } throw e; } finally { if (conn ! null) { conn.close(); } } } private void createOrderRecord(Connection conn, ListOrderItem items) throws SQLException { // 创建订单主表记录 String orderSql INSERT INTO orders (created_at) VALUES (NOW()) RETURNING id; long orderId; try (PreparedStatement stmt conn.prepareStatement(orderSql)) { ResultSet rs stmt.executeQuery(); rs.next(); orderId rs.getLong(id); } // 创建订单明细记录 String detailSql INSERT INTO order_items (order_id, product_id, quantity) VALUES (?, ?, ?); try (PreparedStatement stmt conn.prepareStatement(detailSql)) { for (OrderItem item : items) { stmt.setLong(1, orderId); stmt.setLong(2, item.getProductId()); stmt.setInt(3, item.getQuantity()); stmt.addBatch(); } stmt.executeBatch(); } } }在这个实现中我们首先使用FOR SHARE获取所有相关商品的共享锁然后在更新时自动升级为排他锁。这种模式充分利用了PostgreSQL的意向锁机制既保证了数据一致性又最大化了并发性能。4.4 避免死锁的最佳实践死锁是并发系统中最棘手的问题之一。在PostgreSQL中虽然数据库会自动检测并解决死锁通过回滚其中一个事务但我们应该尽量避免死锁的发生。public class DeadlockAvoidanceService { private final DataSource dataSource; public DeadlockAvoidanceService(DataSource dataSource) { this.dataSource dataSource; } /** * 安全的转账操作避免死锁 */ public void transferMoney(Long fromAccountId, Long toAccountId, BigDecimal amount) throws SQLException { // 关键总是按照相同的顺序获取锁 // 例如总是先锁定ID较小的账户 Long firstAccountId fromAccountId.compareTo(toAccountId) 0 ? fromAccountId : toAccountId; Long secondAccountId fromAccountId.equals(firstAccountId) ? toAccountId : fromAccountId; Connection conn null; try { conn dataSource.getConnection(); conn.setAutoCommit(false); // 按照固定顺序获取行级锁 lockAccountForUpdate(conn, firstAccountId); lockAccountForUpdate(conn, secondAccountId); // 执行转账辑 updateAccountBalance(conn, fromAccountId, amount.negate()); updateAccountBalance(conn, toAccountId, amount); conn.commit(); } catch (SQLException e) { if (conn ! null) { conn.rollback(); } throw e; } finally { if (conn ! null) { conn.close(); } } } private void lockAccountForUpdate(Connection conn, Long accountId) throws SQLException { String sql SELECT balance FROM accounts WHERE id ? FOR UPDATE; try (PreparedStatement stmt conn.prepareStatement(sql)) { stmt.setLong(1, accountId); stmt.executeQuery(); // 获取排他锁 } } private void updateAccountBalance(Connection conn, Long accountId, BigDecimal amount) throws SQLException { String sql UPDATE accounts SET balance balance ? WHERE id ?; try (PreparedStatement stmt conn.prepareStatement(sql)) { stmt.setBigDecimal(1, amount); stmt.setLong(2, accountId); stmt.executeUpdate(); } } }死锁避免的关键原则按固定顺序获取锁总是按照相同的顺序如ID升序获取资源锁最小化锁持有时间尽快完成操作并释放锁使用合适的隔离级别在某些场景下使用READ COMMITTED而不是SERIALIZABLE5. 锁监控与性能调优在生产环境中监控和调优锁机制对于系统性能至关重要。PostgreSQL提供了丰富的视图和工具来帮助我们分析锁的使用情况。5.1 查看当前锁信息PostgreSQL的pg_locks视图包含了当前所有锁的信息-- 查看当前所有锁 SELECT pid, locktype, database, relation::regclass, page, tuple, virtualxid, transactionid, mode, granted FROM pg_locks WHERE pid pg_backend_pid(); -- 查看阻塞的查询 SELECT blocked_locks.pid AS blocked_pid, blocked_activity.usename AS blocked_user, blocking_locks.pid AS blocking_pid, blocking_activity.usename AS blocking_user, blocked_activity.query AS blocked_statement, blocking_activity.query AS blocking_statement FROM pg_catalog.pg_locks blocked_locks JOIN pg_catalog.pg_stat_activity blocked_activity ON blocked_activity.pid blocked_locks.pid JOIN pg_catalog.pg_locks blocking_locks ON blocking_locks.locktype blocked_locks.locktype AND blocking_locks.database IS NOT DISTINCT FROM blocked_locks.database AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid AND blocking_locks.pid ! blocked_locks.pid JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid blocking_locks.pid WHERE NOT blocked_locks.granted;5.2 Java中的锁监控实现我们可以在Java应用中集成锁监控功能public class LockMonitoringService { private final DataSource dataSource; public LockMonitoringService(DataSource dataSource) { this.dataSource dataSource; } /** * 检查是否存在阻塞的查询 */ public ListBlockingQuery getBlockingQueries() throws SQLException { String sql SELECT blocked_locks.pid AS blocked_pid, blocked_activity.usename AS blocked_user, blocking_locks.pid AS blocking_pid, blocking_activity.usename AS blocking_user, blocked_activity.query AS blocked_statement, blocking_activity.query AS blocking_statement, blocked_activity.query_start AS blocked_since FROM pg_catalog.pg_locks blocked_locks JOIN pg_catalog.pg_stat_activity blocked_activity ON blocked_activity.pid blocked_locks.pid JOIN pg_catalog.pg_locks blocking_locks ON blocking_locks.locktype blocked_locks.locktype AND blocking_locks.database IS NOT DISTINCT FROM blocked_locks.database AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid AND blocking_locks.pid ! blocked_locks.pid JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid blocking_locks.pid WHERE NOT blocked_locks.granted ; ListBlockingQuery blockingQueries new ArrayList(); try (Connection conn dataSource.getConnection(); PreparedStatement stmt conn.prepareStatement(sql); ResultSet rs stmt.executeQuery()) { while (rs.next()) { BlockingQuery query new BlockingQuery(); query.setBlockedPid(rs.getLong(blocked_pid)); query.setBlockedUser(rs.getString(blocked_user)); query.setBlockingPid(rs.getLong(blocking_pid)); query.setBlockingUser(rs.getString(blocking_user)); query.setBlockedStatement(rs.getString(blocked_statement)); query.setBlockingStatement(rs.getString(blocking_statement)); query.setBlockedSince(rs.getTimestamp(blocked_since)); blockingQueries.add(query); } } return blockingQueries; } /** * 记录长时间运行的事务 */ public void logLongRunningTransactions(Duration threshold) throws SQLException { String sql SELECT pid, usename, application_name, client_addr, backend_start, xact_start, query_start, state_change, wait_event_type, wait_event, state, query FROM pg_stat_activity WHERE xact_start IS NOT NULL AND now() - xact_start ? AND pid pg_backend_pid() ORDER BY xact_start; ; try (Connection conn dataSource.getConnection(); PreparedStatement stmt conn.prepareStatement(sql)) { stmt.setObject(1, threshold.toString()); try (ResultSet rs stmt.executeQuery()) { while (rs.next()) { // 记录日志或发送告警 System.out.println(Long running transaction detected: rs.getLong(pid) - rs.getString(query)); } } } } }5.3 性能调优建议基于锁机制的性能调优以下是一些最佳实践选择合适的隔离级别对于大多数应用READ COMMITTED是最佳选择只在必要时使用REPEATABLE READ或SERIALIZABLE最小化事务范围尽快提交事务减少锁持有时间避免在事务中进行耗时的业逻辑处理使用合适的锁粒度优先使用行级锁而不是表级锁避免不必要的LOCK TABLE语句优化查询性能确保查询能够使用索引减少锁竞争避免全表扫描导致的大量行锁监控和告警设置锁等待超时监控对长时间运行的事务进行告警6. 高级锁模式的实际应用案例6.1 分布式任务调度系统在分布式系统中多个节点可能同时尝试处理同一个任务。我们需要确保每个任务只被一个节点处理。public class DistributedTaskScheduler { private final DataSource dataSource; public DistributedTaskScheduler(DataSource dataSource) { this.dataSource dataSource; } /** * 获取下一个可执行的任务 */ public Task acquireNextTask() throws SQLException { String sql UPDATE tasks SET status PROCESSING, worker_id ?, updated_at NOW() WHERE id ( SELECT id FROM tasks WHERE status PENDING AND scheduled_time NOW() ORDER BY priority DESC, created_at ASC LIMIT 1 FOR UPDATE SKIP LOCKED ) RETURNING id, task_data, priority; ; try (Connection conn dataSource.getConnection(); PreparedStatement stmt conn.prepareStatement(sql)) { conn.setAutoCommit(false); stmt.setString(1, getWorkerId()); // 当前工作节点ID try (ResultSet rs stmt.executeQuery()) { if (rs.next()) { Task task new Task(); task.setId(rs.getLong(id)); task.setTaskData(rs.getString(task_data)); task.setPriority(rs.getInt(priority)); conn.commit(); return task; } } conn.rollback(); return null; // 没有可执行的任务 } } /** * 标记任务完成 */ public void markTaskCompleted(Long taskId) throws SQLException { String sql UPDATE tasks SET status COMPLETED, updated_at NOW() WHERE id ?; try (Connection conn dataSource.getConnection(); PreparedStatement stmt conn.prepareStatement(sql)) { stmt.setLong(1, taskId); stmt.executeUpdate(); } } private String getWorkerId() { // 返回当前工作节点的唯一标识 return System.getProperty(worker.id, default-worker); } }关键技术点FOR UPDATE SKIP LOCKED跳过已被其他事务锁定的行避免阻塞直接在UPDATE中完成任务状态转换原子性保证利用PostgreSQL的行级锁机制实现分布式锁6.2 实时库存管理系统在高并发的电商场景中库存管理是一个经典挑战。我们需要处理大量并发的库存查询和扣减操作。public class RealTimeInventoryManager { private final DataSource dataSource; private final ExecutorService executorService; public RealTimeInventoryManager(DataSource dataSource) { this.dataSource dataSource; this.executorService Executors.newFixedThreadPool(10); } /** * 异步处理库存扣减请求 */ public CompletableFutureBoolean deductInventoryAsync(Long productId, int quantity) { return CompletableFuture.supplyAsync(() - { try { return deductInventory(productId, quantity); } catch (SQLException e) { throw new RuntimeException(Inventory deduction failed, e); } }, executorService); } /** * 同步库存扣减使用乐观锁 */ private boolean deductInventory(Long productId, int quantity) throws SQLException { // 首先尝试乐观锁方式 if (tryOptimisticDeduction(productId, quantity)) { return true; } // 如果乐观锁失败使用悲观锁重试 return tryPessimisticDeduction(productId, quantity); } /** * 乐观锁方式基于版本号 */ private boolean tryOptimisticDeduction(Long productId, int quantity) throws SQLException { String sql UPDATE products SET stock stock - ?, version version 1 WHERE id ? AND stock ? AND version ? ; try (Connection conn dataSource.getConnection(); PreparedStatement stmt conn.prepareStatement(sql)) { // 先获取当前版本 int currentVersion getCurrentVersion(conn, productId); if (currentVersion -1) { return false; } stmt.setInt(1, quantity); stmt.setLong(2, productId); stmt.setInt(3, quantity); stmt.setInt(4, currentVersion); return stmt.executeUpdate() 0; } } /** * 悲观锁方式使用FOR UPDATE */ private boolean tryPessimisticDeduction(Long productId, int quantity) throws SQLException { String sql SELECT stock, version FROM products WHERE id ? FOR UPDATE; try (Connection conn dataSource.getConnection(); PreparedStatement stmt conn.prepareStatement(sql)) { conn.setAutoCommit(false); stmt.setLong(1, productId); ResultSet rs stmt.executeQuery(); if (!rs.next()) { conn.rollback(); return false; } int currentStock rs.getInt(stock); if (currentStock quantity) { conn.rollback(); return false; } // 执行扣减 String updateSql UPDATE products SET stock stock - ? WHERE id ?; try (PreparedStatement updateStmt conn.prepareStatement(updateSql)) { updateStmt.setInt(1, quantity); updateStmt.setLong(2, productId); updateStmt.executeUpdate(); } conn.commit(); return true; } } private int getCurrentVersion(Connection conn, Long productId) throws SQLException { String sql SELECT version FROM products WHERE id ?; try (PreparedStatement stmt conn.prepareStatement(sql)) { stmt.setLong(1, productId); ResultSet rs stmt.executeQuery(); return rs.next() ? rs.getInt(version) : -1; } } }设计要点混合锁策略先尝试乐观锁高性能失败后使用悲观锁强一致性版本控制通过version字段实现乐观锁异步处理提高系统吞吐量错误处理优雅处理库存不足等情况7. 锁机制与隔离级别的关系PostgreSQL的锁机制与事务隔离级别密切相关。理解它们之间的关系对于正确设计并发应用至关重要。7.1 四种隔离级别及其锁行为PostgreSQL支持四种标准的事务隔离级别READ UNCOMMITTED实际上等同于READ COMMITTEDREAD COMMITTED默认每次查询都看到最新的已提交数据REPEATABLE READ事务内多次查询看到相同的数据快照SERIALIZABLE提供最严格的隔离完全避免并发异常7.2 不同隔离级别下的锁行为7.2.1 READ COMMITTED默认级别在READ COMMITTED级别下普通SELECT不获取任何锁使用MVCC快照SELECT FOR UPDATE/SHARE获取行级锁UPDATE/DELETE自动获取行级排他锁每次查询都看到最新的已提交数据// READ COMMITTED示例 public void readCommittedExample() throws SQLException { // 会话1 Connection conn1 dataSource.getConnection(); conn1.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED); conn1.setAutoCommit(false); // 查询1看到初始数据 Product p1 getProduct(conn1, 1L); // 此时会话2修改了数据并提交 // ... // 查询2看到会话2的修改 Product p2 getProduct(conn1, 1L); // p1和p2可能不同 }7.2.2 REPEATABLE READ在REPEATABLE READ级别下事务开始时创建数据快照同一事务内的多次查询看到相同的数据使用SIREAD锁来检测潜在的序列化冲突// REPEATABLE READ示例 public void repeatableReadExample() throws SQLException { // 会话1 Connection conn1 dataSource.getConnection(); conn1.setTransactionIsolation(Connection.TRANSACTION_REPEATABLE_READ); conn1.setAutoCommit(false); // 查询1看到初始数据 Product p1 getProduct(conn1, 1L); // 此时会话2修改了数据并提交 // ... // 查询2仍然看到初始数据与p1相同 Product p2 getProduct(conn1, 1L); // p1和p2相同 }7.2.3 SERIALIZABLE在SERIALIZABLE级别下提供最严格的隔离保证使用谓词锁Predicate Locks检测写偏斜可能因检测到序列化冲突而回滚事务// SERIALIZABLE示例 public void serializableExample() throws SQLException { try { Connection conn1 dataSource.getConnection(); conn1.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE); conn1.setAutoCommit(false); // 查询满足条件的记录数 int count1 getCount(conn1, status active); // 此时会话2插入了满足条件的新记录 // ... // 再次查询 int count2 getCount(conn1, status active); // 如果count1 ! count2可能会抛出序列化异常 conn1.commit(); } catch (SQLTransactionRollbackException e) { if (40001.equals(e.getSQLState())) { // 序列化失败需要重试 retryTransaction(); } } }8. 常见陷阱与解决方案在使用PostgreSQL锁机制时开发者经常会遇到一些陷阱。让我们看看如何避免和解决这些问题。8.1 隐式锁升级有时候看似简单的操作会触发意外的锁行为。// 危险的代码示例 public void dangerousUpdate() throws SQLException { String sql UPDATE products SET price price * 1.1 WHERE category electronics; // 这个UPDATE会在所有匹配的行上获取排他锁 // 如果category electronics匹配大量行会导致严重的锁竞争 }解决方案分批处理大更新使用LIMIT和循环在低峰期执行大批量操作// 安全的分批更新 public void safeBatchUpdate() throws SQLException { int batchSize 100; int updated; do { String sql UPDATE products SET price price * 1.1 WHERE category electronics AND id IN ( SELECT id FROM products WHERE category electronics AND price_updated false LIMIT ? ) ; try (Connection conn dataSource.getConnection(); PreparedStatement stmt conn.prepareStatement(sql)) { stmt.setInt(1, batchSize); updated stmt.executeUpdate(); // 短暂休眠减少锁竞争 Thread.sleep(100); } } while (updated 0); }8.2 长事务持有锁长时间运行的事务会持有锁阻塞其他事务。// 危险的代码示例 public void longRunningTransaction() throws SQLException { Connection conn dataSource.getConnection(); conn.setAutoCommit(false); // 获取锁 Product product getProductForUpdate(conn, 1L); // 危险在这里进行耗时的业务逻辑处理 // 其他事务会被阻塞 Thread.sleep(30000); // 30秒 // 执行更新 update