Issue: Deadlock found when trying to get lock

Issue Tools
- View Changes

February 20, 2017 1:32 PM

Alfa1

Distinguished Member

Deadlock found when trying to get lock

Code:

Zend_Db_Statement_Mysqli_Exception: Mysqli statement execute error : Deadlock found when trying to get lock; try restarting transaction - library/Zend/Db/Statement/Mysqli.php:214
Generated By: Alfa, 4 minutes ago

Stack Trace
#0 /var/www/html/library/Zend/Db/Statement.php(297): Zend_Db_Statement_Mysqli->_execute(Array)
#1 /var/www/html/library/Zend/Db/Adapter/Abstract.php(479): Zend_Db_Statement->execute(Array)
#2 /var/www/html/library/Zend/Db/Adapter/Abstract.php(574): Zend_Db_Adapter_Abstract->query('INSERT INTO `vw...', Array)
#3 /var/www/html/library/XenForo/DataWriter.php(1638): Zend_Db_Adapter_Abstract->insert('vw_permission', Array)
#4 /var/www/html/library/XenForo/DataWriter.php(1627): XenForo_DataWriter->_insert()
#5 /var/www/html/library/vw/XenForo/DataWriter.php(352): XenForo_DataWriter->_save()
#6 /var/www/html/library/XenForo/DataWriter.php(1419): vw_XenForo_DataWriter->_save()
#7 /var/www/html/vault/core/controller/dm/xf.php(415): XenForo_DataWriter->save()
#8 /var/www/html/vault/core/controller/cp/permission/vw.php(740): vw_DM_Controller_XF->save()
#9 /var/www/html/vault/core/view/cp/permission/vw.php(616): vw_CP_Permission_Controller->init_permissions_for_group(49)
#10 /var/www/html/vault/core/controller/cp/permission/vw.php(752): vw_CP_Permission_View->modify(Object(vw_CP_Permission_Controller))
#11 /var/www/html/vault/core/controller/cp/permission/vw.php(65): vw_CP_Permission_Controller->modify()
#12 /var/www/html/library/vw/XenForo/ControllerAdmin/Wiki.php(118): vw_CP_Permission_Controller->execute('', 'get')
#13 /var/www/html/library/XenForo/FrontController.php(351): vw_XenForo_ControllerAdmin_Wiki->actionIndex()
#14 /var/www/html/library/XenForo/FrontController.php(134): XenForo_FrontController->dispatch(Object(XenForo_RouteMatch))
#15 /var/www/html/admin.php(13): XenForo_FrontController->run()
#16 {main}
Request State
array(3) {
  ["url"] => string(54) "http://site.nl/admin.php?wiki/permission"
  ["_GET"] => array(2) {
    ["wiki/permission"] => string(0) ""
    ["panel"] => string(10) "permission"
  }
  ["_POST"] => array(0) {
  }
}

Issue Details

Issue Number 4926

Issue Type Bug

Project VaultWiki 4.x Series

Category Admin Panel

Status Fixed

Priority 2 - Fatal / Database Errors

Affected Version 4.0.16

Fixed Version (none)

Milestone (none)

Software DependencyXenForo 1.x

License TypePaid

Users able to reproduce bug 0

Users unable to reproduce bug 0

Attachments 0

Assigned Users (none)

Tags (none)

February 21, 2017 10:12 AM
pegasus

VaultWiki Team
In MySQL, please run:

Code:

SHOW ENGINE INNODB STATUS \G

In the results, look for the section called 'LATEST DETECTED DEADLOCK'
We will need to know all of the transactions involved in the deadlock in order to fix this issue.
Reply
February 21, 2017 1:36 PM

Alfa1

Distinguished Member

I am getting this:
SHOW ENGINE INNODB STATUS \G
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '\G' at line 1

Reply
February 21, 2017 2:26 PM

pegasus

VaultWiki Team

In that case, use a ; instead of the \G

Reply

February 21, 2017 3:48 PM

Alfa1

Distinguished Member

Code:

2017-02-20 19:27:00 7f63cfed3700
*** (1) TRANSACTION:
TRANSACTION 37905831, ACTIVE 1 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 360, 2 row lock(s), undo log entries 1
MySQL thread id 278944, OS thread handle 0x7f64ef8ac700, query id 783589246 localhost xxx update
INSERT INTO `vw_permission` (`applytypeid`, `applyid`, `nodetypeid`, `nodeid`, `applykey`, `bitvalue`) VALUES (?, ?, ?, ?, ?, ?)
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 2554 page no 4 n bits 128 index `race_condition` of table `xxx_xxx`.`vw_permission` trx id 37905831 lock mode S waiting
*** (2) TRANSACTION:
TRANSACTION 37905828, ACTIVE 2 sec fetching rows, thread declared inside InnoDB 4941
mysql tables in use 1, locked 1
1443 lock struct(s), heap size 161320, 241754 row lock(s), undo log entries 240258
MySQL thread id 278943, OS thread handle 0x7f63cfed3700, query id 783589249 localhost xxx updating
DELETE FROM `vw_permission` WHERE applytypeid = 0 AND applyid <> 0
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 2554 page no 4 n bits 128 index `race_condition` of table `xxx_xxx`.`vw_permission` trx id 37905828 lock_mode X locks rec but not gap
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 2554 page no 13 n bits 80 index `PRIMARY` of table `xxx_xxx`.`vw_permission` trx id 37905828 lock_mode X waiting
*** WE ROLL BACK TRANSACTION (1)

February 21, 2017 4:34 PM

pegasus

VaultWiki Team

Did you open a main Wiki Permissions page, such as the "Usergroups" list, in 2 separate tabs at the same time, having never accessed that page before in the current installation? In this case, I might foresee a race condition where both tabs are trying to insert the initial permissions and rebuild the user-permission cache at the same time; we have only basic protection against a duplicate key error here (you would have gotten lucky on that) but it would also be possible to create a deadlock in that situation.

I cannot imagine any other way to do this since both the error in the OP and the conflicting query in the deadlock are both initiated by the admin.

Reply
February 21, 2017 5:27 PM

Alfa1

Distinguished Member

Its possible that I opened both wiki permissions as usergroup permissions.

Reply
February 22, 2017 8:51 AM

pegasus

VaultWiki Team

I have made some improvements here for the next release that will reduce the occurrence of one of the queries in the deadlock. This should also reduce the probability that a deadlock will occur with these queries, when opening duplicate tabs with the same permissions panel that was never opened before.

Reply

+ Reply

All times are GMT -4. The time now is 7:45 AM.

This site uses cookies to help personalize content, to tailor your experience, and to keep you logged in if you register.
By continuing to use this site, you are consenting to our use of cookies.

Learn more… Accept Remind me later

Welcome to VaultWiki.org, home of the wiki add-on for vBulletin and XenForo!

Issue: Deadlock found when trying to get lock

Issue Tools