CODEWITHSUNDEEP
mysqltransactionsserializableisolation-level
endyey Es
endyey Es

Reputation: 509

Isolation Level using mySQL

I just don't get how I can form this query.

Question:

How can I query for Isolation Level using MySQL?

Is it supposed to be like the following?

BEGIN;
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
START TRANSACTION;

Select ....

COMMIT;

Upvotes: 5

Views: 8921

Answers (2)

JayRizzo
JayRizzo

Reputation: 3636

Question #1: How can I query for Isolation Level using MySQL?

MySQL 5.X

SELECT @@GLOBAL.TX_ISOLATION;  -- SERVER DEFAULT SETTING
SELECT @@SESSION.TX_ISOLATION; -- CURRENT SESSION SERVER SETTING

MySQL 8.X

SELECT @@GLOBAL.TRANSACTION_ISOLATION;  -- SERVER DEFAULT SETTING
SELECT @@SESSION.TRANSACTION_ISOLATION; -- CURRENT SESSION SERVER SETTING

Note: I was unable to capture the Isolation change during the transaction as the docs notate:

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

Question #2: How can I use this?

-- Capture your autocommit setting from your session.
SET @var_AC = (SELECT @@SESSION.AUTOCOMMIT);
-- SELECT @var_AC;

SET AUTOCOMMIT = 0; -- FORCE AUTOCOMMIT OFF
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; 
-- SET YOUR ISOLATION LEVEL FOR THE NEXT TRANSACTION

START TRANSACTION;
-- -- ADD YOUR CODE HERE
COMMIT;
-- IF NEEDED REVERT YOUR AUTOCOMMIT BACK TO WHAT IT WAS BEFORE
SET AUTOCOMMIT = @var_AC;

You can also change this on the Server Defaults or your current session like so:

SET @@GLOBAL.TRANSACTION_ISOLATION = 'transaction_characteristic'; -- Change for all FUTURE sessions.
SET @@SESSION.TRANSACTION_ISOLATION = 'transaction_characteristic'; -- Change for the Current Session.
SET @@TRANSACTION_ISOLATION = 'transaction_characteristic'; -- Change the next transaction setting.

More info on Transactions, Isolation Levels, & how they work.

NOTE: Within all stored programs, the parser treats BEGIN [WORK] as the beginning of a BEGIN ... END block.

To begin a transaction in this context, use START TRANSACTION instead.

Upvotes: 0

Gobea Alcoba Gonzalo
Gobea Alcoba Gonzalo

Reputation: 59

First, you should set autocommit to 0 to run the commit at the end:

SET AUTOCOMMIT = 0;

Later, you should set the isolation level: (For more information about isolation levels read https://dev.mysql.com/doc/refman/5.0/en/set-transaction.html)

SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;

Finally develop the transaction:

START TRANSACTION;
Select ....
/* all queries that you want */
COMMIT;

Regards.

Upvotes: 4

Related Questions