首页 / MYSQL / mysql存储过程之事务篇

mysql存储过程之事务篇

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了mysql存储过程之事务篇，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含8430字，纯文字阅读大概需要13分钟。

内容图文

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

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

sql代码1 

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

sql代码2

 1 create procedure t_insert_table() 
 2 begin 
 3     /** 标记是否出错 */ 
 4     declare t_error int default 0; 
 5     /** 如果出现sql异常，则将t_error设置为1后继续执行后面的操作 */ 
 6     declare continue handler for sqlexception set t_error=1; -- 出错处理 
 7     /** 显示的开启事务，启动它后，autocommit值会自动设置为0 */ 
 8     start transaction; 
 9         insert into t_bom_test(parent_id,child_id) values(‘C‘,‘XXXX‘); 
10         insert into t_trigger_test(name,age) values(‘zhangsan‘,34); 
11         /** 标记被改变,表示事务应该回滚 */ 
12     if t_error=1 then 
13         rollback; -- 事务回滚 
14     else 
15         commit; -- 事务提交 
16     end if; 
17 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

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

 1 CREATE PROCEDURE nested_tfer_funds(in_from_acct INTEGER, in_to_acct INTEGER, in_tfer_amount DECIMAL(8,2)) 
 2 BEGIN 
 3     DECLARE txn_error INTEGER DEFAULT 0; 
 4     DECLARE CONTINUE HANDLER FOR SQLEXCEPTION 
 5     BEGIN 
 6         SET txn_error=1;
 7     END 
 8     SAVEPINT savepint_tfer; 
 9     UPDATE account_balance SET balance=balance-in_tfer_amount WHERE account_id=in_from_acct; 
10     IF txn_error THEN 
11         ROLLBACK TO savepoint_tfer; 
12         SELECT ‘Transfer aborted‘; 
13     ELSE 
14         UPDATE account_balance SET balance=balance+in_tfer_amount WHERE account_id=in_to_acct; 
15         IF txn_error THEN 
16             ROLLBACK TO savepoint_tfer; 
17             SELECT ‘Transfer aborted‘; 
18         END IF: 
19     END IF; 
20 END;

 

 

 

 

 

事务和锁

事务的ACID属性只能通过限制数据库的同步更改来实现，通过对数据加锁来实现。 
直到事务触发COMMIT或ROLLBACK语句时锁才释放。
这样做的缺点是后面的事务必须等前面的事务完成才能开始执行，吞吐量随着等待锁释放的时间增长而递减。

 

Mysql的innodb通过行级锁来最小化锁竞争。这样修改同一table里其他行的数据没有限制，而且读数据可以始终没有等待。

 

可以在SELECT语句里使用FOR UPDATE或LOCK IN SHARE MODE语句来加上行级锁 

1. 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锁 

 

 

下面了解一下死锁，悲观锁，乐观锁，但是不深入掌握，当前只掌握概念

 

Sql代码   1.死锁发生于两个事务相互等待彼此释放锁的情景   当MySQL/InnoDB检查到死锁时，它会强制一个事务rollback并触发一条错误消息 
对InnoDB而言，所选择的rollback的事务是完成工作最少的事务（所修改的行最少） Sql代码 
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，你可以向你的程序中添加一些逻辑来处理死锁并重试事务，但这部分代码多了以后很难维护 
所以，比较好的避免死锁的方式是在做任何修改之前按一定的顺序添加行级锁，这样就能避免死锁: 

 1 CREATE PROCEDURE tfer_funds3 (from_account INT, to_account INT, tfer_amount NUMERIC(10,2)) 
 2 BEGIN 
 3     DECLARE local_account_id INT; 
 4     DECLARE lock_cursor CURSOR FOR SELECT account_id FROM account_balance WHERE account_id IN (from_account, to_account) 
 5         ORDER BY account_id FOR UPDATE; 
 6     START TRANSACTION; 
 7         OPEN lock_cursor; 
 8         FETCH lock_cursor INTO local_account_id; 
 9         UPDATE account_balance SET balance=balance-tfer_amount WHERE account_id=from_account; 
