MySQL事务

概述

事务是作为单个工作单元执行的数据库操作序列。事务通过确保事务中的所有操作要么全部成功完成，要么全部回滚，从而确保数据完整性。

ACID属性

原子性：所有操作成功或全部失败 一致性：数据库保持有效状态 隔离性：事务互不干扰 持久性：提交的更改持久保存

事务基础

START TRANSACTION / START TRANSACTION

-- Start a transaction

-- Alternative syntax

-- Start with specific isolation level

COMMIT / COMMIT

-- Commit changes

-- Changes are now permanent

ROLLBACK / ROLLBACK

-- Rollback changes

-- Something goes wrong
-- Changes are undone

完整示例

-- Start transaction

-- Check inventory

-- Check if enough stock
    -- Create order
    
    -- Update inventory
    
    -- Commit if successful
    -- Rollback if not enough stock

事务命令

SET AUTOCOMMIT / SET AUTOCOMMIT

-- Disable autocommit (transaction mode)

-- Enable autocommit (auto-commit each statement)

-- Check autocommit status

自动提交行为

-- With autocommit ON (default)

-- With autocommit OFF

-- Or rollback

隔离级别

隔离级别概述

级别	Dirty Reads / 脏读	Non-Repeatable Reads / 不可重复读	Phantom Reads / 幻读
是	Yes / 是	Yes / 是
否	Yes / 是	Yes / 是
否	No / 否	Yes / 是
否	No / 否	No / 否

设置隔离级别

-- Global level

-- Session level

-- Next transaction only

-- Check current isolation level

-- For specific transaction

隔离级别示例

-- READ COMMITTED (prevents dirty reads)
-- Other transaction can insert new products

-- REPEATABLE READ (prevents non-repeatable reads)
-- Other transaction's inserts not visible until you commit

-- SERIALIZABLE (prevents phantom reads)
-- Other transactions blocked until you commit

事务控制语句

保存点

-- Create savepoints within transaction

-- Rollback to specific savepoint
-- inventory_update undone, order_created remains

-- Rollback to earlier savepoint
-- Both undone, back to start

-- Release savepoint
-- savepoint removed

条件回滚

-- Update inventory

-- Check if update affected rows

实用示例

银行转账

-- Ensure both accounts updated or neither

-- Debit from account A

-- Credit to account B

-- Verify both succeeded

订单处理

-- Create order

-- Add order items

-- Update inventory

-- Check inventory

    -- Update order status
    
    -- Not enough inventory

批量操作

-- Process multiple records

    
    -- Process each user

事务日志

二进制日志

-- Check if binary logging is enabled

-- View binary log files

-- Check current binary log position

InnoDB日志

-- Check InnoDB log settings

-- Check transaction isolation

事务锁定

锁类型

-- Shared lock (read lock)

-- Exclusive lock (write lock)

-- Nowait lock (returns immediately if locked)

锁超时

-- Set lock timeout

-- Transaction with timeout
-- Waits up to 50 seconds for lock

事务性能

优化提示

-- Keep transactions short
-- Long transactions hold locks longer

-- Minimize data in transactions

-- Avoid large transactions

-- Use appropriate isolation levels
-- Higher isolation = more locking overhead

监控事务

-- View current transactions

-- View transaction locks

-- View lock waits

-- View long-running transactions

死锁

死锁检测

-- InnoDB automatically detects deadlocks
-- Rolls back one transaction to resolve

-- Deadlock example
-- Transaction 1:
-- Waits for lock on id=2

-- Transaction 2:
-- Waits for lock on id=1

-- Deadlock! One transaction rolled back automatically

处理死锁

-- Retry logic for deadlocks

            -- Deadlock error code
        
        -- Your transaction logic here

分布式事务

两阶段提交

-- Simplified two-phase commit concept

-- Phase 1: Prepare

-- Phase 2: Commit

最佳实践

事务设计

-- 1. Keep transactions short
-- Few statements

-- 2. Minimize data accessed
-- Use WHERE clause to lock only necessary rows

-- 3. Use appropriate isolation level
-- Don't use SERIALIZABLE unless necessary

-- 4. Handle errors properly
    -- Log error

-- 5. Use savepoints for complex transactions
-- Operation 1
-- Operation 2
-- If error, rollback to sp1

错误处理

-- Error handling in stored procedures

小结

MySQL事务提供：

数据完整性：ACID属性确保可靠性 控制：提交和回滚更改 灵活性：保存点实现部分回滚 隔离：多种级别的并发控制 安全性：自动死锁检测

---

上一个：正则表达式

下一个：ALTER命令