Deadlocks on node 9 in node table in vBulletin 5.1.1

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • nikkinm
    New Member
    • Jun 2015
    • 1
    • 5.1.x

    Deadlocks on node 9 in node table in vBulletin 5.1.1

    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!!

Related Topics

Collapse

Working...