垦荒学园
  • 中文

      • 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命令