關於MYSQL交易機制與鎖表問題

php mysql 交易

imagine10255 2015-02-02 14:59:05 ‧ 14968 瀏覽

分享至

各位好
請問MYSQL的交易機制啟動時，是否會自動鎖表或鎖ROW呢？
比如說現在有兩個使用者，同時進行

假設member id = 10 的 amount = 100

USER1:
1.交易開始
2.UPDATE member SET 'amount` = amount-10 WHERE id=10; //結果90
3.檢查不符合條件 (USER2 已經提交)
4.回滾 (因user2 已提交, 這時候回滾的amount是?)
5.交易結束

USER2:
1.交易開始 (USER1 先交易開始)
2.UPDATE member SET 'amount` = amount-10 WHERE id=10; //結果80
3.檢查符合條件
4.提交 (80)
5.交易結束

有這種可能性嗎?
還是當USER1交易開始跑到UPDATE步驟的時候，就會做 lock 不讓USER2 繼續的動作了呢？或是要再另外下一個 SELECT * FORM member WHERE id=10 for update 的 row lock 呢？

這部分不太懂...又擔心到時死結的問題

第一次發問，若問題說的不清楚請見諒，我再補充

看更多先前的討論...收起先前的討論...

海綿寶寶 iT邦大神 1 級 ‧ 2015-02-02 15:07:09 檢舉

大哉問
先做記號等大師回答

一級屠豬士 iT邦大師 1 級 ‧ 2015-02-02 15:32:46 檢舉

MySQL有 InnoDB, MyISAM, 不同儲存引擎.
另外你的第三步驟,是什麼???
既然user1就先做了,那為何user2會跑到前面???
還有死結跟這裡沒關係啊.....

葉承宇 (丁丁) Dean Yeh iT邦大師 4 級 ‧ 2015-02-02 15:36:07 檢舉

附上教學, 給您參考

官方教學
http://dev.mysql.com/doc/refman/5.0/en/commit.html
MySQL 交易功能 Transaction 整理 - XYZ的筆記本
http://xyz.cinc.biz/2013/05/mysql-transaction.html

賽門 iT邦超人 1 級 ‧ 2015-02-02 15:39:23 檢舉

為什麼兩個User都會要處理Id=10的事呢？
基本上，你可以不用考慮Deadlock的事，現在DB Engine已經很聰明了，會自動調整這種狀況，不致於造成Deadlock。
不然每來個交易就Deadlock，還能叫資料庫管理系統嗎？

imagine10255 iT邦新手 4 級 ‧ 2015-02-03 08:33:11 檢舉

為什麼兩個User都會要處理Id=10的事呢

因為想不到什麼比較簡單說明的例子...所以拿同樣處理同一個ID做舉例

因為在網路爬文有寫到要row lock要小心死結的問題
如.
user1:
時間1. select * from member where id='4' for update
時間2. select * from paylog where member_id='4' for update
等待...user2 解鎖 paylog 資料表

user2:
時間1. select * from paylog where member_id='4' for update
時間2. select * from member where id='4' for update
等待...user1 解鎖 member 資料表

就這樣互相等解鎖的問題....最後要重開機解決
這樣子的問題會發生嗎 ?

imagine10255 iT邦新手 4 級 ‧ 2015-02-03 08:36:39 檢舉

既然user1就先做了,那為何user2會跑到前面???

因為如果同時開始，或差距毫秒時間開始，若因為user1的中間還有較多執行程序，或因查詢某段SQL查詢較久，user2 應該有可能會超強步驟。(不同支程式的情況)

還有死結跟這裡沒關係啊.....

因為在網路爬文有寫到要row lock要小心死結的問題
如.
user1:
時間1. select * from member where id='4' for update
時間2. select * from paylog where member_id='4' for update
等待...user2 解鎖 paylog 資料表

user2:
時間1. select * from paylog where member_id='4' for update
時間2. select * from member where id='4' for update
等待...user1 解鎖 member 資料表

