MySQL存储过程之事务管理.docx
《MySQL存储过程之事务管理.docx》由会员分享，可在线阅读，更多相关《MySQL存储过程之事务管理.docx（11页珍藏版）》请在冰豆网上搜索。
MySQL存储过程之事务管理
MySQL存储过程之事务管理
MySQL存储过程之事务管理
ACID:
Atomic、Consistent、Isolated、Durable
存储程序提供了一个绝佳的机制来定义、封装和管理事务。
1，MySQL的事务支持
MySQL的事务支持不是绑定在MySQL服务器本身，而是与存储引擎相关：
Java代码
1MyISAM：
不支持事务，用于只读程序提高性能
2InnoDB：
支持ACID事务、行级锁、并发
3BerkeleyDB：
支持事务
隔离级别：
隔离级别决定了一个session中的事务可能对另一个session的影响、并发session对数据库的操作、一个session中所见数据的一致性
ANSI标准定义了4个隔离级别，MySQL的InnoDB都支持：
Java代码
4READUNCOMMITTED：
最低级别的隔离，通常又称为dirtyread，它允许一个事务读取还没commit的数据，这样可能会提高性能，但是dirtyread可能不是我们想要的
5READCOMMITTED：
在一个事务中只允许已经commit的记录可见，如果session中select还在查询中，另一session此时insert一条记录，则新添加的数据不可见
6REPEATABLEREAD：
在一个事务开始后，其他session对数据库的修改在本事务中不可见，直到本事务commit或rollback。
在一个事务中重复select的结果一样，除非本事务中update数据库。
7SERIALIZABLE：
最高级别的隔离，只允许事务串行执行。
为了达到此目的，数据库会锁住每行已经读取的记录，其他session不能修改数据直到前一事务结束，事务commit或取消时才释放锁。
可以使用如下语句设置MySQL的session隔离级别：
Java代码
8SETTRANSACTIONISOLATIONLEVEL{READUNCOMMITTED|READCOMMITTED|REPEATABLEREAD|SERIALIZABLE}
MySQL默认的隔离级别是REPEATABLEREAD，在设置隔离级别为READUNCOMMITTED或SERIALIZABLE时要小心，READUNCOMMITTED会导致数据完整性的严重问题，而SERIALIZABLE会导致性能问题并增加死锁的机率
事务管理语句：
Java代码
9STARTTRANSACTION：
开始事务，autocommit设为0，如果已经有一个事务在运行，则会触发一个隐藏的COMMIT
10COMMIT：
提交事务，保存更改，释放锁
11ROLLBACK：
回滚本事务对数据库的所有更改，然后结束事务，释放锁
12SAVEPOINTsavepoint_name：
创建一个savepoint识别符来ROLLBACKTOSAVEPOINT
13ROLLBACKTOSAVEPOINTsavepoint_name：
回滚到从savepoint_name开始对数据库的所有更改，这样就允许回滚事务中的一部分，保证更改的一个子集被提交
14SETTRANSACTION：
允许设置事务的隔离级别
15LOCKTABLES：
允许显式的锁住一个或多个table，会隐式的关闭当前打开的事务，建议在执行LOCKTABLES语句之前显式的commit或rollback。
我们一般所以一般在事务代码里不会使用LOCKTABLES
2，定义事务
MySQL默认的行为是在每条SQL语句执行后执行一个COMMIT语句，从而有效的将每条语句独立为一个事务。
在复杂的应用场景下这种方式就不能满足需求了。
为了打开事务，允许在COMMIT和ROLLBACK之前多条语句被执行，我们需要做以下两步：
1,设置MySQL的autocommit属性为0，默认为1
2，使用STARTTRANSACTION语句显式的打开一个事务
如果已经打开一个事务，则SETautocommit=0不会起作用，因为STARTTRANSACTION会隐式的提交session中所有当前的更改，结束已有的事务，并打开一个新的事务。
使用SETAUTOCOMMIT语句的存储过程例子：
Java代码
16CREATEPROCEDUREtfer_funds
17（from_accountint,to_accountint,tfer_amountnumeric（10,2））
18BEGIN
19SETautocommit=0;
20
21UPDATEaccount_balanceSETbalance=balance-tfer_amountWHEREaccount_id=from_account;
22
23UPDATEaccount_balanceSETbalance=balance+tfer_amountWHEREaccount_id=to_account;
24
25COMMIT;
26END;
使用STARTTRANSACITON打开事务的例子：
Java代码
27CREATEPROCEDUREtfer_funds
28（from_accountint,to_accountint,tfer_amountnumeric（10,2））
29BEGIN
30STARTTRANSACTION;
31
32UPDATEaccount_balanceSETbalance=balance-tfer_amountWHEREaccount_id=from_account;
33
34UPDATEaccount_balanceSETbalance=balance+tfer_amountWHEREaccount_id=to_account;
35
36COMMIT;
37END;
通常COMMIT或ROLLBACK语句执行时才完成一个事务，但是有些DDL语句等会隐式触发COMMIT，所以应该在事务中尽可能少用或注意一下：
Java代码
38ALTERFUNCTION
39ALTERPROCEDURE
40ALTERTABLE
41BEGIN
42CREATEDATABASE
43CREATEFUNCTION
44CREATEINDEX
45CREATEPROCEDURE
46CREATETABLE
47DROPDATABASE
48DROPFUNCTION
49DROPINDEX
50DROPPROCEDURE
51DROPTABLE
52UNLOCKTABLES
53LOADMASTERDATA
54LOCKTABLES
55RENAMETABLE
56TRUNCATETABLE
57SETAUTOCOMMIT=1
58STARTTRANSACTION
3，使用Savepoint
使用savepoint回滚难免有些性能消耗，一般可以用IF改写
savepoint的良好使用的场景之一是“嵌套事务”，你可能希望程序执行一个小的事务，但是不希望回滚外面更大的事务：
Java代码
59CREATEPROCEDUREnested_tfer_funds
60（in_from_acctINTEGER,
61in_to_acctINTEGER,
62in_tfer_amountDECIMAL（8,2））
63BEGIN
64DECLAREtxn_errorINTEGERDEFAULT0;
65
66DECLARECONTINUEHANDLERFORSQLEXCEPTIONBEGIN
67SETtxn_error=1;
68END
69
70SAVEPINTsavepint_tfer;
71
72UPDATEaccount_balance
73SETbalance=balance-in_tfer_amount
74WHEREaccount_id=in_from_acct;
75
76IFtxn_errorTHEN
77ROLLBACKTOsavepoint_tfer;
78SELECT'Transferaborted';
79ELSE
80UPDATEaccount_balance
81SETbalance=balance+in_tfer_amount
82WHEREaccount_id=in_to_acct;
83
84IFtxn_errorTHEN
85ROLLBACKTOsavepoint_tfer;
86SELECT'Transferaborted';
87
88ENDIF:
89ENDIF;
90END;
4，事务和锁
事务的ACID属性只能通过限制数据库的同步更改来实现，从而通过对修改数据加锁来实现。
直到事务触发COMMIT或ROLLBACK语句时锁才释放。
缺点是后面的事务必须等前面的事务完成才能开始执行，吞吐量随着等待锁释放的时间增长而递减。
MySQL/InnoDB通过行级锁来最小化锁竞争。
这样修改同一table里其他行的数据没有限制，而且读数据可以始终没有等待。
可以在SELECT语句里使用FORUPDATE或LOCKINSHAREMODE语句来加上行级锁
Java代码
91SELECTselect_statementoptions[FORUPDATE|LOCKINSHAREMODE]
FORUPDATE会锁住该SELECT语句返回的行，其他SELECT和DML语句必须等待该SELECT语句所在的事务完成
LOCKINSHAREMODE同FORUPDATE，但是允许其他session的SELECT语句执行并允许获取SHAREMODE锁
死锁：
死锁发生于两个事务相互等待彼此释放锁的情景
当MySQL/InnoDB检查到死锁时，它会强制一个事务rollback并触发一条错误消息
对InnoDB而言，所选择的rollback的事务是完成工作最少的事务（所修改的行最少）
Java代码
92mysql>CALLtfer_funds（1,2,300）;
93ERROR1213（40001）:
Deadlockfoundwhentryingtogetlock;tryrestartingtransaction
死锁在任何数据库系统里都可能发生，但是对MySQL/InnoDB这种行级锁数据库而言可能性相对较少。
可以通过使用一致的顺序来锁row或table以及让事务保持尽可能短来减少死锁的频率。
如果死锁不容易debug，你可以向你的程序中添加一些逻辑来处理死锁并重试事务，但这部分代码多了以后很难维护
所以，比较好的避免死锁的方式是在做任何修改之前按一定的顺序添加行级锁，这样就能避免死锁:
Java代码
94CREATEPROCEDUREtfer_funds3
95（from_accountINT,to_accountINT,tfer_amountNUMERIC（10,2））
96BEGIN
97DECLARElocal_account_idINT;
98DECLARElock_cursorCURSORFOR
99SELECTaccount_id
100FROMaccount_balance
101WHEREaccount_idIN（from_account,to_account）
102ORDERBYaccount_id
103FORUPDATE;
104
105STARTTRANSACTION;
106
107OPENlock_cursor;
108FETCHlock_cursorINTOlocal_account_id;
109
110UPDATEaccount_balance
111SETbalance=balance-tfer_amount
112WHEREaccount_id=from_account;
113
114UPDATEaccount_balance
115SETbalance=balance+tfer_amount
116WHEREaccount_id=to_account;
117
118CLOSElock_cursor;
119
120COMMIT;
121END;
设置死锁ttl:
innodb_lock_wait_timeout，默认为50秒
如果你在一个事务中混合使用InnoDB和非InnoDB表，则MySQL不能检测到死锁，此时会抛出“lockwaittimeuot”1205错误
乐观所和悲观锁策略：
悲观锁：
在读取数据时锁住那几行，其他对这几行的更新需要等到悲观锁结束时才能继续
乐观所：
读取数据时不锁，更新时检查是否数据已经被更新过，如果是则取消当前更新
一般在悲观锁的等待时间过长而不能接受时我们才会选择乐观锁
悲观锁的例子：
Java代码
122CREATEPROCEDUREtfer_funds
123（from_accountINT,to_accountINT,tfer_amountNUMERIC（10,2）,
124OUTstatusINT,OUTmessageVARCHAR（30））
125BEGIN
126DECLAREfrom_account_balanceNUMERIC（10,2）;
127
128STARTTRANSACTION;
129
130
131SELECTbalance
132INTOfrom_account_balance
133FROMaccount_balance
134WHEREaccount_id=from_account
135FORUPDATE;
136
137IFfrom_account_balance>=tfer_amountTHEN
138
139UPDATEaccount_balance
140SETbalance=balance-tfer_amount
141WHEREaccount_id=from_account;
142
143UPDATEaccount_balance
144SETbalance=balance+tfer_amount
145WHEREaccount_id=to_account;
146COMMIT;
147
148SETstatus=0;
149SETmessage='OK';
150ELSE
151ROLLBACK;
152SETstatus=-1;
153SETmessage='Insufficientfunds';
154ENDIF;
155END;
乐观锁的例子：
Java代码
156CREATEPROCEDUREtfer_funds
157（from_accountINT,to_accountINT,tfer_amountNUMERIC（10,2）,
158OUTstatusINT,OUTmessageVARCHAR（30））
159
160BEGIN
161
162DECLAREfrom_account_balanceNUMERIC（8,2）;
163DECLAREfrom_account_balance2NUMERIC（8,2）;
164DECLAREfrom_account_timestamp1TIMESTAMP;
165DECLAREfrom_account_timestamp2TIMESTAMP;
166
167SELECTaccount_timestamp,balance
168INTOfrom_account_timestamp1,from_account_balance
169FROMaccount_balance
170WHEREaccount_id=from_account;
171
172IF（from_account_balance>=tfer_amount）THEN
173
174--Hereweperformsomelongrunningvalidationthat
175--mighttakeafewminutes*/
176CALLlong_running_validation（from_account）;
177
178STARTTRANSACTION;
179
180--Makesuretheaccountrowhasnotbeenupdatedsince
181--ourinitialcheck
182SELECTaccount_timestamp,balance
183INTOfrom_account_timestamp2,from_account_balance2
184FROMaccount_balance
185WHEREaccount_id=from_account
186FORUPDATE;
187
188IF（from_account_timestamp1<>from_account_timestamp2OR
189from_account_balance<>from_account_balance2）THEN
190ROLLBACK;
191SETstatus=-1;
192SETmessage=CONCAT（"Transactioncancelledduetoconcurrentupdate",
193"ofaccount",from_account）;
194ELSE
195UPDATEaccount_balance
196SETbalance=balance-tfer_amount
197WHEREaccount_id=from_account;
198
199UPDATEaccount_balance
200SETbalance=balance+tfer_amount
201WHEREaccount_id=to_account;
202
203COMMIT;
204
205SETstatus=0;
206SETmessage="OK";
207ENDIF;
208
209ELSE
210ROLLBACK;
211SETstatus=-1;
212SETmessage="Insufficientfunds";
213ENDIF;
214END$$
5，事务设计指南
Java代码
2151，保持事务短小
2162，尽量避免事务中rollback
2173，尽量避免savepoint
2184，默认情况下，依赖于悲观锁
2195，为吞吐量要求苛刻的事务考虑乐观锁
2206，显示声明打开事务
2217，锁的行越少越好，锁的时间越短越好
升级会员 