From 948d505d0d300e2f1035f75fead028b6bd79b2e1 Mon Sep 17 00:00:00 2001
From: =?utf8?q?Tim=20D=C3=BCsterhus?= <duesterhus@woltlab.com>
Date: Fri, 27 Mar 2015 22:52:05 +0100
Subject: [PATCH] Improve UserStorageHandler::shutdown()

1. Better ordering of queries (REPLACE INTO is DELETE + INSERT),
   previous solution in 8518f3636eae69065e52ff7c75208ed34fa6f87c
   was insufficient
2. Retry failed transaction up to two times.
---
 .../user/storage/UserStorageHandler.class.php | 121 +++++++++++-------
 1 file changed, 73 insertions(+), 48 deletions(-)

diff --git a/wcfsetup/install/files/lib/system/user/storage/UserStorageHandler.class.php b/wcfsetup/install/files/lib/system/user/storage/UserStorageHandler.class.php
index de3c8617ff..41ffb91b5b 100644
--- a/wcfsetup/install/files/lib/system/user/storage/UserStorageHandler.class.php
+++ b/wcfsetup/install/files/lib/system/user/storage/UserStorageHandler.class.php
@@ -1,6 +1,7 @@
 <?php
 namespace wcf\system\user\storage;
 use wcf\system\database\util\PreparedStatementConditionBuilder;
+use wcf\system\exception\SystemException;
 use wcf\system\SingletonFactory;
 use wcf\system\WCF;
 
@@ -128,7 +129,7 @@ class UserStorageHandler extends SingletonFactory {
 	 */
 	public function update($userID, $field, $fieldValue) {
 		$this->updateFields[$userID][$field] = $fieldValue;
-		
+
 		// update data cache for given user
 		if (isset($this->cache[$userID])) {
 			$this->cache[$userID][$field] = $fieldValue;
@@ -175,68 +176,92 @@ class UserStorageHandler extends SingletonFactory {
 	 * Removes and inserts data records on shutdown.
 	 */
 	public function shutdown() {
-		WCF::getDB()->beginTransaction();
+		$toReset = array();
 		
 		// remove outdated entries
-		if (!empty($this->resetFields)) {
-			$sql = "DELETE FROM	wcf".WCF_N."_user_storage
-				WHERE		userID = ?
-						AND field = ?";
-			$statement = WCF::getDB()->prepareStatement($sql);
-			
-			ksort($this->resetFields, SORT_NATURAL);
-			
-			foreach ($this->resetFields as $userID => $fields) {
-				foreach ($fields as $field) {
-					$statement->execute(array(
-						$userID,
-						$field
-					));
-				}
+		foreach ($this->resetFields as $userID => $fields) {
+			foreach ($fields as $field) {
+				if (!isset($toReset[$field])) $toReset[$field] = array();
+				$toReset[$field][] = $userID;
+			}
+		}
+		foreach ($this->updateFields as $userID => $fieldValues) {
+			foreach ($fieldValues as $field => $fieldValue) {
+				if (!isset($toReset[$field])) $toReset[$field] = array();
+				$toReset[$field][] = $userID;
 			}
 		}
+		ksort($toReset);
 		
-		// insert data
-		if (!empty($this->updateFields)) {
-			// exclude values which should be resetted
-			foreach ($this->updateFields as $userID => $fieldValues) {
-				if (isset($this->resetFields[$userID])) {
-					foreach ($fieldValues as $field => $fieldValue) {
-						if (in_array($field, $this->resetFields[$userID])) {
-							unset($this->updateFields[$userID][$field]);
-						}
-					}
-					
-					if (empty($this->updateFields[$userID])) {
-						unset($this->updateFields[$userID]);
+		// exclude values which should be resetted
+		foreach ($this->updateFields as $userID => $fieldValues) {
+			if (isset($this->resetFields[$userID])) {
+				foreach ($fieldValues as $field => $fieldValue) {
+					if (in_array($field, $this->resetFields[$userID])) {
+						unset($this->updateFields[$userID][$field]);
 					}
 				}
+				
+				if (empty($this->updateFields[$userID])) {
+					unset($this->updateFields[$userID]);
+				}
 			}
-			
-			if (!empty($this->updateFields)) {
-				$sql = "REPLACE INTO	wcf".WCF_N."_user_storage
-							(userID, field, fieldValue)
-					VALUES		(?, ?, ?)";
-				$statement = WCF::getDB()->prepareStatement($sql);
+		}
+		ksort($this->updateFields);
+		
+		$i = 0;
+		while (true) {
+			try {
+				WCF::getDB()->beginTransaction();
 				
-				ksort($this->updateFields, SORT_NATURAL);
+				// reset data
+				foreach ($toReset as $field => $userIDs) {
+					sort($userIDs);
+					$conditions = new PreparedStatementConditionBuilder();
+					$conditions->add("userID IN (?)", array($userIDs));
+					$conditions->add("field = ?", array($field));
+
+					$sql = "DELETE FROM	wcf".WCF_N."_user_storage
+						".$conditions;
+					$statement = WCF::getDB()->prepareStatement($sql);
+					$statement->execute($conditions->getParameters());
+				}
 				
-				foreach ($this->updateFields as $userID => $fieldValues) {
-					ksort($fieldValues, SORT_STRING);
+				// insert data
+				if (!empty($this->updateFields)) {
+					$sql = "INSERT INTO	wcf".WCF_N."_user_storage
+								(userID, field, fieldValue)
+						VALUES		(?, ?, ?)";
+					$statement = WCF::getDB()->prepareStatement($sql);
 					
-					foreach ($fieldValues as $field => $fieldValue) {
-						$statement->execute(array(
-							$userID,
-							$field,
-							$fieldValue
-						));
+					foreach ($this->updateFields as $userID => $fieldValues) {
+						ksort($fieldValues);
+						
+						foreach ($fieldValues as $field => $fieldValue) {
+							$statement->execute(array(
+								$userID,
+								$field,
+								$fieldValue
+							));
+						}
 					}
 				}
+				
+				WCF::getDB()->commitTransaction();
+				break;
+			}
+			catch(SystemException $e) {
+				WCF::getDB()->rollbackTransaction();
+				
+				// retry up to 2 times
+				if (++$i === 2) {
+					$e->getExceptionID();
+					break;
+				}
+				
+				usleep(mt_rand(0, .1e6)); // 0 to .1 seconds
 			}
 		}
-		
-		WCF::getDB()->commitTransaction();
-		
 		$this->resetFields = $this->updateFields = array();
 	}
 	
-- 
2.20.1