就這樣互相等解鎖的問題....最後要重開機解決
這樣子的問題會發生嗎 ?

imagine10255 iT邦新手 4 級 ‧ 2015-02-03 08:38:49 檢舉

MySQL 交易功能 Transaction 整理 - XYZ的筆記本

這個我有爬文過，謝謝...大致上有一點點概念...但有些地方覺得不確定
另外官方英文看不太懂...小弟不才

wiseguy iT邦超人 1 級 ‧ 2015-02-03 08:54:03 檢舉

依我的經驗，會發生 deadlock！雖然機會極低，但我遇到了。
通常是程式設計師的 transaction 包得太大、設計不良所致。
所以不是 DB 有 transaction 就不管三七二十一就給它從頭包到尾 (如同程式的 try catch 一樣)。transaction 要包在有 race condition 疑慮的最小區段。

imagine10255 iT邦新手 4 級 ‧ 2015-02-03 08:59:27 檢舉

通常是程式設計師的 transaction 包得太大、設計不良所致。

恩...因為我也擔心會發生這樣的錯誤...到時要找問題很難找
測試又不知道要從何測..才能測出相同狀況重現...因為要可能2~3個使用者同時執行或執行時間點不同才會發生

一級屠豬士 iT邦大師 1 級 ‧ 2015-02-03 09:00:16 檢舉

你不Commit是要幹麼???

imagine10255 iT邦新手 4 級 ‧ 2015-02-03 09:01:50 檢舉

