Issue: Many Deadlock errors after upgrade

Issue Tools
- View Changes

4 Weeks Ago 9:57 AM

gerryvz

New Member

Many Deadlock errors after upgrade

After updating the code, the Vaultwiki was operating OK.

However, it immediately started throwing these errors every few minutes.

XF\Db\DeadlockException: MySQL query error [1213]: Deadlock found when trying to get lock; try restarting transaction src/XF/Db/AbstractStatement.php:230

Generated by: Unknown account Feb 23, 2025 at 11:46 AM

Stack trace

INSERT IGNORE INTO `xf_vw_accelerate` (`entry_type`, `entry_name`, `entry_content`) VALUES ('file','/var/www/html/500eboard.co/public_html/forums/src/addons/vw/vw/_core/controller/request/xf2.php','1'),
('file','/var/www/html/500eboard.co/public_html/forums/src/addons/vw/vw/_core/controller/request/vw.php','1'),
('file','/var/www/html/500eboard.co/public_html/forums/src/addons/vw/vw/_core/model/shutdown/xf2.php','1'),
('file','/var/www/html/500eboard.co/public_html/forums/src/addons/vw/vw/_core/model/shutdown/vw.php','1'),
('file','/var/www/html/500eboard.co/public_html/forums/src/addons/vw/vw/_core/model/lock/vw.php','1')
------------

#0 src/XF/Db/Mysqli/Statement.php(198): XF\Db\AbstractStatement->getException('MySQL query err...', 1213, '40001')
#1 src/XF/Db/Mysqli/Statement.php(78): XF\Db\Mysqli\Statement->getException('MySQL query err...', 1213, '40001')
#2 src/XF/Db/AbstractAdapter.php(96): XF\Db\Mysqli\Statement->execute()
#3 src/addons/vw/vw/_core/controller/db/xf2.php(118): XF\Db\AbstractAdapter->query('INSERT IGNORE ...')
#4 src/addons/vw/vw/_core/model/db/mysql/vw.php(1454): vw_DB_Controller_XF2->query_write('INSERT IGNORE ...')
#5 src/addons/vw/vw/_core/model/db/mysql/vw.php(1122): vw_DB_MySQL_Model->shutdown_or_run('INSERT IGNORE ...', false)
#6 src/addons/vw/vw/_core/model/shutdown/vw.php(143): vw_DB_MySQL_Model->insert(Array)
#7 src/addons/vw/vw/_core/model/shutdown/xf2.php(70): vw_Shutdown_Model->shutdown()
#8 src/addons/vw/vw/Listener.php(341): vw_Shutdown_Model_XF2->shutdown()
#9 src/XF/Extension.php(81): vw\vw\Listener::app_complete(Object(XF\Pub\App), Object(XF\Http\Response))
#10 src/XF/App.php(3020): XF\Extension->fire('app_complete', Array, NULL)
#11 src/XF/App.php(2251): XF\App->fire('app_complete', Array)
#12 src/XF/Pub/App.php(456): XF\App->complete(Object(XF\Http\Response))
#13 src/XF/App.php(2516): XF\Pub\App->complete(Object(XF\Http\Response))
#14 src/XF.php(524): XF\App->run()
#15 index.php(20): XF::runApp('XF\\Pub\\App')
#16 {main}

Request state

array(4) {
["url"] => string(69) "/forums/attachments/15234827365558af70779a54d500e_silver_08-jpg.67249"
["referrer"] => bool(false)
["_GET"] => array(0) {
}
["_POST"] => array(0) {
}
}

Issue Details

Issue Number 6470

Issue Type Bug

Project VaultWiki 4.x Series

Category General

Status Fixed

Priority 2 - Fatal / Database Errors

Affected Version 4.1.8

Fixed Version Next Release

Milestone (none)

Software DependencyAny

License TypePaid

Users able to reproduce bug 0

Users unable to reproduce bug 0

Attachments 0

Assigned Users (none)

Tags (none)

4 Weeks Ago 9:58 AM

gerryvz

New Member

This is with VW 4.1.8 PL2 with suggested file change, running on XenForo 2.2.17

Database is MySQL 8.0.33

Reply

4 Weeks Ago 11:17 AM

pegasus

VaultWiki Team

This has been a problem for a long time. The error occurs when multiple users trigger a bulk INSERT IGNORE at the same time, if the users are trying to insert the same row. This tends to happen with cache entries, because users share cache. Cache tends to rebuild immediately following an upgrade. Errors related to cache building should resolve over time, since the cache will be there and users won't be trying to build it anymore.

As we did in recent versions for REPLACE, in the next version we are already scheduled to retry bulk INSERT queries that fail due to deadlock, as non-bulk instead, and if the deadlock continues, to ignore deadlock errors that occur during cache updates.

In src/addons/vw/vw/_core/model/db/mysql/vw.php, find:

Code:

	public function insert($input)
	{
		$data = array(
			'fields' => '',
			'values' => ''
		);

Replace with:

Code:

	public function insert($input)
	{
		$data = array(
			'fields' => '',
			'values' => '',
			'valueList' => array()
		);

Find:

Code:

		return $this->shutdown_or_run($query, isset($input['shutdown']));
	}

	protected function construct_insert_string($data)

Replace with:

Code:

		vw_DB::get($this->key)->func()->hide_errors();
		$retval = $this->shutdown_or_run($query);
		vw_DB::get($this->key)->func()->show_errors();

		if ($retval !== false)
		{
			return $retval;
		}
		else if (!empty($input['isRetry']) OR count($data['valueList']) > 1)
		{
			$errno = vw_DB::get($this->key)->func()->errno();
			$retries = 0;

			switch ($errno)
			{
				case 1205:
					$retries = 1;
					break;

				case 1213:
					$retries = 5;
					break;
			}

			switch ($errno)
			{
				case 1205:
				case 1213:
				{
					static $i = 0;

					if ($i < $retries)
					{
						$i++;

						// deadlock, retry, but one row at a time
						foreach ($input['values'] as $set)
						{
							$this->insert(array(
								'values' => array($set),
								'isRetry' => 1
							) + $input);
						}

						$i--;

						return true;
					}
					else if (!empty($input['inShutdown']))
					{
						return false;
					}
				}
				break;
			}
		}

		if (vw_DB::get($this->key)->func()->should_error())
		{
			$error = "Database Error:\n\n";
			$error .= $query . "\n\n";
			$error .= vw_DB::get($this->key)->func()->error();

			vw_DB::get($this->key)->func()->halt($error);
		}
		return false;
	}

	protected function construct_insert_string($data)

Find:

Code:

return array(
			'fields' => implode(', ', $fields),
			'values' => implode(",\n\t", $output)
		);

Replace with:

Code:

return array(
			'fields' => implode(', ', $fields),
			'values' => implode(",\n\t", $output),
			'valueList' => $output
		);

This code has been running live on this site for almost a month and I have not seen any new deadlocks during INSERTs in that time. I have seen 1 or 2 deadlocks during cache DELETEs only, and am working on a solution for that.

+ Reply

All times are GMT -4. The time now is 1:18 PM.

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: Many Deadlock errors after upgrade

Issue Tools