最新国产好看的视频,伊人天堂AV在线,国产Aaaaaa视频,蜜臀视频在线观看一区,人妻av色图,密臀久久久精品影片,青青视频免费观看毛片,久草在线观看视,国产三级精品色情在线

mysql報(bào)錯(cuò):Deadlock found when trying to get lock; try restarting transaction的解決方法

 更新時(shí)間:2017年07月12日 09:37:30   作者:都市煙火  
這篇文章主要給大家介紹了關(guān)于mysql出現(xiàn)報(bào)錯(cuò):Deadlock found when trying to get lock; try restarting transaction的解決方法,文中通過(guò)示例代碼介紹的非常詳細(xì),對(duì)大家具有一定的參考學(xué)習(xí)價(jià)值,需要的朋友們下面來(lái)一起看看吧。

發(fā)現(xiàn)問(wèn)題

最近在補(bǔ)以前數(shù)據(jù)的時(shí)候程序突然報(bào)如下錯(cuò)誤:

[2017-02-10 13:12:06.678] [INFO] mysqlLog - update tbl_playerdata_error: { [Error: ER_LOCK_DEADLOCK: Deadlock found when trying to get lock; try restarting transaction]
 code: 'ER_LOCK_DEADLOCK',
 errno: 1213,
 sqlState: '40001',
 index: 0 }

一看就是mysql出現(xiàn)了死鎖問(wèn)題,其實(shí)上面跑的程序在測(cè)試服跑了好久都沒(méi)什么問(wèn)題,為什么在正式服上會(huì)出現(xiàn)mysql的死鎖問(wèn)題呢,第一反應(yīng)是不是數(shù)據(jù)量太大(3百多萬(wàn)條),可是也不可能啊,再說(shuō)死鎖和這些有什么雞毛的關(guān)系,看來(lái)要好好解決下了。

問(wèn)題分析

我的分析是:由于現(xiàn)在處理的是正式服的數(shù)據(jù),而正式服還有許多用戶(hù)在操作,應(yīng)該是在用戶(hù)查詢(xún),或者是其他操作的時(shí)候,和我這邊的數(shù)據(jù)更新產(chǎn)生了死鎖(首先說(shuō)明使用的是:InnoDB存儲(chǔ)引擎。由于用戶(hù)那邊的查詢(xún)或者其他操作鎖定了我需要的資源,而我這邊更新也鎖定了用戶(hù)操作的一部分資源,兩邊都等著對(duì)方釋放資源,從而導(dǎo)致死鎖)。

解決方法

知道錯(cuò)誤code之后,先來(lái)查看mysql的說(shuō)明,關(guān)于上面的 Error: 1213 SQLSTATE: 40001,參見(jiàn):Server Error Codes and Messages

Message: Deadlock found when trying to get lock; try restarting transaction

InnoDB reports this error when a transaction encounters a deadlock and is automatically rolled back so that your application can take corrective action. To recover from this error, run all the operations in this transaction again. A deadlock occurs when requests for locks arrive in inconsistent order between transactions. The transaction that was rolled back released all its locks, and the other transaction can now get all the locks it requested. Thus, when you re-run the transaction that was rolled back, it might have to wait for other transactions to complete, but typically the deadlock does not recur. If you encounter frequent deadlocks, make the sequence of locking operations (LOCK TABLES, SELECT ... FOR UPDATE, and so on) consistent between the different transactions or applications that experience the issue. See Section 14.8.5, “Deadlocks in InnoDB” for details.

上面有兩句:

To recover from this error, run all the operations in this transaction again<br><br>If you encounter frequent deadlocks, make the sequence of locking operations (<code class="literal">LOCK TABLES</code>, <code class="literal">SELECT ... FOR UPDATE</code>, and so on) <br>consistent between the different transactions or applications that experience the issue 

這兩句也就道出了處理死鎖的方法了,我就是在死鎖錯(cuò)誤發(fā)生的時(shí)候,使用定時(shí)器再重新做一次更新操作,這樣就避免了上面出現(xiàn)的問(wèn)題。

另外,參考了stack overflow上面一個(gè)回答:http://stackoverflow.com/questions/2332768/how-to-avoid-mysql-deadlock-found-when-trying-to-get-lock-try-restarting-trans

One easy trick that can help with most deadlocks is sorting the operations in a specific order.

You get a deadlock when two transactions are trying to lock two locks at opposite orders, ie:

connection 1: locks key(1), locks key(2);
connection 2: locks key(2), locks key(1);
If both run at the same time, connection 1 will lock key(1), connection 2 will lock key(2) and each connection will wait for the other to release the key -> deadlock.

Now, if you changed your queries such that the connections would lock the keys at the same order, ie:

connection 1: locks key(1), locks key(2);
connection 2: locks key(1), locks key(2);
it will be impossible to get a deadlock.

So this is what I suggest:

Make sure you have no other queries that lock access more than one key at a time except for the delete statement. if you do (and I suspect you do), order their WHERE in (k1,k2,..kn) in ascending order.
Fix your delete statement to work in ascending order:
Change

DELETE FROM onlineusers WHERE datetime <= now() - INTERVAL 900 SECOND
To

DELETE FROM onlineusers WHERE id IN (SELECT id FROM onlineusers
 WHERE datetime <= now() - INTERVAL 900 SECOND order by id) u;
Another thing to keep in mind is that mysql documentation suggest that in case of a deadlock the client should retry automatically. you can add this logic to your client code. (Say, 3 retries on this particular error before giving up).

參考:http://blog.sina.com.cn/s/blog_4acbd39c01014gsq.html

總結(jié)

