最近工作非常郁闷，天天被领导盯着，
事务、事务并发
。主要是系统近来死锁发生在频率很高。最终，经过大家的共同努力，我们成功的定位并解决了问题，所以把过程中学习的知识与经验分享一下vcrCzvEsy/zKx9K7uPay2df30PLB0KOs1eLQqbLZ1/fSqsO0trzWtNDQo6zSqsO0tryyu9a00NCjrMv8ysfSu7j2sru/ybfWuO61xNSt19O1pc67oaPA/cjno6zS+NDQ16rVyrmk1/ejurTT0ru49tXKusW/27/usqLKucHt0ru49tXKusXU9r/uo6zV4sG9uPay2df30qrDtLa81rTQ0KOs0qrDtLa8srvWtNDQoaM8YnI+Cjxicj4KysLO8bXEzNjQ1Dxicj4KCjx1PjxlbT48c3Ryb25nPtSt19PQ1Dwvc3Ryb25nPjwvZW0+PC91PqGj0ru49srCzvHKx9K7uPayu7/Jt9a47rXEuaTX97WlzrujrMrCzvHW0LD8wKi1xNbustnX99Kqw7S2vNf2o6zSqsO0tryyu9f2oaM8YnI+Cjx1PjxlbT48c3Ryb25nPtK71sLQ1Dwvc3Ryb25nPjwvZW0+PC91PqGjysLO8bHY0OvKx8q5yv2+3b/itNPSu7j20rvWwtDU17TMrLHktb3B7dK7uPbSu9bC0NTXtMysoaPSu9bC0NTT69St19PQ1MrHw9zH0M/gudi1xKGjPGJyPgo8dT48ZW0+PHN0cm9uZz649MDr0NQ8L3N0cm9uZz48L2VtPjwvdT6ho9K7uPbKws7xtcTWtNDQsrvE3LG7xuTL+8rCzvG4ycjFoaO8tNK7uPbKws7xxNqyv7XEstnX97ywyrnTw7XEyv2+3bbUsqK3orXExuTL+8rCzvHKx7j0wOu1xKOssqK3ota00NC1xLj3uPbKws7x1q685LK7xNy7pc/guMnIxaGjPGJyPgo8dT48ZW0+PHN0cm9uZz6z1r7D0NQ8L3N0cm9uZz48L2VtPjwvdT6ho7PW0PjQ1NKys8bTwL7D0NSjrNa40ru49srCzvHSu7WpzOG9u6Osy/y21Mr9vt2/4tbQyv2+3bXEuMSx5L7N06a4w8rH08C+w9DUtcSho73Tz8LAtLXExuTL+7LZ1/e78rnK1c+yu9OmuMO21Mbk09DIzrrO07DP7KGjCjxicj4Ky8S49szY0NTW0KOs0+vO0sPHzsrM4s/gudi1xNOmuMO+zcrHysLO8bXEuPTA69DUwcujrNKqwO294rj0wOvQ1KOsvs2yu7XDsrvLtcu1ysLO8bXEsqK3osHLoaM8YnI+Cjxicj4KysLO8bXEsqK3orywtPjAtLXE07DP7Dxicj4KysLO8bKit6LNqMvXteO9sr7Nyse24Lj2ysLO8c2syrHWtNDQo6yyoreitcS3w87Ku/K4/NDCyv2+3b/i1tDP4M2stcTK/b7doaPKws7xsqK3otK7sOPQ6NKqz+DTprXEuPTA67TryqmjrLfx1PK+zbvhs/bP1rj31tbOyszioaM8YnI+Cjxicj4KysLO8bXEsqK3orvh1OyzycTE0KnOyszio788YnI+Cgo8dT48ZW0+PHN0cm9uZz62qsqnuPzQwjwvc3Ryb25nPjwvZW0+PC91PqGjtdrSu9bWx+m/9qO6tbEyuPbKws7xuPzQws/gzay1xMr9vt3UtKOsyOe5+7Xa0ru49srCzvGxu8zhvbujrNauuvPB7c3i0ru49srCzvHSsszhvbvBy6OsxMfDtLXa0ru49srCzvHL+df2tcS4/NDCvs3Kp7aqwcuho7Xatv7W1sfpv/ajurWxMrj2ysLO8bj80MLP4M2stcTK/b7d1LSjrMjnufu12tK7uPbKws7xsbvM4b27o6y2+MHtzeLSu7j2ysLO8ci0sbuzt8/6o6zEx8O0u+HBrM2stdrSu7j2ysLO8cv51/a1xLj60MLSsrG7s7fP+qGjPGJyPgo8dT48ZW0+PHN0cm9uZz7U4LbBPC9zdHJvbmc+PC9lbT48L3U+oaPKws7xtsHIobHwtcTKws7xzrTM4b27tcTU4Mr9vt2ho8jno7rKws7xMbLlyOu78rj80MLBy8r9vt1BuvOjrMrCzvEytsHIocHLyv2+3UGjrNXiyrHKws7xMbvYufbBy6OsxMfKws7xMrbBtb21xMr9vt1Bvs3Kx9Tgyv2+3cHLoaM8YnI+Cjx1PjxlbT48c3Ryb25nPrK7v8nW2Li0tsE8L3N0cm9uZz48L2VtPjwvdT6ho9TazazSu7j2ysLO8be2zqfE2qOstuC0zrLp0a/NrNK7uPbK/b7dtcQmIzIwNTQwO7K7zayho8jno7rKws7xMbLp0a/K/b7dQSA9IDEwo6zV4sDvysLO8TKw0cr9vt1BtcQmIzIwNTQwO7j80MLOqjExuvPM4b27wcujrMrCzvEx1NrN6rPJsr+31sLfvK2689TZtM62wcihyv2+3UG1xCYjMjA1NDA7seTOqjExwcuhozxicj4KPHU+PGVtPjxzdHJvbmc+u8O2wTwvc3Ryb25nPjwvZW0+PC91PqGj1NrNrNK7uPbKws7xt7bOp8Tao6y24LTO0tTP4M2stcSy6dGvzPW8/rLptb21xMr9vt3K/cG/srvSu9H5oaPI56O6ysLO8THS1Mz1vP5YsunRr8r9vt2jrNXiyrHKws7xMrLlyOu78sm+s/3By8r9vt2jrNauuvPKws7xMdTazeqzybK/t9bC37ytuvPU2bTO0tTM9bz+WLLp0a/K/b7dyrGjrLe1u9i1xL3hufvK/cG/srvSu9H5wcuhozxicj4KPHU+PGVtPjxzdHJvbmc+uLK4x7j80MI8L3N0cm9uZz48L2VtPjwvdT6ho8rCzvG21Mr9vt21xLj80MKxu7HwtcTKws7xuLK4x8HLoaPI56O6ysLO8TG6zTLNrMqxtsHIocr9vt1BID0gMTCjrMrCzvExyejWw0EgPSBBIC0gMbrzuPzQwsr9vt1BIKO9IDmjrMrCzvEyyejWw0EgPSBBIC0gMrrzuPzQwsr9vt1BID0gOKOs1+7W1cr9vt1BtcQmIzIwNTQwO7Hks8nByzijrLWr1f3It8fpv/bPwkG1xCYjMjA1NDA706a4w8rHN6GjCjxicj4K1qq1wMHLysLO8bKit6K74dTss8nKssO007DP7KOsxMfI57rOsdzD4s7KzOK1xLeiyfrE2KO/tNPK/b7dv+Ky48PmwLS9sqOsysfNqLn9t+LL+NCt0unU2b3ivva1xKGjPGJyPgo8YnI+Csr9vt2/4rfiy/jQrdLpPGJyPgrU2su1t+LL+NCt0unHsKOsz8i1w72yvbLL+KGjPHU+PGVtPjxzdHJvbmc+yv2+3b/i1tDT0MG91tbL+KO6ubLP7cv4o6i2wcv4U6Op0+vFxcv8y/ijqNC0y/hYo6k8L3N0cm9uZz48L2VtPjwvdT6hozxicj4KCjx1PjxlbT48c3Ryb25nPrbBy/g8L3N0cm9uZz48L2VtPjwvdT6jusj0ysLO8VS21Mr9vt221M/zQbzTyc9Ty/ijrNTyysLO8VS/ydLUtsFBtauyu8Tc0N64xEGjrMbky/vKws7x1rvE3NTZttRBvNNTy/ijrLb4srvE3LzTWMv4o6zWsbW9VMrNt8VTy/ihozxicj4KPHU+PGVtPjxzdHJvbmc+0LTL+Dwvc3Ryb25nPjwvZW0+PC91PqO6yPTKws7xVLbUyv2+3bbUz/NBvNPJz1jL+KOs1PLWu9TK0O1UtsHIobrN0N64xEGjrMbky/vKws7xsrvE3NTZttRBvNPIzrrOwODQzbXEy/ijrNaxtb1Uys23xUHJz7XEWMv4oaMKPGJyPgrU2cC0v7S/tLfiy/jQrdLpPGJyPgoKPHU+PGVtPjxzdHJvbmc+0ru8trfiy/jQrdLpPC9zdHJvbmc+PC9lbT48L3U+o7rKws7xVNTZ0N64xMr9vt1S1q7HsKOssdjQ68/IttTG5LzTWMv4o6zWsbW9ysLO8b3hyviyxcrNt8Who9K7vLa34sv40K3S6b/J0tS3wNa5PGVtPjx1PjxzdHJvbmc+tqrKp7j80MI8L3N0cm9uZz48L3U+PC9lbT6hozxicj4KPHU+PGVtPjxzdHJvbmc+tv68trfiy/jQrdLpPC9zdHJvbmc+PC9lbT48L3U+o7rU2tK7vLa34sv40K3S6bXEu/m0ocnPo6zKws7xVNTatsHIocr9vt1S1q7HsKOssdjQ68/IttTG5LzTyc9Ty/ijrLbBzerWrrrzwaK8tMrNt8VTy/iho7b+vLa34sv40K3S6bP9wcvE3LfA1rk8ZW0+PHU+PHN0cm9uZz62qsqnuPzQwjwvc3Ryb25nPjwvdT48L2VtPqOsu7m/ydLUt8DWuTxlbT48dT48c3Ryb25nPtTgtsE8L3N0cm9uZz48L3U+PC9lbT6hozxicj4KPHU+PGVtPjxzdHJvbmc+yP28trfiy/jQrdLpPC9zdHJvbmc+PC9lbT48L3U+o7rU2tK7vLa34sv40K3S6bXEu/m0ocnPo6zKws7xVNTatsHIocr9vt1S1q7HsKOssdjQ68/IttTG5LzTyc9Ty/ijrNaxtb3Kws7xveHK+LrzssXKzbfFoaPI/by2t+LL+NCt0um/ydLUt8DWuTxlbT48dT48c3Ryb25nPraqyqe4/NDCPC9zdHJvbmc+PC91PjwvZW0+oaI8ZW0+PHU+PHN0cm9uZz7U4LbBPC9zdHJvbmc+PC91PjwvZW0+us08ZW0+PHU+PHN0cm9uZz6yu7/J1ti4tLbBPC9zdHJvbmc+PC91PjwvZW0+oaMKPGJyPgq34tLp0K3S6bKisrvE3L3ivvbKws7xsqK3orXEy/nT0M7KzOKjqDx1PjxlbT48c3Ryb25nPriyuMe4/NDCPC9zdHJvbmc+PC9lbT48L3U+o6mjrMHtzeKjrLfiy/jQrdLpysfK/b7dv+LE2rK/yrXP1srCzvGyoreiv9jWxrXE0rvW1rv61sajrM7Sw8fO3reo1NqzzNDy1tDKudPDoaPU2rPM0PK/qrei1tCjrMrHzai5/da4tqjKws7xtcQ8dT48ZW0+PHN0cm9uZz649MDrvLax8Dwvc3Ryb25nPjwvZW0+PC91PsC0veK+9rKit6K1xLj31tbOyszitcShozxicj4KPGJyPgrKws7xtcS49MDrvLax8Dxicj4K09DLxNbWuPTA67y2sfCjrM7ewtvExNK71ta2vCYjNjsmIzY7sru74bP2z9Y8dT48ZW0+PHN0cm9uZz62qsqnuPzQwjwvc3Ryb25nPjwvZW0+PC91PqOs0vLOqsvE1ta49MDrvLax8La80qrH89TauPzQwsr9vt221M/zx7DPyNKqttTK/b7dvNNYy/ijrNaxtb3Kws7xveHK+LrzssXKzbfFo6y8tNK7vLa34sv40K3S6aGjPGJyPgoKPHU+PGVtPjxzdHJvbmc+UmVhZCBVbmNvbW1pdHRlZLbBzrTM4b27PC9zdHJvbmc+PC9lbT48L3U+o7o8dT48ZW0+PHN0cm9uZz7U4LbBPC9zdHJvbmc+PC9lbT48L3U+oaI8dT48ZW0+PHN0cm9uZz6yu7/J1ti4tLbBPC9zdHJvbmc+PC9lbT48L3U+oaI8dT48ZW0+PHN0cm9uZz67w7bBPC9zdHJvbmc+PC9lbT48L3U+us08dT48ZW0+PHN0cm9uZz64srjHuPzQwjwvc3Ryb25nPjwvZW0+PC91Pra8u+G3osn6o6y1q7Kit6LQ1MTc1+66w6GjPGJyPgo8dT48ZW0+PHN0cm9uZz5SZWFkIENvbW1pdHRlZLbB0tHM4b27PC9zdHJvbmc+PC9lbT48L3U+o7rE3LHcw+I8dT48ZW0+PHN0cm9uZz7U4LbBPC9zdHJvbmc+PC9lbT48L3U+o6y1qzx1PjxlbT48c3Ryb25nPrK7v8nW2Li0tsE8L3N0cm9uZz48L2VtPjwvdT6hojx1PjxlbT48c3Ryb25nPrvDtsE8L3N0cm9uZz48L2VtPjwvdT66zTx1PjxlbT48c3Ryb25nPriyuMe4/NDCPC9zdHJvbmc+PC9lbT48L3U+u+G3osn6o6yyorei0NTE3LrDoaO087K/t9bK/b7dv+K1xMSsyM+49MDrvLax8KOssqK3otDUxNy9z7rDoaM8YnI+Cjx1PjxlbT48c3Ryb25nPlJlcGVhdGFibGUgUmVhZL/J1ti4tLbBPC9zdHJvbmc+PC9lbT48L3U+o7rE3LHcw+I8dT48ZW0+PHN0cm9uZz7U4LbBPC9zdHJvbmc+PC9lbT48L3U+us08dT48ZW0+PHN0cm9uZz6yu7/J1ti4tLbBPC9zdHJvbmc+PC9lbT48L3U+o6y1qzx1PjxlbT48c3Ryb25nPrvDtsE8L3N0cm9uZz48L2VtPjwvdT674beiyfqjrLKit6LQ1MTcu+HK3NOwz+yho9bB09o8dT48ZW0+PHN0cm9uZz64srjHuPzQwjwvc3Ryb25nPjwvZW0+PC91PqOs1PLK08r9vt2/4rb4wtuhozxicj4KPHU+PGVtPjxzdHJvbmc+U2VyaWFsaXphYmxl0PLB0LuvPC9zdHJvbmc+PC9lbT48L3U+o7rE3LHcw+I8dT48ZW0+PHN0cm9uZz7U4LbBPC9zdHJvbmc+PC9lbT48L3U+oaI8dT48ZW0+PHN0cm9uZz6yu7/J1ti4tLbBPC9zdHJvbmc+PC9lbT48L3U+us08dT48ZW0+PHN0cm9uZz67w7bBPC9zdHJvbmc+PC9lbT48L3U+o6yyorei0NTE3LLuoaPWwdPaPHU+PGVtPjxzdHJvbmc+uLK4x7j80MI8L3N0cm9uZz48L2VtPjwvdT6jrNTyytPK/b7dv+K2+MLboaMKPGJyPgrV4tH5v7TAtKOsysLO8bj0wOu8trHwusPP8dPrt+LL+NCt0unKx9K70ru21NOmtcSho7bBzrTM4b270+vSu7y2t+LL+NCt0um21NOmoaK2wdLRzOG9u9Prtv68trfiy/jQrdLpttTTpqGiv8nW2Li0tsHT68j9vLa34sv40K3S6bbU06aho7Wrvq25/bG+yMu1xNHp1qSjrNXiuPayu83qyKvV/ci3o6y49MDrvLax8MrH0ru49rnmt7ajrLb4t+LS6dCt0unKx7nmt7a1xNK71tbKtc/Wt73KvaGjscjI5215c3HNrMqxyrnTw8r9vt3Q0LDmsb7T68v4tcS7+tbGwLTKtc/WuPTA67y2sfC1xKOsREIytaW0v8q508PL+LXEu/rWxsC0yrXP1rj0wOu8trHwoaPO0rvh1tjQwtX7wO3Su8aqzsTVwr2yvbK52NPabXlzcWzT60RCMtTauPTA67y2sfC3vcPmtcTPuL3a0+vH+LHwoaM8YnI+Cjxicj4KztLDx9LRvq3WqrXAwcvP4LnYtcTWqsq2sbO+sKOs1NnAtL+0v7TO0sPHz7XNs7XEzsrM4qGjPGJyPgrPtc2zyrnTw7XEysdEQjKjrMv509DWu7bBysLO8cq508NVUrj0wOu8trHwo6jP4LnY09q2wc60zOG9u6Opo6zG5Mv7ysLO8cq508NSU7j0wOu8trHwo6jP4LnY09q/ydbYuLS2waOpoaO74dSxtcTXyrL6uabE3LzytaW9stPQ1Pa807rNv9u89bLZ1/ehozxicj4K1Pa807LZ1/c8YnI+CjxwcmUgY2xhc3M9"brush:sql;">start transaction//第一步，查询账户可用余额select ... from account where account_id = ...//第二步，余量加上增加量，并赋值给新变量newBalanceset newBalance = dbBalance + changeAmount//第三步，更新会员账户记录update account set balance = newBalance where account_id = ...//第四步，其它操作commit扣减操作
start transaction//第一步，查询账户可用余额select ... from account where account_id = ...//第二步，余量扣去增加量，并赋值给新变量NEW_BALANCE，如果余量不足则报错，电脑资料
《事务、事务并发》(http://meiwen.anslib.com)。set newBalance = dbBalance - changeAmountif ( newBalance < 0 ) throw new Exception("...");//第三步，更新会员账户记录update account set balance = newBalance where account_id = ...//第四步，其它操作commit
上面使用伪语言描述，可以看出，不论是增加还是扣减操作，都是先查询出账户记录，根据记录值作计算，最后再更新记录。当外围系统同时访问同一会员做扣减的时候，死锁就有可能发生了，比如：
事务T1执行第一步，查询了会员的账户记录，因为隔离级别是RS，所以会对会员账户记录加S锁；这时候事务T2也执行了第一步，也对会员的账户记录加了S锁；之后事务T1执行第三步，在准备更新会员账户记录前需要先对其加X锁，但发现记录已经被其它事务（事务T2）加了S锁，所以事务T1挂起，等待事务T2释放账户记录的S锁；接着事务T2也执行到第三步，在准备更新会员账户记录前需要先对其加X锁，但发现记录已经被其它事务（事务T1）加了S锁，所以事务T2也挂起等待事务T1。这样死锁就发生了。
知道了死锁发生的原因，那现在看看如何解决这个问题。死锁发生的原因主要是第一步查询的时候对账户记录加了S锁，所以如何我们把隔离级别降为CS（相当于读已提交），能否解决问题呢？答案是否，把隔离级底降为CS确实可以避免死锁的发生，因为查询操作结束后就会释放S锁，但是却会发生覆盖更新的问题，所以这个方案不可行。既然问题出在第一步，那就是第一步出法看看吧，如果查询账户的时候，我们显示的对记录加X锁而不是S锁，那问题就解决了？看看：
事务T1执行第一步，查询了会员的账户记录，显式对会员账户记录加X锁；这时候事务T2执行到第一步，在查询会员账户记录尝试加X锁时会等待，因为记录已经被别的事务（事务T1）加了X锁；之后事务T1执行第三步，顺利的更新了记录，因为它已经占有记录的X锁；在事务T1提交之后，事务T2就可以继续往下执行了，所以死锁的问题解决了。
优化后的扣减操作
start transaction//第一步，查询账户可用余额select ... from account where account_id = ... for update with rs//第二步，余量扣去增加量，并赋值给新变量NEW_BALANCE，如果余量不足则报错。set newBalance = dbBalance - changeAmountif ( newBalance < 0 ) throw new Exception("...");//第三步，更新会员账户记录update account set balance = newBalance where account_id = ...//第四步，其它操作commit对于增加操作来讲，因为没有上限限制，所以可以直接更新增加量就可以了。优化后的增加操作
start transaction//第一步，更新会员账户记录update account set balance = balance + changeAmount where account_id = ...//第二步，其它操作commit
经过上面两个优化，死锁不再发生了，另外接口的平均响应时间也有不小的提高。