[Performance]Cache whether a DB supports transaction level (#18691)

* add check

add check

* Update TransactionLevelTest.php

update tests to fit different mysql version

* built vue files

* cs/ws

* move supportsUncommitted to db level

move supportsUncommitted to db level

* Update Db.php

append $supportsUncommitted to global Db

* Update Mysql.php

append supportsUncommitted

* Update Mysqli.php

append to mysqli supportsUncommitted

* Update TransactionLevel.php

update function

* Update TransactionLevel.php

update transaction

* Update TransactionLevel.php

revert back to previous change

* Update TransactionLevel.php

update cache level key

* Update TransactionLevel.php

update cache

* Update TransactionLevel.php

update supportsUncommitted

* Update TransactionLevel.php

add some comments

* Update core/Db/TransactionLevel.php

Co-authored-by: Stefan Giehl <stefan@matomo.org>

* remove $supportsUncommitted reset

remove $supportsUncommitted reset

Co-authored-by: peterhashair <peterhashair@users.noreply.github.com>
Co-authored-by: sgiehl <stefan@matomo.org>

6 files changed, 69 insertions, 41 deletions

diff --git a/core/Db.php b/core/Db.php
index 23d0a493b5..d3df9003ed 100644
--- a/core/Db.php
+++ b/core/Db.php
@@ -40,6 +40,8 @@ class Db
 
     private static $logQueries = true;
 
+    // this is used for indicate TransactionLevel Cache
+    public $supportsUncommitted;
     /**
      * Returns the database connection and creates it if it hasn't been already.
      *
diff --git a/core/Db/Adapter/Mysqli.php b/core/Db/Adapter/Mysqli.php
index 87f91ac32f..a46236c570 100644
--- a/core/Db/Adapter/Mysqli.php
+++ b/core/Db/Adapter/Mysqli.php
@@ -25,6 +25,10 @@ class Mysqli extends Zend_Db_Adapter_Mysqli implements AdapterInterface
      *
      * @param array|Zend_Config $config database configuration
      */
+
+    // this is used for indicate TransactionLevel Cache
+    public $supportsUncommitted;
+
     public function __construct($config)
     {
         // Enable LOAD DATA INFILE
diff --git a/core/Db/Adapter/Pdo/Mysql.php b/core/Db/Adapter/Pdo/Mysql.php
index 1170afe3f5..adbdaa6908 100644
--- a/core/Db/Adapter/Pdo/Mysql.php
+++ b/core/Db/Adapter/Pdo/Mysql.php
@@ -29,6 +29,10 @@ class Mysql extends Zend_Db_Adapter_Pdo_Mysql implements AdapterInterface
      *
      * @param array|Zend_Config $config database configuration
      */
+    
+    // this is used for indicate TransactionLevel Cache
+    public $supportsUncommitted;
+
     public function __construct($config)
     {
         // Enable LOAD DATA INFILE
diff --git a/core/Db/TransactionLevel.php b/core/Db/TransactionLevel.php
index c19699084c..f087f4adcd 100644
--- a/core/Db/TransactionLevel.php
+++ b/core/Db/TransactionLevel.php
@@ -40,12 +40,18 @@ class TransactionLevel
 
     public function setUncommitted()
     {
+        if ($this->db->supportsUncommitted === false) {
+            // we know "Uncommitted" transaction level is not supported, we don't need to do anything as it won't work to set the status
+            return false;
+        }
+
         try {
             $backup = $this->db->fetchOne('SELECT @@TX_ISOLATION');
         } catch (\Exception $e) {
             try {
                 $backup = $this->db->fetchOne('SELECT @@transaction_isolation');
             } catch (\Exception $e) {
+                $this->db->supportsUncommitted = false;
                 return false;
             }
         }
@@ -54,9 +60,17 @@ class TransactionLevel
             $this->db->query('SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED');
             $this->statusBackup = $backup;
 
-            Option::set(self::TEST_OPTION_NAME, '1'); // try setting something w/ the new transaction isolation level
+            if ($this->db->supportsUncommitted === null) {
+                // the first time we need to check if the transaction level actually works by
+                // trying to set something w/ the new transaction isolation level
+                Option::set(self::TEST_OPTION_NAME, '1');
+            }
+
+            $this->db->supportsUncommitted = true;
         } catch (\Exception $e) {
+            // setting the transaction level status did not work
             // catch eg 1665 Cannot execute statement: impossible to write to binary log since BINLOG_FORMAT = STATEMENT and at least one table uses a storage engine limited to row-based logging. InnoDB is limited to row-logging when transaction isolation level is READ COMMITTED or READ UNCOMMITTED
+            $this->db->supportsUncommitted = false;
             $this->restorePreviousStatus();
             return false;
         }
@@ -77,7 +91,5 @@ class TransactionLevel
                 $this->db->query('SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ');
             }
         }
-
     }
