数据库事务与并发控制

数据库事务与并发控制

1. 技术分析

1.1 事务概述

事务是数据库的核心概念：

事务特性(ACID) Atomicity: 原子性 Consistency: 一致性 Isolation: 隔离性 Durability: 持久性 事务状态: 活动状态 部分提交状态 失败状态 中止状态 提交状态

1.2 并发控制

并发问题 脏读: 读取未提交数据 不可重复读: 同一事务内重复读取不同结果 幻读: 新数据出现 隔离级别: READ UNCOMMITTED READ COMMITTED REPEATABLE READ SERIALIZABLE

1.3 锁机制

2. 核心功能实现

2.1 事务控制

-- 开启事务 START TRANSACTION; -- 设置隔离级别 SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; -- 执行操作 UPDATE accounts SET balance = balance - 100 WHERE id = 1; UPDATE accounts SET balance = balance + 100 WHERE id = 2; -- 提交事务 COMMIT; -- 回滚事务 ROLLBACK; -- 保存点 SAVEPOINT before_transfer; ROLLBACK TO SAVEPOINT before_transfer;

2.2 锁机制实现

-- 显式加锁 SELECT * FROM products WHERE id = 1 FOR UPDATE; -- 共享锁 SELECT * FROM products WHERE id = 1 LOCK IN SHARE MODE; -- 乐观锁 UPDATE products SET stock = stock - 1, version = version + 1 WHERE id = 1 AND version = 1; -- 悲观锁 SELECT * FROM orders WHERE status = 'pending' FOR UPDATE SKIP LOCKED;

2.3 并发控制工具

class TransactionManager: def __init__(self, connection): self.connection = connection def begin_transaction(self, isolation_level='REPEATABLE READ'): self.connection.execute(f"SET TRANSACTION ISOLATION LEVEL {isolation_level}") self.connection.execute("START TRANSACTION") def commit(self): try: self.connection.execute("COMMIT") return True except Exception as e: self.rollback() return False def rollback(self): self.connection.execute("ROLLBACK") def savepoint(self, name): self.connection.execute(f"SAVEPOINT {name}") def rollback_to_savepoint(self, name): self.connection.execute(f"ROLLBACK TO SAVEPOINT {name}") class OptimisticLockManager: def __init__(self, connection): self.connection = connection def update_with_version(self, table, id, updates): version = self._get_version(table, id) set_clause = ', '.join([f"{k} = {v}" for k, v in updates.items()]) set_clause += ', version = version + 1' sql = f"UPDATE {table} SET {set_clause} WHERE id = {id} AND version = {version}" cursor = self.connection.cursor() cursor.execute(sql) return cursor.rowcount > 0 def _get_version(self, table, id): sql = f"SELECT version FROM {table} WHERE id = {id}" cursor = self.connection.cursor() cursor.execute(sql) result = cursor.fetchone() return result[0] if result else 0

3. 性能对比

3.1 隔离级别对比

3.2 锁策略对比

3.3 事务模式对比

4. 最佳实践

4.1 事务设计原则

class TransactionDesign: @staticmethod def keep_transactions_short(): return "事务应尽快完成，减少锁持有时间" @staticmethod def minimize_locking(): return "只锁定必要的数据" @staticmethod def use_appropriate_isolation(): return "选择合适的隔离级别"

4.2 死锁处理

class DeadlockHandler: def __init__(self, connection): self.connection = connection def detect_deadlock(self): sql = "SHOW ENGINE INNODB STATUS" cursor = self.connection.cursor() cursor.execute(sql) result = cursor.fetchone() return 'DEADLOCK' in str(result) def handle_deadlock(self, max_retries=3): for attempt in range(max_retries): try: self.connection.execute("COMMIT") return True except Exception as e: if 'Deadlock' in str(e): self.connection.execute("ROLLBACK") continue else: raise return False

5. 总结

事务与并发控制是数据库的核心：

1. ACID特性：保证数据一致性
2. 隔离级别：平衡一致性与性能
3. 锁机制：控制数据访问
4. 并发策略：乐观锁与悲观锁

对比数据如下：

• READ COMMITTED是最常用的隔离级别
• 乐观锁适合读多写少场景
• 事务应尽量短小
• InnoDB的MVCC提供更好的并发性能