MySQL中的隔离级别和悲观锁及乐观锁示例

MySQL中的隔离级别和悲观锁及乐观锁示例

1，MySQL的事务支持
MySQL的事务支持不是绑定在MySQL服务器本身，而是与存储引擎相关：

MyISAM：不支持事务，用于只读程序提高性能
InnoDB：支持ACID事务、行级锁、并发
Berkeley DB：支持事务

2，隔离级别
隔离级别决定了一个session中的事务可能对另一个session的影响、并发session对数据库的操作、一个session中所见数据的一致性
ANSI标准定义了4个隔离级别，MySQL的InnoDB都支持：

Java代码

READ UNCOMMITTED：最低级别的隔离，通常又称为dirty read，它允许一个事务读取还没commit的数据，这样可能会提高性能，但是dirty read可能不是我们想要的
READ COMMITTED：在一个事务中只允许已经commit的记录可见，如果session中select还在查询中，另一session此时insert一条记录，则新添加的数据不可见
REPEATABLE READ：在一个事务开始后，其他session对数据库的修改在本事务中不可见，直到本事务commit或rollback。在一个事务中重复select的结果一样，除非本事务中update数据库。
SERIALIZABLE：最高级别的隔离，只允许事务串行执行。为了达到此目的，数据库会锁住每行已经读取的记录，其他session不能修改数据直到前一事务结束，事务commit或取消时才释放锁。

可以使用如下语句设置MySQL的session隔离级别：

[c-sharp]view plaincopy 
1.  SET TRANSACTION ISOLATION LEVEL {READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE}   

MySQL默认的隔离级别是REPEATABLE READ，在设置隔离级别为READ UNCOMMITTED或SERIALIZABLE时要小心，READ UNCOMMITTED会导致数据完整性的严重问题，而SERIALIZABLE会导致性能问题并增加死锁的机率

3，隔离级别
乐观所和悲观锁策略：
悲观锁：在读取数据时锁住那几行，其他对这几行的更新需要等到悲观锁结束时才能继续
乐观所：读取数据时不锁，更新时检查是否数据已经被更新过，如果是则取消当前更新
一般在悲观锁的等待时间过长而不能接受时我们才会选择乐观锁
悲观锁的例子：

[c-sharp]view plaincopy 
CREATE PROCEDURE tfer_funds     
       (from_account INT, to_account INT,tfer_amount NUMERIC(10,2),     
        OUT status INT, OUT message VARCHAR(30))     
BEGIN     
    DECLARE from_account_balance NUMERIC(10,2);     
    
    START TRANSACTION;     
    
    
    SELECT balance     
      INTO from_account_balance     
      FROM account_balance     
     WHERE account_id=from_account     
       FOR UPDATE;     
    
    IF from_account_balance>=tfer_amount THEN     
    
         UPDATE account_balance     
            SET balance=balance-tfer_amount     
          WHERE account_id=from_account;     
    
         UPDATE account_balance     
            SET balance=balance+tfer_amount     
          WHERE account_id=to_account;     
         COMMIT;     
    
         SET status=0;     
         SET message='OK';     
    ELSE     
         ROLLBACK;     
         SET status=-1;     
         SET message='Insufficient funds';     
    END IF;     
END;    

乐观锁的例子：

[c-sharp]view plaincopy 
CREATE PROCEDURE tfer_funds     
    (from_account INT, to_account INT, tfer_amount NUMERIC(10,2),     
        OUT status INT, OUT message VARCHAR(30) )     
    
BEGIN     
    
    DECLARE from_account_balance    NUMERIC(8,2);     
    DECLARE from_account_balance2   NUMERIC(8,2);     
    DECLARE from_account_timestamp1 TIMESTAMP;     
    DECLARE from_account_timestamp2 TIMESTAMP;     
    
    SELECT account_timestamp,balance     
        INTO from_account_timestamp1,from_account_balance     
            FROM account_balance     
            WHERE account_id=from_account;     
    
    IF (from_account_balance>=tfer_amount) THEN     
    
        -- Here we perform some long running validation that     
        -- might take a few minutes */     
        CALL long_running_validation(from_account);     
    
        START TRANSACTION;     
    
        -- Make sure the account row has not been updated since     
        -- our initial check     
        SELECT account_timestamp, balance     
            INTO from_account_timestamp2,from_account_balance2     
            FROM account_balance     
            WHERE account_id=from_account     
            FOR UPDATE;     
    
        IF (from_account_timestamp1 <> from_account_timestamp2 OR     
            from_account_balance    <> from_account_balance2)  THEN     
            ROLLBACK;     
            SET status=-1;     
            SET message=CONCAT("Transaction cancelled due to concurrent update",     
                " of account"  ,from_account);     
        ELSE     
            UPDATE account_balance     
                SET balance=balance-tfer_amount     
                WHERE account_id=from_account;     
    
            UPDATE account_balance     
                SET balance=balance+tfer_amount     
                WHERE account_id=to_account;     
    
            COMMIT;     
    
            SET status=0;     
            SET message="OK";     
        END IF;     
    
    ELSE     
        ROLLBACK;     
        SET status=-1;     
        SET message="Insufficient funds";     
    END IF;     
END$$    

MySQL中的隔离级别和悲观锁及乐观锁示例

相关推荐