Hi,
I'm having more than 2k database error emails about deadlocks happened between these 2 SQL statements:
1: Invalid SQL:
UPDATE node SET
textcount =
CASE WHEN 1 > 0 OR textcount > -1 * 1
THEN textcount + (1)
ELSE 0 END,
textunpubcount =
CASE WHEN 0 > 0 OR textunpubcount > -1 * 0
THEN textunpubcount + (0)
ELSE 0 END
WHERE nodeid IN (9)
2: UPDATE node AS node LEFT JOIN
(SELECT lastcontent, lastcontentid, lastcontentauthor, lastauthorid, parentid
FROM node
WHERE parentid = 9
ORDER BY lastcontent DESC, lastcontentid DESC LIMIT 1 ) AS lc ON (lc.parentid = node.nodeid)
SET node.lastcontent = lc.lastcontent,
node.lastcontentauthor = lc.lastcontentauthor,
node.lastcontentid = lc.lastcontentid,
node.lastauthorid = lc.lastauthorid
WHERE nodeid = 9
Below is the content of this certain record in the table of node:
*************************** 1. row ***************************
nodeid: 9
routeid: 51
contenttypeid: 23
publishdate: 1321982542
unpublishdate: NULL
userid: 1
groupid: NULL
authorname: xxxx
description: Private Messages
title: Private Messages
htmltitle: Private Messages
parentid: 7
urlident: private-messages
displayorder: 2
starter: 0
created: 1400840752
lastcontent: 1434410947
lastcontentid: 309908
lastcontentauthor: whatwhatwhat
lastauthorid: 3599
lastprefixid:
textcount: 246646
textunpubcount: 1017
totalcount: 247674
totalunpubcount: 1020
ipaddress: 50.152.145.6
showpublished: 1
oldid: NULL
oldcontenttypeid: NULL
nextupdate: NULL
lastupdate: 1400840752
featured: 0
CRC32: 1401867583
taglist: NULL
inlist: 0
protected: 1
setfor: 0
votes: 0
hasphoto: 0
hasvideo: 0
deleteuserid: NULL
open: 1
showopen: 1
sticky: 0
approved: 1
showapproved: 1
viewperms: 2
commentperms: 1
nodeoptions: 138
prefixid:
iconid: 0
public_preview: 0
And I did some research in the db and found that vBulletin uses different nodes as some kind of inner data structure to keep track of posts/pages/URLs, but I couldn't pinpoint exact cause to this deadlock. Any solutions or hints are greatly appreciated!!
I'm having more than 2k database error emails about deadlocks happened between these 2 SQL statements:
1: Invalid SQL:
UPDATE node SET
textcount =
CASE WHEN 1 > 0 OR textcount > -1 * 1
THEN textcount + (1)
ELSE 0 END,
textunpubcount =
CASE WHEN 0 > 0 OR textunpubcount > -1 * 0
THEN textunpubcount + (0)
ELSE 0 END
WHERE nodeid IN (9)
2: UPDATE node AS node LEFT JOIN
(SELECT lastcontent, lastcontentid, lastcontentauthor, lastauthorid, parentid
FROM node
WHERE parentid = 9
ORDER BY lastcontent DESC, lastcontentid DESC LIMIT 1 ) AS lc ON (lc.parentid = node.nodeid)
SET node.lastcontent = lc.lastcontent,
node.lastcontentauthor = lc.lastcontentauthor,
node.lastcontentid = lc.lastcontentid,
node.lastauthorid = lc.lastauthorid
WHERE nodeid = 9
Below is the content of this certain record in the table of node:
*************************** 1. row ***************************
nodeid: 9
routeid: 51
contenttypeid: 23
publishdate: 1321982542
unpublishdate: NULL
userid: 1
groupid: NULL
authorname: xxxx
description: Private Messages
title: Private Messages
htmltitle: Private Messages
parentid: 7
urlident: private-messages
displayorder: 2
starter: 0
created: 1400840752
lastcontent: 1434410947
lastcontentid: 309908
lastcontentauthor: whatwhatwhat
lastauthorid: 3599
lastprefixid:
textcount: 246646
textunpubcount: 1017
totalcount: 247674
totalunpubcount: 1020
ipaddress: 50.152.145.6
showpublished: 1
oldid: NULL
oldcontenttypeid: NULL
nextupdate: NULL
lastupdate: 1400840752
featured: 0
CRC32: 1401867583
taglist: NULL
inlist: 0
protected: 1
setfor: 0
votes: 0
hasphoto: 0
hasvideo: 0
deleteuserid: NULL
open: 1
showopen: 1
sticky: 0
approved: 1
showapproved: 1
viewperms: 2
commentperms: 1
nodeoptions: 138
prefixid:
iconid: 0
public_preview: 0
And I did some research in the db and found that vBulletin uses different nodes as some kind of inner data structure to keep track of posts/pages/URLs, but I couldn't pinpoint exact cause to this deadlock. Any solutions or hints are greatly appreciated!!