# Part 1
# on the existing In-Portal installation:
# go to `.../core/install.php` URL
# confirm, that the "Installation Maintenance" step is shown (if perform the recommended steps shown on the screen)
# enter the login/password of the "root" user
# select the "Update Database Configuration" option
# press the "Continue" button
# confirm, that these new settings are now shown on the configuration screen and they're not empty:
* Database Error Backoff Max Retry Attempts
* Database Error Backoff Logic Base Time (in milliseconds)
* Database Lock Retry Debugging Enabled
# specify empty values for "Database Error Backoff*" settings
# press the "Continue" button
# confirm, that the validation error was shown
# change values for all of the settings to non-empty values
# press the "Continue" button
# press ENTER in the Web Browser Location field
# confirm, that the "Installation Maintenance" step is shown
# enter the login/password of the "root" user
# select the "Update Database Configuration" option
# press the "Continue" button
# confirm, that previously specified settings retained their new values
# Part 2
# try installing a clean In-Portal
# confirm, that these new settings are now shown on the "Database Configuration" step and they're not empty:
* Database Error Backoff Max Retry Attempts
* Database Error Backoff Logic Base Time (in milliseconds)
* Database Lock Retry Debugging Enabled
# specify empty values for "Database Error Backoff*" settings
# press the "Continue" button
# confirm, that the validation error was shown
# change values for all of the settings to non-empty values
# press the "Continue" button
# press ENTER in the Web Browser Location field
# confirm, that the "System Requirements Check" step is shown
# follow the installation instructions until you reach the "Database Configuration" step
# confirm, that previously specified settings retained their new values
# Part 3
# execute `SET GLOBAL innodb_lock_wait_timeout = 10` database query by MySQL user with a SUPER privilege (e.g. `root`)
# change `LanguageLabels` engine type to `InnoDB`
# in the PhpStorm confirmation ensure, that the "Max. simultaneous connections" setting is 2 or larger
# create the `/dl_test.php` file as a copy from the `/index.php` file
# in the "/dl_test.php" file replace `$application->Run();` line with this code:
```
lang=php
// Lock.
if ( $application->GetVar('user') === 'a' ) {
$application->Conn->Query('START TRANSACTION'); // Step 1.
$application->Conn->Query('SELECT * FROM ' . TABLE_PREFIX . 'LanguageLabels WHERE PhraseId = 1 LOCK IN SHARE MODE'); // Step 2.
$application->Conn->Query('DELETE FROM ' . TABLE_PREFIX . 'LanguageLabels WHERE PhraseId = 1'); // Step 5.
}
elseif ( $application->GetVar('user') === 'b' ) {
$application->Conn->Query('START TRANSACTION'); // Step 3.
$application->Conn->Query('DELETE FROM ' . TABLE_PREFIX . 'LanguageLabels WHERE PhraseId = 1'); // Step 4.
}
// Deadlock.
if ( $application->GetVar('user') === 'c' ) {
// the "user a" start the process
$application->Conn->Query('START TRANSACTION'); // Step 1.
$application->Conn->Query('UPDATE ' . TABLE_PREFIX . 'LanguageLabels SET l1_Translation = "Active C" WHERE PhraseId = 1'); // Step 3.
$application->Conn->Query('UPDATE ' . TABLE_PREFIX . 'LanguageLabels SET l1_Translation = "Add C" WHERE PhraseId = 2'); // Step 5.
}
elseif ( $application->GetVar('user') === 'd' ) {
$application->Conn->Query('START TRANSACTION'); // Step 2.
$application->Conn->Query('UPDATE ' . TABLE_PREFIX . 'LanguageLabels SET l1_Translation = "Add D" WHERE PhraseId = 2'); // Step 4.
$application->Conn->Query('UPDATE ' . TABLE_PREFIX . 'LanguageLabels SET l1_Translation = "Active D" WHERE PhraseId = 1'); // Step 6 = Deadlock.
}
```
# regular lock retries
# enable debug mode in the Web Browser
# enable the "DBEnableLockRetryDebugging" setting in the `/system/config.php`
# place breakpoint inside each IF statement of the `dl_test.php` file
# open the `/dl_test.php?user=a` in 1st tab
# open the `/dl_test.php?user=b` in 2nd tab
# execute code line by line from the breakpoint as shown in comments (e.g. "Step 1" and "Step 2" in 1st debug tab of PhpStorm, then switch to 2nd debug tab of PhpStorm and execute "Step 3" and so on)
# confirm, that:
* a regular lock happened (wait time can be 10 seconds on each retry)
* after all scripts are finished detailed lock retry info is shown in the system log
# disable the "DBEnableLockRetryDebugging" setting in the `/system/config.php`
# repeat the test, but this time no records about lock retries should be made
# deadlock retries
# enable debug mode in the Web Browser
# enable the "DBEnableLockRetryDebugging" setting in the `/system/config.php`
# place breakpoint inside each IF statement of the `dl_test.php` file
# open the `/dl_test.php?user=c` in 1st tab
# open the `/dl_test.php?user=d` in 2nd tab
# execute code line by line from the breakpoint as shown in comments (e.g. "Step 1" in 1st debug tab of PhpStorm, then switch to 2nd debug tab of PhpStorm and execute "Step 2" and so on)
# confirm, that:
* a regular lock happened
* after all scripts are finished detailed lock retry info is shown in the system log
# disable the "DBEnableLockRetryDebugging" setting in the `/system/config.php`
# repeat the test, but this time no records about lock retries should be made# Part 1 - existing install new settings testing
# on the existing In-Portal installation:
# go to `.../core/install.php` URL
# confirm, that the "Installation Maintenance" step is shown (if perform the recommended steps shown on the screen)
# enter the login/password of the "root" user
# select the "Update Database Configuration" option
# press the "Continue" button
# confirm, that these new settings are now shown on the configuration screen and they're not empty:
* Database Error Backoff Max Retry Attempts
* Database Error Backoff Logic Base Time (in milliseconds)
* Database Lock Retry Debugging Enabled
# specify empty values for "Database Error Backoff*" settings
# press the "Continue" button
# confirm, that the validation error was shown
# change values for all of the settings to non-empty values
# press the "Continue" button
# press ENTER in the Web Browser Location field
# confirm, that the "Installation Maintenance" step is shown
# enter the login/password of the "root" user
# select the "Update Database Configuration" option
# press the "Continue" button
# confirm, that previously specified settings retained their new values
# Part 2 - new install new settings testing
# try installing a clean In-Portal
# confirm, that these new settings are now shown on the "Database Configuration" step and they're not empty:
* Database Error Backoff Max Retry Attempts
* Database Error Backoff Logic Base Time (in milliseconds)
* Database Lock Retry Debugging Enabled
# specify empty values for "Database Error Backoff*" settings
# press the "Continue" button
# confirm, that the validation error was shown
# change values for all of the settings to non-empty values
# press the "Continue" button
# press ENTER in the Web Browser Location field
# confirm, that the "System Requirements Check" step is shown
# follow the installation instructions until you reach the "Database Configuration" step
# confirm, that previously specified settings retained their new values
# Part 3 - preparations
# in the Admin Console:
* go to the {nav Configuration > Website > Advanced} section
* specify e-mail in the `Send User-defined "System Log" messages to` system setting
* select `Immediate` option in the `Email Delivery` system setting
* save changes
# in the phpMyAdmin (or other database client):
* login with a user with a SUPER privilege (e.g. `root`)
* execute `SET GLOBAL innodb_lock_wait_timeout = 10` database query
* change `LanguageLabels` engine type to `InnoDB`
# in the PhpStorm:
* create the `/dl_test.php` file as a copy from the `/index.php` file
* in the "/dl_test.php" file replace `$application->Run();` line with this code:
* in the `/core/units/logs/system_logs/system_logs_config.php` file change declaration of the `LogTimestamp` field to include `'time_format' => 'H:i:s',`
```
lang=php
$user = $application->GetVar('user');
if ( $user === false ) {
throw new LogicException('Please specify the "user" request variable.');
}
$cookie_flag_name = 'user_' . $user . '_window_opened';
if ( !array_key_exists($cookie_flag_name, $_COOKIE) ) {
echo '<br/><br/>Ready for reload.';
setcookie($cookie_flag_name, 1);
exit;
}
// Lock.
if ( $user === 'a' ) {
$application->Conn->Query('START TRANSACTION'); // Step 1.
$application->Conn->Query('SELECT * FROM ' . TABLE_PREFIX . 'LanguageLabels WHERE PhraseId = 1 LOCK IN SHARE MODE'); // Step 2.
sleep(2 * 60); // delay to keep transaction open and ensure lock presence in "user=b" process.
}
elseif ( $user === 'b' ) {
sleep(5); // delay to allow "step 1" and "step 2" from "user=a" process to execute.
$application->Conn->Query('START TRANSACTION'); // Step 3.
/*
* Lock starts at below line, because we're attempting to delete
* a record, that was selected within a transaction in another
* process.
*
* InnoDB will wait 10 seconds in hopes, that lock can be acquired
* and then throws an error upon which we issue a delay and retry a
* query.
*
* Each retry is logged into a System Log. Wait time with default
* configuration:
* 1. initial query lock waiting: 10 sec
* 2. 1st retry delay: (100*2^1)ms = 200ms
* 3. waiting for lock of 1st retry: 10 sec
* 4. 2nd retry delay: (100*2^2)ms = 400ms
* 5. waiting for lock of 2nd retry: 10 sec
* 6. 3rd retry delay: (100*2^3)ms = 800ms
* 7. waiting for lock of 3rd retry: 10 sec
*
* Total wait time: 41 sec 400 ms + 5 seconds initial delay for user=b.
*/
$application->Conn->Query('DELETE FROM ' . TABLE_PREFIX . 'LanguageLabels WHERE PhraseId = 1'); // Step 4.
}
// Deadlock.
if ( $user === 'c' ) {
// the "user a" start the process
$application->Conn->Query('START TRANSACTION'); // Step 1.
$application->Conn->Query('UPDATE ' . TABLE_PREFIX . 'LanguageLabels SET l1_Translation = "Active C" WHERE PhraseId = 1'); // Step 3.
$application->Conn->Query('UPDATE ' . TABLE_PREFIX . 'LanguageLabels SET l1_Translation = "Add C" WHERE PhraseId = 2'); // Step 5.
}
elseif ( $user === 'd' ) {
$application->Conn->Query('START TRANSACTION'); // Step 2.
$application->Conn->Query('UPDATE ' . TABLE_PREFIX . 'LanguageLabels SET l1_Translation = "Add D" WHERE PhraseId = 2'); // Step 4.
$application->Conn->Query('UPDATE ' . TABLE_PREFIX . 'LanguageLabels SET l1_Translation = "Active D" WHERE PhraseId = 1'); // Step 6 = Deadlock.
}
```
# Part 4 - regular lock retries
# in the PhpStorm:
# enable the "DBEnableLockRetryDebugging" setting in the `/system/config.php` file
# in the Web Browser
# delete the `user_a_window_opened` and `user_b_window_opened` cookies
# open the `/dl_test.php?user=a` in 1st tab
# confirm, that page showed `Ready for reload.`
# open the `/dl_test.php?user=b` in 2nd tab
# confirm, that page showed `Ready for reload.`
# select both tabs and via context menu (right clicking) reload both of them simultaneously
# wait 2.5 minutes maximum for both tab to finish loading
# confirm, that:
* in Web Browser:
* 1st tab:
* finished loading in approximately 2 minutes
* has nothing shown except debugger toolbar
* 2nd tab:
* finished loading in approximately 1 minute
* shows debugger with an `Lock wait timeout exceeded; try restarting transaction (1205)` SQL error about `DELETE FROM LanguageLabels WHERE PhraseId = 1` database query
* in the {nav Logs & Reports > System Log} section of the Admin Console:
* when executing script with `DBEnableLockRetryDebugging` setting enabled:
* 4 records were added with `Lock wait timeout exceeded; try restarting transaction` message
* each record was added with approximately 10 seconds delay
* 1st, 2nd and 3rd record represent errored query retry attempts and they show delay and retry attempt number in the `User Data` field of the system log record
* 4th record shows just an error without retry information
* when executing script with `DBEnableLockRetryDebugging` setting disabled:
* 1 record was added with `Lock wait timeout exceeded; try restarting transaction` message and no retry information
# disable the "DBEnableLockRetryDebugging" setting in the `/system/config.php`
# repeat the test, but this time no records about lock retries should be made
# Part 5 - deadlock retries (**incomplete**)
# enable debug mode in the Web Browser
# enable the "DBEnableLockRetryDebugging" setting in the `/system/config.php`
# place breakpoint inside each IF statement of the `dl_test.php` file
# open the `/dl_test.php?user=c` in 1st tab
# open the `/dl_test.php?user=d` in 2nd tab
# execute code line by line from the breakpoint as shown in comments (e.g. "Step 1" in 1st debug tab of PhpStorm, then switch to 2nd debug tab of PhpStorm and execute "Step 2" and so on)
# confirm, that:
* a regular lock happened
* after all scripts are finished detailed lock retry info is shown in the system log
# disable the "DBEnableLockRetryDebugging" setting in the `/system/config.php`
# repeat the test, but this time no records about lock retries should be made
# Part 6 - cleaning up
# execute `SET GLOBAL innodb_lock_wait_timeout = 50` database query by MySQL user with a SUPER privilege (e.g. `root`)