[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>

5 files changed, 28 insertions, 3 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.