[转]MySQL中的隔离级别跟悲观锁及乐观锁_mysql

一、事务支持

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

    1. MyISAM 不支持事务，用于只读程序提高性能；

    2. InnoDB 支持ACID事务，行级锁、并发；

    3. Berkeley DB 支持事务。

二、隔离级别

    隔离级别决定了一个session中的事务可能对另一个session的影响、并发session对数据库的操作、一个session中所见数据的一致性。

    ANSI标准定义了4个隔离级别，MySQL的InnoDB都支持：

    1. READ-UNCOMMITTED：最低级别的隔离，通常又称为dirty read，它允许一个事务读取还没commit的数据，这样可能会提高性能，但是dirty read可能不是我们想要的。

    2. READ-COMMITTED：在一个事务中只允许已经commit的记录可见，如果session中select还在查询中，另一session此时insert一条记录，则新添加的数据不可见。

    3. REPEATABLE-READ：在一个事务开始后，其他session对数据库的修改在本事务中不可见，直到本事务commit或rollback。在一个事务中重复select的结果一样，除非本事务中update数据库。

    4. SERIALIZABLE：最高级别的隔离，只允许事务串行执行。为了达到此目的，数据库会锁住每行已经读取的记录，其他session不能修改数据直到前一事务结束，事务commit或取消时才释放锁。

    MySQL 隔离级别设置：

# 设置 SET TRANSACTION ISOLATION LEVEL {READ-UNCOMMITTED | READ-COMMITTED | REPEATABLE-READ | SERIALIZABLE}  # 查看 SELECT @@tx_isolation

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

三、悲观锁及乐观锁

    悲观锁：在读取数据时锁住那几行，其他对这几行的更新需要等到悲观锁结束时才能继续；
    乐观所：读取数据时不锁，更新时检查是否数据已经被更新过，如果是则取消当前更新。

    一般在悲观锁的等待时间过长而不能接受时我们才会选择乐观锁 。两种锁各有优缺点，不可认为一种好于另一种，像乐观锁适用于写比较少的情况下，即冲突真的很少发生的时候，这样可以省去了锁的开销，加大了系统的整个吞吐量。但如果经常产生冲突，上层应用会不断的进行retry，这样反倒是降低了性能，所以这种情况下用悲观锁就比较合适。

    悲观锁示例：

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;   

     乐观锁示例：

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$$  

参考文章：http://blog.csdn.net/zztfj/article/details/6319740