10         UPDATE account_balance SET balance=balance+tfer_amount WHERE account_id=to_account; 
11         CLOSE lock_cursor; 
12     COMMIT; 
13 END;

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

2.悲观锁：在读取数据时锁住那几行，其他对这几行的更新需要等到悲观锁结束时才能继续  

 1 CREATE PROCEDURE tfer_funds(from_account INT, to_account INT,tfer_amount NUMERIC(10,2), OUT status INT, OUT message VARCHAR(30)) 
 2 BEGIN 
 3     DECLARE from_account_balance NUMERIC(10,2); 
 4     START TRANSACTION; 
 5         insert INTO from_account_balance 
 6             SELECT balance FROM account_balance WHERE account_id=from_account FOR UPDATE; 
 7         IF from_account_balance>=tfer_amount THEN 
 8             UPDATE account_balance SET balance=balance-tfer_amount WHERE account_id=from_account; 
 9             UPDATE account_balance SET balance=balance+tfer_amount WHERE account_id=to_account; 
10             COMMIT; 
11             SET status=0; 
12             SET message=‘OK‘; 
13         ELSE 
14             ROLLBACK; 
15             SET status=-1; 
16             SET message=‘Insufficient funds‘; 
17         END IF; 
18 END;

 

3.乐观所：读取数据时不锁，更新时检查是否数据已经被更新过，如果是则取消当前更新  

 1 CREATE PROCEDURE tfer_funds(from_account INT, to_account INT, tfer_amount NUMERIC(10,2), OUT status INT, OUT message VARCHAR(30) ) 
 2 BEGIN 
 3     DECLARE from_account_balance NUMERIC(8,2); 
 4     DECLARE from_account_balance2 NUMERIC(8,2); 
 5     DECLARE from_account_timestamp1 TIMESTAMP; 
 6     DECLARE from_account_timestamp2 TIMESTAMP; 
 7     SELECT account_timestamp,balance INTO from_account_timestamp1,from_account_balance FROM account_balance WHERE account_id=from_account; 
 8     IF (from_account_balance>=tfer_amount) THEN 
 9         -- Here we perform some long running validation that 
10         -- might take a few minutes */ 
11         CALL long_running_validation(from_account); 
12         START TRANSACTION; 
13         -- Make sure the account row has not been updated since 
14         -- our initial check 
15         
16         insert INTO from_account_timestamp2,from_account_balance2 
17             SELECT account_timestamp, balance FROM account_balance WHERE account_id=from_account FOR UPDATE; 
18         IF (from_account_timestamp1 <> from_account_timestamp2 OR from_account_balance <> from_account_balance2) THEN 
19             ROLLBACK; 
20             SET status=-1; 
21             SET message=CONCAT("Transaction cancelled due to concurrent update", " of account" ,from_account); 
22         ELSE 
23             UPDATE account_balance SET balance=balance-tfer_amount WHERE account_id=from_account; 
24             UPDATE account_balance SET balance=balance+tfer_amount WHERE account_id=to_account; 
25             COMMIT; 
26             SET status=0; 
27             SET message="OK"; 
28         END IF; 
29     ELSE 
30         ROLLBACK; 
31         SET status=-1; 
32         SET message="Insufficient funds"; 
33     END IF; 
34 END

 

一般在悲观锁的等待时间过长而不能接受时我们才会选择乐观锁

 

事务设计指南

 

1，保持事务短小  

2，尽量避免事务中rollback  

3，尽量避免savepoint  

4，默认情况下，依赖于悲观锁  

5，为吞吐量要求苛刻的事务考虑乐观锁  

6，显示声明打开事务  

7，锁的行越少越好，锁的时间越短越好

 

mysql存储过程之事务篇

标签：完整性   添加行   时间   问题   lex   mysql   过程   etc   修改   

本文系统来源：http://www.cnblogs.com/silentmuh/p/7457700.html

内容总结

以上是互联网集市为您收集整理的mysql存储过程之事务篇全部内容，希望文章能够帮你解决mysql存储过程之事务篇所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端

扫描二维码推送至手机访问。

本文链接：https://qyyshop.com/info/489759.html

来源：【匿名】