MySQL 在储存过程中加事务 mysql存储过程事务写法

转载

mob64ca1404ed65 2024-07-28 10:43:52

文章标签 MySQL 在储存过程中加事务 mysql 存储过程事务 Java MySQL 死锁 文章分类 MySQL 数据库

MySQL存储过程之事务管理

ACID:Atomic、Consistent、Isolated、Durable

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

1，MySQL的事务支持

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

Java代码
1
MyISAM：不支持事务，用于只读程序提高性能
2
InnoDB：支持ACID事务、行级锁、并发
3
Berkeley DB：支持事务
隔离级别：
隔离级别决定了一个session中的事务可能对另一个session的影响、并发session对数据库的操作、一个session中所见数据的一致性
ANSI标准定义了4个隔离级别，MySQL的InnoDB都支持：
Java代码
4
READ UNCOMMITTED：最低级别的隔离，通常又称为dirty read，它允许一个事务读取还没commit的数据，这样可能会提高性能，但是dirty read可能不是我们想要的
5
READ COMMITTED：在一个事务中只允许已经commit的记录可见，如果session中select还在查询中，另一session此时insert一条记录，则新添加的数据不可见
6
REPEATABLE READ：在一个事务开始后，其他session对数据库的修改在本事务中不可见，直到本事务commit或rollback。在一个事务中重复select的结果一样，除非本事务中update数据库。
7
SERIALIZABLE：最高级别的隔离，只允许事务串行执行。为了达到此目的，数据库会锁住每行已经读取的记录，其他session不能修改数据直到前一事务结束，事务commit或取消时才释放锁。
可以使用如下语句设置MySQL的session隔离级别：
Java代码
8
SET TRANSACTION ISOLATION LEVEL {READ UNCOMMITTED | READ
COMMITTED | REPEATABLE READ | SERIALIZABLE}
MySQL默认的隔离级别是REPEATABLE READ，在设置隔离级别为READ UNCOMMITTED或SERIALIZABLE时要小心，READ UNCOMMITTED会导致数据完整性的严重问题，而SERIALIZABLE会导致性能问题并增加死锁的机率
事务管理语句：
Java代码
9
START TRANSACTION：开始事务，autocommit设为0，如果已经有一个事务在运行，则会触发一个隐藏的COMMIT
10
COMMIT：提交事务，保存更改，释放锁
11
ROLLBACK：回滚本事务对数据库的所有更改，然后结束事务，释放锁
12
SAVEPOINT savepoint_name：创建一个savepoint识别符来ROLLBACK TO SAVEPOINT
13
ROLLBACK TO SAVEPOINT savepoint_name：回滚到从savepoint_name开始对数据库的所有更改，这样就允许回滚事务中的一部分，保证更改的一个子集被提交
14
SET TRANSACTION：允许设置事务的隔离级别
15
LOCK TABLES：允许显式的锁住一个或多个table，会隐式的关闭当前打开的事务，建议在执行LOCK TABLES语句之前显式的commit或rollback。我们一般所以一般在事务代码里不会使用LOCK TABLES
2，定义事务
MySQL默认的行为是在每条SQL语句执行后执行一个COMMIT语句，从而有效的将每条语句独立为一个事务。
在复杂的应用场景下这种方式就不能满足需求了。
为了打开事务，允许在COMMIT和ROLLBACK之前多条语句被执行，我们需要做以下两步：
1, 设置MySQL的autocommit属性为0，默认为1
2，使用START TRANSACTION语句显式的打开一个事务
如果已经打开一个事务，则SET autocommit=0不会起作用，因为START TRANSACTION会隐式的提交session中所有当前的更改，结束已有的事务，并打开一个新的事务。
使用SET AUTOCOMMIT语句的存储过程例子：
Java代码
16
CREATE PROCEDURE tfer_funds
17
(from_account int, to_account int, tfer_amount numeric(10,2))
18
BEGIN
19
SET autocommit=0;
20
21
UPDATE account_balance
SET balance=balance-tfer_amount WHERE account_id=from_account;
22
23
UPDATE
account_balance SET balance=balance+tfer_amount WHERE
account_id=to_account;
24
25
COMMIT;
26
END;
使用START TRANSACITON打开事务的例子：
Java代码
27
CREATE PROCEDURE tfer_funds
28
(from_account int, to_account int, tfer_amount numeric(10,2))
29
BEGIN
30
START
TRANSACTION;
31
32
UPDATE
account_balance SET balance=balance-tfer_amount WHERE
account_id=from_account;
33
34
UPDATE
account_balance SET balance=balance+tfer_amount WHERE
account_id=to_account;
35
36
COMMIT;
37
END;
通常COMMIT或ROLLBACK语句执行时才完成一个事务，但是有些DDL语句等会隐式触发COMMIT，所以应该在事务中尽可能少用或注意一下：
Java代码
38
ALTER FUNCTION
39
ALTER PROCEDURE
40
ALTER TABLE
41
BEGIN
42
CREATE DATABASE
43
CREATE FUNCTION
44
CREATE INDEX
45
CREATE PROCEDURE
46
CREATE TABLE
47
DROP DATABASE
48
DROP FUNCTION
49
DROP INDEX
50
DROP PROCEDURE
51
DROP TABLE
52
UNLOCK TABLES
53
LOAD MASTER DATA
54
LOCK TABLES
55
RENAME TABLE
56
TRUNCATE TABLE
57
SET AUTOCOMMIT=1
58
START TRANSACTION
3，使用Savepoint
使用savepoint回滚难免有些性能消耗，一般可以用IF改写
savepoint的良好使用的场景之一是“嵌套事务”，你可能希望程序执行一个小的事务，但是不希望回滚外面更大的事务：
Java代码
59
CREATE PROCEDURE nested_tfer_funds
60
(in_from_acct   INTEGER,
61
in_to_acct     INTEGER,
62
in_tfer_amount
DECIMAL(8,2))
63
BEGIN
64
DECLARE txn_error
INTEGER DEFAULT 0;
65
66
DECLARE CONTINUE
HANDLER FOR SQLEXCEPTION BEGIN
67
SET
txn_error=1;
68
END
69
70
SAVEPINT
savepint_tfer;
71
72
UPDATE
account_balance
73
SET
balance=balance-in_tfer_amount
74
WHERE
account_id=in_from_acct;
75
76
IF txn_error
THEN
77
ROLLBACK TO
savepoint_tfer;
78
SELECT 'Transfer aborted';
79
ELSE
80
UPDATE
account_balance
81
SET
balance=balance+in_tfer_amount
82
WHERE
account_id=in_to_acct;
83
84
IF txn_error
THEN
85
ROLLBACK
TO savepoint_tfer;
86
SELECT 'Transfer aborted';
87
88
END IF:
89
END IF;
90
END;
4，事务和锁
事务的ACID属性只能通过限制数据库的同步更改来实现，从而通过对修改数据加锁来实现。
直到事务触发COMMIT或ROLLBACK语句时锁才释放。
缺点是后面的事务必须等前面的事务完成才能开始执行，吞吐量随着等待锁释放的时间增长而递减。
MySQL/InnoDB通过行级锁来最小化锁竞争。这样修改同一table里其他行的数据没有限制，而且读数据可以始终没有等待。
可以在SELECT语句里使用FOR
UPDATE或LOCK IN SHARE MODE语句来加上行级锁
Java代码
91
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代码
92
mysql > CALL tfer_funds(1,2,300);
93
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
死锁在任何数据库系统里都可能发生，但是对MySQL/InnoDB这种行级锁数据库而言可能性相对较少。
可以通过使用一致的顺序来锁row或table以及让事务保持尽可能短来减少死锁的频率。
如果死锁不容易debug，你可以向你的程序中添加一些逻辑来处理死锁并重试事务，但这部分代码多了以后很难维护
所以，比较好的避免死锁的方式是在做任何修改之前按一定的顺序添加行级锁，这样就能避免死锁:
Java代码
94
CREATE PROCEDURE tfer_funds3
95
(from_account
INT, to_account INT, tfer_amount NUMERIC(10,2))
96
BEGIN
97
DECLARE
local_account_id INT;
98
DECLARE
lock_cursor CURSOR FOR
99
SELECT
account_id
100
FROM
account_balance
101
WHERE
account_id IN (from_account, to_account)
102
ORDER BY
account_id
103
FOR
UPDATE;
104
105
START
TRANSACTION;
106
107
OPEN
lock_cursor;
108
FETCH lock_cursor
INTO local_account_id;
109
110
UPDATE account_balance
111
SET
balance=balance-tfer_amount
112
WHERE
account_id=from_account;
113
114
UPDATE
account_balance
115
SET
balance=balance+tfer_amount
116
WHERE
account_id=to_account;
117
118
CLOSE
lock_cursor;
119
120
COMMIT;
121
END;
设置死锁ttl: innodb_lock_wait_timeout，默认为50秒
如果你在一个事务中混合使用InnoDB和非InnoDB表，则MySQL不能检测到死锁，此时会抛出“lock wait timeuot”1205错误
乐观所和悲观锁策略：
悲观锁：在读取数据时锁住那几行，其他对这几行的更新需要等到悲观锁结束时才能继续
乐观所：读取数据时不锁，更新时检查是否数据已经被更新过，如果是则取消当前更新
一般在悲观锁的等待时间过长而不能接受时我们才会选择乐观锁
悲观锁的例子：
Java代码
122
CREATE PROCEDURE tfer_funds
123
(from_account
INT, to_account INT,tfer_amount NUMERIC(10,2),
124
OUT status
INT, OUT message VARCHAR(30))
125
BEGIN
126
DECLARE
from_account_balance NUMERIC(10,2);
127
128
START
TRANSACTION;
129
130
131
SELECT balance
132
INTO
from_account_balance
133
FROM
account_balance
134
WHERE
account_id=from_account
135
FOR
UPDATE;
136
137
IF
from_account_balance>=tfer_amount THEN
138
139
UPDATE
account_balance
140
SET
balance=balance-tfer_amount
141
WHERE
account_id=from_account;
142
143
UPDATE
account_balance
144
SET
balance=balance+tfer_amount
145
WHERE
account_id=to_account;
146
COMMIT;
147
148
SET status=0;
149
SET message='OK';
150
ELSE
151
ROLLBACK;
152
SET status=-1;
153
SET message='Insufficient funds';
154
END IF;
155
END;
乐观锁的例子：
Java代码
156
CREATE PROCEDURE tfer_funds
157
(from_account
INT, to_account INT, tfer_amount NUMERIC(10,2),
158
OUT status
INT, OUT message VARCHAR(30) )
159
160
BEGIN
161
162
DECLARE
from_account_balance    NUMERIC(8,2);
163
DECLARE
from_account_balance2   NUMERIC(8,2);
164
DECLARE
from_account_timestamp1 TIMESTAMP;
165
DECLARE
from_account_timestamp2 TIMESTAMP;
166
167
SELECT
account_timestamp,balance
168
INTO
from_account_timestamp1,from_account_balance
169
FROM
account_balance
170
WHERE
account_id=from_account;
171
172
IF
(from_account_balance>=tfer_amount) THEN
173
174
-- Here we
perform some long running validation that
175
-- might take
a few minutes */
176
CALL
long_running_validation(from_account);
177
178
START
TRANSACTION;
179
180
-- Make sure
the account row has not been updated since
181
-- our
initial check
182
SELECT
account_timestamp, balance
183
INTO
from_account_timestamp2,from_account_balance2
184
FROM
account_balance
185
WHERE
account_id=from_account
186
FOR
UPDATE;
187
188
IF
(from_account_timestamp1 <> from_account_timestamp2 OR
189
from_account_balance    <> from_account_balance2)  THEN
190
ROLLBACK;
191
SET
status=-1;
192
SET
message=CONCAT("Transaction
cancelled due to concurrent update",
193
" of account"  ,from_account);
194
ELSE
195
UPDATE
account_balance
196
SET
balance=balance-tfer_amount
197
WHERE
account_id=from_account;
198
199
UPDATE
account_balance
200
SET
balance=balance+tfer_amount
201
WHERE
account_id=to_account;
202
203
COMMIT;
204
205
SET
status=0;
206
SET
message="OK";
207
END IF;
208
209
ELSE
210
ROLLBACK;
211
SET status=-1;
212
SET message="Insufficient funds";
213
END IF;
214
END$$

5，事务设计指南

Java代码

215

1，保持事务短小

216

2，尽量避免事务中rollback

217

3，尽量避免savepoint

218

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

219

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