以上就是這篇文章的全部?jī)?nèi)容了,希望本文的內(nèi)容對(duì)大家的學(xué)習(xí)或者工作能帶來(lái)一定的幫助,如果有疑問(wèn)大家可以留言交流,謝謝大家對(duì)腳本之家的支持。

相關(guān)文章

  • 解析SQL Server 視圖、數(shù)據(jù)庫(kù)快照

    解析SQL Server 視圖、數(shù)據(jù)庫(kù)快照

    在程序開(kāi)發(fā)過(guò)程中,任何一個(gè)項(xiàng)目都離不開(kāi)數(shù)據(jù)庫(kù),這篇文章給大家詳細(xì)介紹SQL Server 視圖、數(shù)據(jù)庫(kù)快照相關(guān)內(nèi)容,需要的朋友可以參考下
    2015-08-08
  • MySQL [Warning] TIMESTAMP with implicit DEFAULT value is deprecated(報(bào)錯(cuò)信息解決)

    MySQL [Warning] TIMESTAMP with implicit&

    本文介紹了MySQL中常見(jiàn)的報(bào)錯(cuò)信息及其解決方法,主要包括TIMESTAMP with implicit DEFAULT value is deprecated、ERROR_FOR_DIVISION_BY_ZERO和NO_ZERO_DATE/NO_ZERO_IN_DATE等報(bào)錯(cuò)信息,以及對(duì)應(yīng)的配置文件設(shè)置和sql_mode修改方法,感興趣的朋友一起看看吧
    2025-02-02
  • mysql的case when字段為空,null的問(wèn)題

    mysql的case when字段為空,null的問(wèn)題

    這篇文章主要介紹了mysql的case when字段為空,null的問(wèn)題。具有很好的參考價(jià)值,希望對(duì)大家有所幫助。如有錯(cuò)誤或未考慮完全的地方,望不吝賜教
    2022-12-12
  • 深入理解mysql各種鎖

    深入理解mysql各種鎖

    大家好,本篇文章主要講的是深入理解mysql各種鎖,感興趣的同學(xué)趕快來(lái)看一看吧,對(duì)你有幫助的話(huà)記得收藏一下,方便下次瀏覽
    2021-12-12
  • MySQL中使用auto_increment修改初始值和步長(zhǎng)

    MySQL中使用auto_increment修改初始值和步長(zhǎng)

    本文主要介紹了MySQL中使用auto_increment修改初始值和步長(zhǎng),文中通過(guò)示例代碼介紹的非常詳細(xì),對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值,需要的朋友們下面隨著小編來(lái)一起學(xué)習(xí)學(xué)習(xí)吧
    2024-05-05
  • MySQL鎖等待超時(shí)問(wèn)題的原因和解決方案(Lock wait timeout exceeded; try restarting transaction)

    MySQL鎖等待超時(shí)問(wèn)題的原因和解決方案(Lock wait timeout exceed

    在數(shù)據(jù)庫(kù)開(kāi)發(fā)和管理中,鎖等待超時(shí)是一個(gè)常見(jiàn)而棘手的問(wèn)題,對(duì)于使用 MySQL 的應(yīng)用程序,尤其是采用 InnoDB 存儲(chǔ)引擎的場(chǎng)景,這一問(wèn)題更是屢見(jiàn)不鮮,本文給大家介紹了MySQL鎖等待超時(shí)問(wèn)題的原因和解決方案,需要的朋友可以參考下
    2024-11-11
  • MySQL8.0.24版本Release Note的一些改進(jìn)點(diǎn)

    MySQL8.0.24版本Release Note的一些改進(jìn)點(diǎn)

    這篇文章主要介紹了MySQL8.0.24版本Release Note的一些改進(jìn)點(diǎn),幫助大家更好的對(duì)新版本的MySQL進(jìn)行測(cè)試使用,感興趣的朋友可以了解下
    2021-04-04
  • mysql安裝配置方法圖文教程(CentOS7)

    mysql安裝配置方法圖文教程(CentOS7)

    這篇文章主要為大家詳細(xì)介紹了centos7下mysql安裝配置方法圖文教程,具有一定的參考價(jià)值,感興趣的小伙伴們可以參考一下
    2017-04-04
  • window環(huán)境下使用VScode連接虛擬機(jī)MySQL方法

    window環(huán)境下使用VScode連接虛擬機(jī)MySQL方法

    這篇文章主要介紹了window環(huán)境下使用VScode連接虛擬機(jī)MySQL方法,文中通過(guò)示例代碼介紹的非常詳細(xì),對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值,需要的朋友們下面隨著小編來(lái)一起學(xué)習(xí)學(xué)習(xí)吧
    2019-04-04
  • MySQL是如何實(shí)現(xiàn)主備同步

    MySQL是如何實(shí)現(xiàn)主備同步

    這篇文章主要介紹了MySQL是如何實(shí)現(xiàn)主備同步的,幫助大家更好的理解和使用MySQL數(shù)據(jù)庫(kù),感興趣的朋友可以了解下
    2020-12-12

最新評(píng)論

石柱| 仁怀市| 重庆市| 玉溪市| 龙里县| 建平县| 喜德县| 永吉县| 苏州市| 济源市| 东方市| 西林县| 湖北省| 南乐县| 阿拉善盟| 罗田县| 南阳市| 垫江县| 武威市| 镇安县| 澄迈县| 大洼县| 安龙县| 望都县| 普宁市| 金寨县| 呼伦贝尔市| 体育| 那曲县| 珠海市| 山丹县| 友谊县| 大理市| 双鸭山市| 孟连| 宁明县| 柯坪县| 龙胜| 东至县| 赣州市| 兴海县|