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

1 files changed, 41 insertions, 38 deletions

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);
+    }
 
 }