`
taimukang
  • 浏览: 183951 次
  • 性别: Icon_minigender_1
  • 来自: 广州
社区版块
存档分类
最新评论

Mysql事务处理

MySQLOracleSQL设计模式 
阅读更多

一、事务

 

【定义】事务：是指作为单个逻辑工作单元执行的一系列操作

 

      当前mysql的存储引擎中只有innodb和BDB实现了事务的ACID，并且实现机制和oracle是一致的，主要使用了mvcc的实现理论。

      mysql的事务分两种，一种是标准的事务，也叫normal transaction ,还有一个叫statement transaction。其中normal transaction是标准的实现ACID的事务，而statement transaction是就是一个语句是一个事务。平时我们可以设置mysql autocommit为true，其实这里是只有statement transaction，没有normal transaction，就是把每个statement transaction当作一个normal transaction。

 

MYSQL的事务处理主要有两种方法：
 
  1、用begin,rollback,commit来实现
  begin 开始一个事务
  rollback 事务回滚
  commit 事务确认
 
  2、直接用set来改变mysql的自动提交模式
  MYSQL默认是自动提交的，也就是你提交一个QUERY，它就直接执行！我们可以通过
  set autocommit=0 禁止自动提交
  set autocommit=1 开启自动提交
  来实现事务的处理。
  当你用 set autocommit=0 的时候，你以后所有的SQL都将做为事务处理，直到你用commit确认或rollback结束。
  注意当你结束这个事务的同时也开启了个新的事务！按第一种方法只将当前的作为一个事务！
 
  常用的是第一种方法！

 

 

二、定义事务
MySQL默认的行为是在每条SQL语句执行后执行一个COMMIT语句，从而有效的将每条语句独立为一个事务。
在复杂的应用场景下这种方式就不能满足需求了。
为了打开事务，允许在COMMIT和ROLLBACK之前多条语句被执行，我们需要做以下两步：
1, 设置MySQL的autocommit属性为0，默认为1
2，使用START TRANSACTION语句显式的打开一个事务

如果已经打开一个事务，则SET autocommit=0不会起作用，因为START TRANSACTION会隐式的提交session中所有当前的更改，结束已有的事务，并打开一个新的事务。

使用SET AUTOCOMMIT语句的存储过程例子：

Java代码
  1. CREATE PROCEDURE tfer_funds  
  2.     (from_account int , to_account  int , tfer_amount numeric( 10 , 2 ))  
  3. BEGIN  
  4.     SET autocommit=0 ;  
  5.   
  6.     UPDATE account_balance SET balance=balance-tfer_amount WHERE account_id=from_account;  
  7.   
  8.     UPDATE account_balance SET balance=balance+tfer_amount WHERE account_id=to_account;  
  9.   
  10.     COMMIT;  
  11. END;   
CREATE PROCEDURE tfer_funds
    (from_account int, to_account int, tfer_amount numeric(10,2))
BEGIN
    SET autocommit=0;

    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;
END;


使用START TRANSACITON打开事务的例子：

Java代码
  1. CREATE PROCEDURE tfer_funds  
  2.     (from_account int , to_account  int , tfer_amount numeric( 10 , 2 ))  
  3. BEGIN  
  4.     START TRANSACTION;  
  5.   
  6.     UPDATE account_balance SET balance=balance-tfer_amount WHERE account_id=from_account;  
  7.   
  8.     UPDATE account_balance SET balance=balance+tfer_amount WHERE account_id=to_account;  
  9.   
  10.     COMMIT;  
  11. END;   
CREATE PROCEDURE tfer_funds
    (from_account int, to_account int, tfer_amount numeric(10,2))
BEGIN
    START TRANSACTION;

    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;
END;



通常COMMIT或ROLLBACK语句执行时才完成一个事务，但是有些DDL语句等会隐式触发COMMIT，所以应该在事务中尽可能少用或注意一下：

Java代码
  1. ALTER FUNCTION  
  2. ALTER PROCEDURE  
  3. ALTER TABLE  
  4. BEGIN  
  5. CREATE DATABASE  
  6. CREATE FUNCTION  
  7. CREATE INDEX  
  8. CREATE PROCEDURE  
  9. CREATE TABLE  
  10. DROP DATABASE  
  11. DROP FUNCTION  
  12. DROP INDEX  
  13. DROP PROCEDURE  
  14. DROP TABLE  
  15. UNLOCK TABLES  
  16. LOAD MASTER DATA  
  17. LOCK TABLES  
  18. RENAME TABLE  
  19. TRUNCATE TABLE  
  20. SET AUTOCOMMIT=1   
  21. START TRANSACTION   
ALTER FUNCTION
ALTER PROCEDURE
ALTER TABLE
BEGIN
CREATE DATABASE
CREATE FUNCTION
CREATE INDEX
CREATE PROCEDURE
CREATE TABLE
DROP DATABASE
DROP FUNCTION
DROP INDEX
DROP PROCEDURE
DROP TABLE
UNLOCK TABLES
LOAD MASTER DATA
LOCK TABLES
RENAME TABLE
TRUNCATE TABLE
SET AUTOCOMMIT=1
START TRANSACTION



3，使用Savepoint
使用savepoint回滚难免有些性能消耗，一般可以用IF改写
savepoint的良好使用的场景之一是“嵌套事务”，你可能希望程序执行一个小的事务，但是不希望回滚外面更大的事务：

Java代码
  1. CREATE PROCEDURE nested_tfer_funds  
  2.     (in_from_acct   INTEGER,  
  3.      in_to_acct     INTEGER,  
  4.      in_tfer_amount DECIMAL(8 , 2 ))  
  5. BEGIN  
  6.     DECLARE txn_error INTEGER DEFAULT 0 ;  
  7.   
  8.     DECLARE CONTINUE HANDLER FOR SQLEXCEPTION BEGIN  
  9.         SET txn_error=1 ;  
  10.     END  
  11.   
  12.     SAVEPINT savepint_tfer;  
  13.   
  14.     UPDATE account_balance  
  15.        SET balance=balance-in_tfer_amount  
  16.      WHERE account_id=in_from_acct;  
  17.   
  18.     IF txn_error THEN  
  19.         ROLLBACK TO savepoint_tfer;  
  20.         SELECT 'Transfer aborted' ;  
  21.     ELSE  
  22.         UPDATE account_balance  
  23.            SET balance=balance+in_tfer_amount  
  24.          WHERE account_id=in_to_acct;  
  25.   
  26.         IF txn_error THEN  
  27.             ROLLBACK TO savepoint_tfer;  
  28.             SELECT 'Transfer aborted' ;  
  29.   
  30.         END IF:  
  31.     END IF;  
  32. END;   
CREATE PROCEDURE nested_tfer_funds
    (in_from_acct   INTEGER,
     in_to_acct     INTEGER,
     in_tfer_amount DECIMAL(8,2))
BEGIN
    DECLARE txn_error INTEGER DEFAULT 0;

    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION BEGIN
        SET txn_error=1;
    END

    SAVEPINT savepint_tfer;

    UPDATE account_balance
       SET balance=balance-in_tfer_amount
     WHERE account_id=in_from_acct;

    IF txn_error THEN
        ROLLBACK TO savepoint_tfer;
        SELECT 'Transfer aborted';
    ELSE
        UPDATE account_balance
           SET balance=balance+in_tfer_amount
         WHERE account_id=in_to_acct;

        IF txn_error THEN
            ROLLBACK TO savepoint_tfer;
            SELECT 'Transfer aborted';

        END IF:
    END IF;
END;



4，事务和锁
事务的ACID属性只能通过限制数据库的同步更改来实现，从而通过对修改数据加锁来实现。
直到事务触发COMMIT或ROLLBACK语句时锁才释放。
缺点是后面的事务必须等前面的事务完成才能开始执行，吞吐量随着等待锁释放的时间增长而递减。
MySQL/InnoDB通过行级锁来最小化锁竞争。这样修改同一table里其他行的数据没有限制，而且读数据可以始终没有等待。
可以在SELECT语句里使用FOR UPDATE或LOCK IN SHARE MODE语句来加上行级锁

Java代码
  1. SELECT select_statement options [FOR UPDATE|LOCK IN SHARE MODE]   
SELECT select_statement options [FOR UPDATE|LOCK IN SHARE MODE]


FOR UPDATE会锁住该SELECT语句返回的行，其他SELECT和DML语句必须等待该SELECT语句所在的事务完成
LOCK IN SHARE MODE同FOR UPDATE，但是允许其他session的SELECT语句执行并允许获取SHARE MODE锁

死锁：
死锁发生于两个事务相互等待彼此释放锁的情景
当MySQL/InnoDB检查到死锁时，它会强制一个事务rollback并触发一条错误消息
对InnoDB而言，所选择的rollback的事务是完成工作最少的事务（所修改的行最少）

Java代码
  1. mysql > CALL tfer_funds( 1 , 2 , 300 );  
  2. ERROR 1213  ( 40001 ): Deadlock found when trying to get lock;  try  restarting transaction   
mysql > CALL tfer_funds(1,2,300);
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction


死锁在任何数据库系统里都可能发生，但是对MySQL/InnoDB这种行级锁数据库而言可能性相对较少。
可以通过使用一致的顺序来锁row或table以及让事务保持尽可能短来减少死锁的频率。
如果死锁不容易debug，你可以向你的程序中添加一些逻辑来处理死锁并重试事务，但这部分代码多了以后很难维护
所以，比较好的避免死锁的方式是在做任何修改之前按一定的顺序添加行级锁，这样就能避免死锁:

Java代码
  1. CREATE PROCEDURE tfer_funds3  
  2.     (from_account INT, to_account INT, tfer_amount NUMERIC(10 , 2 ))  
  3. BEGIN  
  4.     DECLARE local_account_id INT;  
  5.     DECLARE lock_cursor CURSOR FOR  
  6.         SELECT account_id  
  7.           FROM account_balance  
  8.          WHERE account_id IN (from_account, to_account)  
  9.          ORDER BY account_id  
  10.            FOR UPDATE;  
  11.   
  12.     START TRANSACTION;  
  13.   
  14.     OPEN lock_cursor;  
  15.     FETCH lock_cursor INTO local_account_id;  
  16.   
  17.     UPDATE account_balance  
  18.        SET balance=balance-tfer_amount  
  19.      WHERE account_id=from_account;  
  20.   
  21.     UPDATE account_balance  
  22.        SET balance=balance+tfer_amount  
  23.      WHERE account_id=to_account;  
  24.   
  25.     CLOSE lock_cursor;  
  26.   
  27.     COMMIT;  
  28. END;   
CREATE PROCEDURE tfer_funds3
    (from_account INT, to_account INT, tfer_amount NUMERIC(10,2))
BEGIN
    DECLARE local_account_id INT;
    DECLARE lock_cursor CURSOR FOR
        SELECT account_id
          FROM account_balance
         WHERE account_id IN (from_account, to_account)
         ORDER BY account_id
           FOR UPDATE;

    START TRANSACTION;

    OPEN lock_cursor;
    FETCH lock_cursor INTO local_account_id;

    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;

    CLOSE lock_cursor;

    COMMIT;
END;



设置死锁ttl: innodb_lock_wait_timeout，默认为50秒
如果你在一个事务中混合使用InnoDB和非InnoDB表，则MySQL不能检测到死锁，此时会抛出“lock wait timeuot”1205错误

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

Java代码
  1. CREATE PROCEDURE tfer_funds  
  2.        (from_account INT, to_account INT,tfer_amount NUMERIC(10 , 2 ),  
  3.         OUT status INT, OUT message VARCHAR(30 ))  
  4. BEGIN  
  5.     DECLARE from_account_balance NUMERIC(10 , 2 );  
  6.   
  7.     START TRANSACTION;  
  8.   
  9.   
  10.     SELECT balance  
  11.       INTO from_account_balance  
  12.       FROM account_balance  
  13.      WHERE account_id=from_account  
  14.        FOR UPDATE;  
  15.   
  16.     IF from_account_balance>=tfer_amount THEN  
  17.   
  18.          UPDATE account_balance  
  19.             SET balance=balance-tfer_amount  
  20.           WHERE account_id=from_account;  
  21.   
  22.          UPDATE account_balance  
  23.             SET balance=balance+tfer_amount  
  24.           WHERE account_id=to_account;  
  25.          COMMIT;  
  26.   
  27.          SET status=0 ;  
  28.          SET message='OK' ;  
  29.     ELSE  
  30.          ROLLBACK;  
  31.          SET status=-1 ;  
  32.          SET message='Insufficient funds' ;  
  33.     END IF;  
  34. 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(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;


乐观锁的例子：

Java代码
  1. CREATE PROCEDURE tfer_funds  
  2.     (from_account INT, to_account INT, tfer_amount NUMERIC(10 , 2 ),  
  3.         OUT status INT, OUT message VARCHAR(30 ) )  
  4.   
  5. BEGIN  
  6.   
  7.     DECLARE from_account_balance    NUMERIC(8 , 2 );  
  8.     DECLARE from_account_balance2   NUMERIC(8 , 2 );  
  9.     DECLARE from_account_timestamp1 TIMESTAMP;  
  10.     DECLARE from_account_timestamp2 TIMESTAMP;  
  11.   
  12.     SELECT account_timestamp,balance  
  13.         INTO from_account_timestamp1,from_account_balance  
  14.             FROM account_balance  
  15.             WHERE account_id=from_account;  
  16.   
  17.     IF (from_account_balance>=tfer_amount) THEN  
  18.   
  19.         -- Here we perform some long  running validation that  
  20.         -- might take a few minutes */  
  21.         CALL long_running_validation(from_account);  
  22.   
  23.         START TRANSACTION;  
  24.   
  25.         -- Make sure the account row has not been updated since  
  26.         -- our initial check  
  27.         SELECT account_timestamp, balance  
  28.             INTO from_account_timestamp2,from_account_balance2  
  29.             FROM account_balance  
  30.             WHERE account_id=from_account  
  31.             FOR UPDATE;  
  32.   
  33.         IF (from_account_timestamp1 <> from_account_timestamp2 OR  
  34.             from_account_balance    <> from_account_balance2)  THEN  
  35.             ROLLBACK;  
  36.             SET status=-1 ;  
  37.             SET message=CONCAT("Transaction cancelled due to concurrent update" ,  
  38.                 " of account"   ,from_account);  
  39.         ELSE  
  40.             UPDATE account_balance  
  41.                 SET balance=balance-tfer_amount  
  42.                 WHERE account_id=from_account;  
  43.   
  44.             UPDATE account_balance  
  45.                 SET balance=balance+tfer_amount  
  46.                 WHERE account_id=to_account;  
  47.   
  48.             COMMIT;  
  49.   
  50.             SET status=0 ;  
  51.             SET message="OK" ;  
  52.         END IF;  
  53.   
  54.     ELSE  
  55.         ROLLBACK;  
  56.         SET status=-1 ;  
  57.         SET message="Insufficient funds" ;  
  58.     END IF;  
  59. 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$$



5，事务设计指南


  1. 保持事务短小  
  2. 尽量避免事务中rollback  
  3. 尽量避免savepoint  
  4. 默认情况下，依赖于悲观锁  
  5. 为吞吐量要求苛刻的事务考虑乐观锁  
  6. 显示声明打开事务  
  7. 锁的行越少越好，锁的时间越短越好 
分享到：
| Java编程题汇总备忘
评论
1 楼 黄进宝与wys 2015-01-13  
发表评论

您还没有登录,请您登录后再发表评论

相关推荐

    mysql事务处理用法与实例详解

    ### MySQL事务处理用法与实例详解 #### 一、事务的概念及重要性 在数据库管理中，事务（Transaction）是指一系列作为一个整体的操作序列。这些操作要么全部成功，要么全部失败，不能只执行其中的一部分。事务处理...

    nodejs + mysql 事务处理问题

    nodejs + mysql 事物处理问题 呵呵 看了就知道 记得npm install啊 需要的东西啊 本想免费 苦于没积分 ，需要的的同学，给点分吧

    Java实现的mysql事务处理操作示例

    "Java实现的mysql事务处理操作示例" 本文主要介绍了Java实现的mysql事务处理操作，结合实例形式较为详细的分析了Java基于JDBC操作mysql数据库实现事务处理的相关概念、操作技巧与注意事项。 事务处理是数据库操作...

    mySQL事务处理

    关于mysql的事务处理 public static void StartTransaction(Connection con, String[] sqls) throws Exception { if (sqls == null) { return; } Statement sm = null; try { // 事务开始 System....

    MySql事务处理.docx

    MySQL 事务处理是数据库管理系统中的关键特性，主要用于确保数据操作的安全性和一致性。在MySQL中，事务主要应用于支持事务的存储引擎，如InnoDB或BDB。这些存储引擎能够提供事务的四大特性：原子性（Atomicity）、...

    mysql 事务处理学习资料+示例说明

    根据提供的文件信息，本文将详细解释MySQL事务处理的相关知识点，并结合具体的示例代码进行说明。 ### MySQL事务处理基础 在数据库操作中，事务处理是非常重要的一个环节，它能够确保数据的一致性和完整性。MySQL...

    MySQL事务处理详细讲解及完整实例

    本详细讲解将深入探讨MySQL事务处理的各个方面，并通过实例帮助你更好地理解和应用。 一、事务的基本特性 事务具有四大特性，也被称为ACID属性： 1. 原子性（Atomicity）：事务中的所有操作要么全部完成，要么全部...

    Mysql事务处理详解

    MySQL事务处理详解主要涵盖事务的基本概念、特性以及事务隔离级别，这些都是数据库管理中至关重要的概念。MySQL中的事务主要用于处理大量且复杂的操作，确保数据的一致性和完整性。 首先，事务是MySQL数据库操作的...

    MySQL事务处理与并发访问.pdf

    MySQL事务处理与并发访问.pdf

    php实现mysql事务处理的方法

    本文实例讲述了php实现mysql事务处理的方法。分享给大家供大家参考。具体分析如下： 要实现本功能的条件是环境 mysql 5.2 /php 5 支持事务的table 类型,需要InnoDB,有了这些条件你就可以做上面的实现了,这个事物回滚...

    MySql事务处理.pdf

    MySQL中的事务处理是数据库管理的重要组成部分，主要目的是确保数据的一致性和完整性。事务是一组SQL语句，它们作为一个单元执行，要么全部成功，要么全部回滚，这确保了原子性。事务处理的引入是为了解决多用户、多...

    mysql事务处理[归类].pdf

    MySQL的事务处理是数据库管理中的核心功能，尤其在软件开发中，确保数据一致性、安全性和可靠性至关重要。事务处理允许数据库操作作为一个不可分割的整体执行，确保数据的完整性和一致性。在人员管理系统等应用中，...

    php下pdo的mysql事务处理用法实例

    接下来，我们来看PHP下PDO的MySQL事务处理实例。以下是几个关键步骤： 1. **关闭自动提交**：在开始事务之前，需要关闭自动提交模式。在PDO中，可以通过设置`PDO::ATTR_AUTOCOMMIT`属性为0来实现。例如： ```php ...

    MySQL事务处理与应用简析

    MySQL事务处理是数据库管理系统中的关键特性，特别是在需要确保数据完整性和一致性的商务级应用中。事务是一组数据库操作，它们被视为单个逻辑工作单元，必须全部成功执行或全部不执行。这种机制对于防止数据错误和...

    MySQL事务剖析1

    综上，MySQL事务处理是数据库系统中不可或缺的部分，它通过ACID特性、隔离级别、MVCC和锁机制确保数据的一致性，并在Java、Spring等开发环境中提供了便捷的事务管理接口。理解并合理使用这些概念和机制，对数据库...

Magicbox
Global site tag (gtag.js) - Google Analytics