如何使用mysql乐观锁和悲观锁,
悲观锁:
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;
乐观锁:
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$$
1.悲观锁的例子是不是SELECT balance
INTO from_account_balance
FROM account_balance
WHERE account_id=from_account
FOR UPDATE;
有个FOR UPDATE就锁住了。2.乐观锁是不是通过比较这几个值是否相等赖判断,这句 IF (from_account_timestamp1 <> from_account_timestamp2 OR
from_account_balance <> from_account_balance2)
如果是这样感觉很麻烦,有没有简单点的方法
3.update和delete语句后要不要加FOR UPDATE。
请高手指点一下,谢谢
悲观锁:
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;
乐观锁:
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$$
1.悲观锁的例子是不是SELECT balance
INTO from_account_balance
FROM account_balance
WHERE account_id=from_account
FOR UPDATE;
有个FOR UPDATE就锁住了。2.乐观锁是不是通过比较这几个值是否相等赖判断,这句 IF (from_account_timestamp1 <> from_account_timestamp2 OR
from_account_balance <> from_account_balance2)
如果是这样感觉很麻烦,有没有简单点的方法
3.update和delete语句后要不要加FOR UPDATE。
请高手指点一下,谢谢
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货