`
dove19900520
  • 浏览: 592008 次
  • 性别: Icon_minigender_1
  • 来自: 北京
社区版块
存档分类
最新评论

mysql存储过程中的事务管理

阅读更多

MySQL存储过程之事务管理 

ACID:Atomic、Consistent、Isolated、Durable 
存储程序提供了一个绝佳的机制来定义、封装和管理事务。 

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

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

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

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隔离级别: 

set transaction isolation level {read uncommitted | read committed | repeatable read | serializable}

 

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

3)事务管理语句: 

START TRANSACTION:开始事务,autocommit设为0,如果已经有一个事务在运行,则会触发一个隐藏的COMMIT  
COMMIT:提交事务,保存更改,释放锁  
ROLLBACK:回滚本事务对数据库的所有更改,然后结束事务,释放锁  
SAVEPOINT savepoint_name:创建一个savepoint识别符来ROLLBACK TO SAVEPOINT  
ROLLBACK TO SAVEPOINT savepoint_name:回滚到从savepoint_name开始对数据库的所有更改,这样就允许回滚事务中的一部分,保证更改的一个子集被提交  
SET TRANSACTION:允许设置事务的隔离级别  
LOCK TABLES:允许显式的锁住一个或多个table,会隐式的关闭当前打开的事务,建议在执行LOCK TABLES语句之前显式的commit或rollback。我们一般所以一般在事务代码里不会使用LOCK TABLES  

 

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

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

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

delimiter $$
use test$$
create procedure t_insert_table()
begin
	/** 标记是否出错 */
	declare t_error int default 0;
	/** 如果出现sql异常,则将t_error设置为1后继续执行后面的操作 */
	declare continue handler for sqlexception set t_error=1; -- 出错处理
	/** 显示的开启事务,启动它后,autocommit值会自动设置为0 */
	start transaction;
	insert into t_bom_test(parent_id,child_id) values('C','XXXX');
	insert into t_trigger_test(name,age) values('zhangsan',34);
	/** 标记被改变,表示事务应该回滚 */
	if t_error=1 then
		rollback; -- 事务回滚
	else
		commit; -- 事务提交
	end if;
end$$
delimiter ;

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

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的良好使用的场景之一是“嵌套事务”,你可能希望程序执行一个小的事务,但是不希望回滚外面更大的事务: 

 

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语句来加上行级锁 

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



设置死锁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;  


乐观锁的例子: 

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



5,事务设计指南 

Java代码 
  1. 1,保持事务短小  
  2. 2,尽量避免事务中rollback  
  3. 3,尽量避免savepoint  
  4. 4,默认情况下,依赖于悲观锁  
  5. 5,为吞吐量要求苛刻的事务考虑乐观锁  
  6. 6,显示声明打开事务  
  7. 7,锁的行越少越好,锁的时间越短越好  
分享到:
评论

相关推荐

    MySql存储过程与事务处理教学PPT

    什么情况适合用存储过程? 当多个用不同语言开发的应用程序或不同平台的应用程序需要去执行相同的数据库操作. (避免为各个程序都开发相同的功能) 安全性要求较高时,使用存储程序和...MySql存储过程与事务处理教学PPT

    MYSQL存储过程编程

    mysql存储过程编程教程:  MySQL 存储过程编程基础 -- 指南,基本语句,存储过程中的 SQL 和错误处理  创建 MySQL 存储过程程序 -- 事务处理,内建函数,存储过程函数和触发器  在应用程序中使用 MySQL 存储...

    mysql存储过程事务管理简析

    1,MySQL的事务支持 1)MySQL的事务支持不是绑定在MySQL服务器本身,而是与存储引擎相关: Sql代码 代码如下: MyISAM:不支持事务,用于只读程序提高性能 InnoDB:支持ACID事务、行级锁、并发 Berkeley DB:支持...

    MySQL SQL高级特性-存储过程-触发器-事务

    MySQL SQL高级特性-存储过程-触发器-事务,非常不错,感谢

    银行转帐存储过程

    模拟银行转帐过程的存储过程,实现登录,转帐的管理,可根据返回的信息判断错误,实现事务操作

    mysql索引、触发器、事务、存储过程说明

    mysql索引、触发器、事务、存储过程说明

    MySQL数据库存储过程和事务的区别讲解

    事务是保证多个SQL语句的原子型的...尤其对于较为复杂的逻辑,减少了网络流量之间的消耗,另外比较重要的一点是存储过程只在创造时进行编译,以后每次执行存储过程都不需再重新编译,而一般 SQL 语句每执行一次就编译一

    在Mysql存储过程中使用事务实例

    主要介绍了在Mysql存储过程中使用事务实例,需要的朋友可以参考下

    mysql 流水号 存储过程 附表结构

    mysql上的存储过程,测试通过。 事务中获取流水号, 可配置前缀,后缀,日期字串, 可设定流水号归零周期, 带表结构, 方便移植到其他结构数据库。

    MySQL存储过程例子(包含事务,输出参数,嵌套调用)

    MySQL存储过程例子,包含事务,输出参数,嵌套调用,学习mysql存储过程的朋友可以参考下。

    Mysql存储过程、游标、函数调用、事务处理、触发器代码示例

    Mysql存储过程、游标、函数调用、事务处理、触发器代码示例,可用作学习参考。

    第六章__MySQL存储过程.ppt

    2.当对数据库进行复杂操作时(如对多个表进行Update、Insert、Query、Delete时),可将此复杂操作用存储过程封装起来与数据库提供的事务处理结合一起使用。 3.存储过程可以重复使用,可减少数据库开发人员的工作量...

    mySQL事务处理

    或者 只可以通过存储过程来实现, 单行的锁定 BEGIN; SELECT book_number FROM book WHERE book_id = 123 FOR UPDATE; --这里for update , 以前用Oracle的时候也是有这个行锁 // ... UPDATE book SET book_number ...

    MySQL数据库:MySQL存储引擎.pptx

    InnoDB是事务型数据库的首选引擎,为MySQL提供了具有提交、回滚和崩溃恢复能力的事务安全(ACID兼容)存储引擎,其它存储引擎都是非事务安全表,支持行锁定和外键,MySQL5.5以后默认使用InnoDB存储引擎。 ;2.MyISAM

    MySQL技术内幕 InnoDB存储引擎.pdf

    最近在学习MySQL技术内幕 InnoDB存储引擎 第2版,整理了一些文档分享出来,同时也方便以后查看。若有不当之处,烦请批评指正。 1. MySQL体系结构和存储引擎 2. InnoDB存储引擎 2.1 InnoDB体系结构 2.2 ...

    mysql内核 innodb存储引擎

    接着以InnoDB的内部实现为切入点,逐一详细讲解了InnoDB存储引擎内部的各个功能模块,包括InnoDB存储引擎的体系结构、内存中的数据结构、基于InnoDB存储引擎的表和页的物理存储、索引与算法、文件、锁、事务、备份,...

Global site tag (gtag.js) - Google Analytics