[Kai's Treasure Chest] MYSQL 5(七)
https://sites.google.com/a/spot.idv.tw/kais-world/my-notes/database/mysql5qi
我有找到這個資料，但有些概念還需要確認了解

wiseguy iT邦超人 1 級 ‧ 2015-02-03 09:05:57 檢舉

問題，就讓它發生！
不發生，你怎麼知道是問題？更何況如果從未發生，那就不是問題了，你根本不用擔心吧？

imagine10255 iT邦新手 4 級 ‧ 2015-02-03 09:12:20 檢舉

不發生，你怎麼知道是問題？

也是啦...但某些情況下經不起他發生..例如會影響訂單量..會員跑了之類的

賽門 iT邦超人 1 級 ‧ 2015-02-03 09:30:05 檢舉

我覺得，這位邦友可能經驗不足，所以在齊人憂天。
基本上，只要用的資料庫不是Access之類的，程式寫再爛，要遇到Deadlock的機會還等於零。
因為你不是設計上百、上千人同時交易的系統，也不在設計那種大量運算，如物料需求計劃的系統。

你只是憑空想像會不會出現，然後出現時如何處理。
很抱歉，這種問題就是只有出現時才能處理。
MySQL會預設以Row Lock來進行交易處理，一般來說，如果真的出現搶Lock時，系統會自動重新排程，先搶到先處理。
沒搶到的會Standby到Timeout，然後一個錯誤訊息告訴操作者。
現在的資料庫引擎有保護機制避免Deadlock出現。

但，真的程式寫的很爛，還是出現Deadlock時，其實你從錯誤訊息就可以發現導致Deadlock發生的原因。
例如，建銷貨單，取單號的程式設計不佳，導致兩個業務同時開到同一單號。
存檔後，更新時，就有可能搶同一筆單據來更新。
這時，MySQL不會當掉要重新開機，只會等到一個Timeout時，把Transaction都切掉。
然後回給前端程式一個錯誤訊息。

所以，在系統開發時就要考慮好這些情況。
這些很多都是經驗之談，沒法全部告訴你何時會發生，只能說....發生時再來補救很來得及。
資料庫系統不會因Deadlock就當掉的。

另外，請你把學習重點放在其他地方，而不是這種特殊異常狀況。
不要再問了。

一級屠豬士 iT邦大師 1 級 ‧ 2015-02-03 09:39:08 檢舉

Lock是好事,不Lock,資料會錯亂.
不Row Lock,那就Table Lock啦,那影響更大.
你要下select for update 就是後面用做update,現在先做Read Lock,
放著不 Commit...那就是懸在那裡,也不是Dead Lock.
你不 Commit,那跟Dead Lock沒關係的.
Dead Lock 是要有條件才會發生的.(要能夠滿足互斥,咬住不放,互相依賴,循環等待四個條件).

一級屠豬士 iT邦大師 1 級 ‧ 2015-02-03 09:43:50 檢舉

imagine10255提到：
因為在網路爬文有寫到要row lock要小心死結的問題

哪裡看到的呢??

葉承宇 (丁丁) Dean Yeh iT邦大師 4 級 ‧ 2015-02-03 11:06:39 檢舉

imagine10255提到：
也是啦...但某些情況下經不起他發生..例如會影響訂單量..會員跑了之類的

請問您是負責二姐專案的人嗎?!

imagine10255 iT邦新手 4 級 ‧ 2015-02-03 11:19:37 檢舉

感謝

imagine10255 iT邦新手 4 級 ‧ 2015-02-03 11:19:47 檢舉

謝謝

登入發表討論

直播研討會

{{ item.channelVendor }} {{ item.webinarstarted }} |

直播中

2 個回答

Albert

iT邦高手 1 級 ‧ 2015-02-02 16:42:00

最佳解答

如果是很多人的線上交易系統
我們部會讓 insert 的 session 去啟動 Update 機制

INSERT Transaction 不啟動 Update Master Table 的機制

由另外一個專門 Update 的 session 去處理,
並回報處理狀況 (就是成功後 insert 另一筆交易成功紀錄更改前後異動欄位值)

回應 1
分享
檢舉

imagine10255 iT邦新手 4 級 ‧ 2015-02-03 08:54:27 檢舉

INSERT Transaction 不啟動 Update Master Table 的機制

請問這部分指的是 master跟slave的架構嗎?

由另外一個專門 Update 的 session 去處理,
並回報處理狀況 (就是成功後 insert 另一筆交易成功紀錄更改前後異動欄位值)

這部分要如何做到..腦袋很抽象中...
類似gearman的排隊處理這樣嗎?

登入發表回應

wiseguy

iT邦超人 1 級 ‧ 2015-02-03 00:31:28

你的例子不可能會發生。因為：

MySQL 的 InnoDB engine 在 transaction 開始時會進行 row lock，也就不能再進行 write，但是其它 transaction 的 read 還是可以進行的。依 SQL 先後順序來決定 read 的結果。
既然 user1 已經啟動 transaction，user2 就不可能再做寫入 (insert, update, delete)，直到 user1 的 transaction 結束，才會輪到 user2 的 update 指令。

回應 3
分享
檢舉

imagine10255 iT邦新手 4 級 ‧ 2015-02-03 08:51:49 檢舉

既然 user1 已經啟動 transaction，user2 就不可能再做寫入 (insert, update, delete)，直到 user1 的 transaction 結束，才會輪到 user2 的 update 指令。

這點我想確認...是當user1的交易啟動時，會先掃描這個程序範圍有用到那些update,insert,就先限制寫入，還是當跑到 insert那段sql的時候才限制update,insert呢?

另外想知道，是不是在這幾個情況下才建議使用 for update 這種row lock
1.更新結果需依前面的查詢結果而訂
2.不希望更新前的資料讓別人讀取

這樣概念對嗎? 請賜教

wiseguy iT邦超人 1 級 ‧ 2015-02-03 09:02:56 檢舉

其實，SQL 跟程式一樣，不要用會讓系統有機會deadlock的語法。
依我的經驗，會發生 deadlock！雖然機會極低，但我遇到了。
而原因通常是程式設計師的 transaction 包得太大或是設計不良所致。就如同你用的語法，可以考慮：

有沒有必要用到 transaction？
用到 transaction 的最小區段在哪？
若 for update 有疑慮，可否改寫成其它語法？
所以不是 DB 有 transaction 就不管三七二十一就給它從頭包到尾 (如同程式的 try catch 一樣)。transaction 要包在有 race condition 疑慮的最小區段。