-
 }
diff --git a/core/Tracker/Db.php b/core/Tracker/Db.php
index 22a9173a8f..a6bce56a76 100644
--- a/core/Tracker/Db.php
+++ b/core/Tracker/Db.php
@@ -30,6 +30,9 @@ abstract class Db
 
     protected $connection = null;
 
+    // this is used for indicate TransactionLevel Cache
+    public $supportsUncommitted = null;
+
     /**
      * Enables the SQL profiling.
      * For each query, saves in the DB the time spent on this query.
diff --git a/tests/PHPUnit/Integration/Db/TransactionLevelTest.php b/tests/PHPUnit/Integration/Db/TransactionLevelTest.php
index 129526e058..28dcb38382 100644
--- a/tests/PHPUnit/Integration/Db/TransactionLevelTest.php
+++ b/tests/PHPUnit/Integration/Db/TransactionLevelTest.php
@@ -2,7 +2,7 @@
 /**
  * Matomo - free/libre analytics platform
  *
- * @link https://matomo.org
+ * @link    https://matomo.org
  * @license http://www.gnu.org/licenses/gpl-3.0.html GPL v3 or later
  */
 
@@ -13,48 +13,51 @@ use Piwik\Db\TransactionLevel;
 use Piwik\Tests\Framework\TestCase\IntegrationTestCase;
 
 /**
- * @group Funnels
  * @group TransactionLevelTest
  * @group TransactionLevel
  * @group Plugins
  */
 class TransactionLevelTest extends IntegrationTestCase
 {
-	/**
-	 * @var TransactionLevel
-	 */
-	private $level;
-
-	/**
-	 * @var \Piwik\Tracker\Db|\Piwik\Db\AdapterInterface|\Piwik\Db $db
-	 */
-	private $db;
-
-	public function setUp(): void
-	{
-		parent::setUp();
-		$this->db = Db::get();
-		$this->level = new TransactionLevel($this->db);
-	}
-
-	public function test_canLikelySetTransactionLevel()
-	{
-		$this->assertTrue($this->level->canLikelySetTransactionLevel());
-	}
-
-	public function test_setUncommitted_restorePreviousStatus()
-	{
-		$value = $this->db->fetchOne('SELECT @@TX_ISOLATION');
-		$this->assertSame('REPEATABLE-READ', $value);
-
-		$this->level->setUncommitted();
-		$value = $this->db->fetchOne('SELECT @@TX_ISOLATION');
-
-		$this->assertSame('READ-UNCOMMITTED', $value);
-		$this->level->restorePreviousStatus();
-
-		$value = $this->db->fetchOne('SELECT @@TX_ISOLATION');
-		$this->assertSame('REPEATABLE-READ', $value);
-	}
+    /**
+     * @var TransactionLevel
+     */
+    private $level;
+
+    /**
+     * @var \Piwik\Tracker\Db|\Piwik\Db\AdapterInterface|\Piwik\Db $db
+     */
+    private $db;
+
+    public function setUp(): void
+    {
+        parent::setUp();
+        $this->db    = Db::get();
+        $this->level = new TransactionLevel($this->db);
+    }
+
+    public function test_canLikelySetTransactionLevel()
+    {
+        $this->assertTrue($this->level->canLikelySetTransactionLevel());
+    }
+
+    public function test_setUncommitted_restorePreviousStatus()
+    {
+        // mysql 8.0 using transaction_isolation
+        $isolation = $this->db->fetchOne("SHOW GLOBAL VARIABLES LIKE 't%_isolation'");
+        $isolation = "@@" . $isolation;
+
+        $value = $this->db->fetchOne('SELECT ' . $isolation);
+        $this->assertSame('REPEATABLE-READ', $value);
+
+        $this->level->setUncommitted();
+        $value = $this->db->fetchOne('SELECT ' . $isolation);
+
+        $this->assertSame('READ-UNCOMMITTED', $value);
+        $this->level->restorePreviousStatus();
+
+        $value = $this->db->fetchOne('SELECT ' . $isolation);
+        $this->assertSame('REPEATABLE-READ', $value);
+    }
 
 }