mySQL - Set isolation level using PHP's mysqli

Question

How do I set the isolation level of a transaction to 'SERIALIZABLE' in PHP using mysqli? I have looked everywhere and I can't find any information on it.

Here is an explanation of the isolation levels.

Prehension

Answer 1

You can just set the isolation level in a query before you run your statements. This assume that you do everything using the same session:

$mysqli = new mysqli('localhost', 'user', 'pass', 'db');
$mysqli->query("SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE");
...

You may also want to turn off autocommit before hand since it changes the way serializable isolation works.

Holleran

Is there some more information regarding the 'You may also want to turn off autocommit before hand since it changes the way serializable isolation works.' comment? – Bornie

dba.stackexchange.com/questions/175550/… – Jink

Answer 2

Short answer:

$mysqli = new mysqli('localhost', 'user', 'pass', 'db');
$mysqli->query("SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE");

Long Answer: Unless you use the SESSION or GLOBAL modifier, setting the transaction level will only apply to the very next query.

tldr;

According to the MySQL documentation with InnoDB tables:

Without any SESSION or GLOBAL keyword:

The statement applies only to the next single transaction performed within the session.

Subsequent transactions revert to using the session value of the named characteristics.

The statement is not permitted within transactions

Note that setting the GLOBAL flag will affect all subsequent queries Existing sessions will not be affected.

Jink