MySQL - SET TRANSACTION Statement

The SET TRANSACTION Statement in MYSQL is used to set the values to the characteristics of the current transaction such as transaction isolation level and access mode.

Transaction Isolation levels

In a database system where more than one transaction is being executed simultaneously and in parallel, the property of isolation states that all the transactions will be carried out and executed as if it is the only transaction in the system. No transaction will affect the existence of any other transaction.

There are four transaction isolation levels provided by the MySQL database namely −

  • REPEATABLE READ − In case we have of multiple non-locking consistent select statements in a transaction. The snapshot established by the first SELECT statement is read by the following ones.

  • READ COMMITTED − If Transaction Isolation is set to this value each consistent SELECT statement reads its own snapshot.

  • READ UNCOMMITTED − If Transaction Isolation is set to this value each the SELECT statements might use the un-committed version of a row (even from the previous transactions), thus effecting the consistency of the current transactions such reads are known as dirty reads.

  • SERIALIZABLE − If Transaction Isolation is set to this value, the transactions will not allow Dirty Reads, Non-Repeatable Reads and, Phantom Reads.

The default value of this is REPEATABLE READ.

Transaction access modes

You can set a MySQL transaction to READ WRITE or READ ONLY modes. By default, it is set to READ WIRTE mode. You cannot set multiple transaction modes using the SET TRANSACTION statement.

Transaction Characteristic Scope

You can set the characteristics of a transaction in 2 scopes global and session.

If you set the GLOBAL scope the characteristics chosen are applicable for all the sessions and if you set GLOBAL scope the SET statement is applicable only to the subsequent transactions in the current session.

Syntax

Following is the syntax of the SET TRANSACTION Statement −

SET [GLOBAL | SESSION] TRANSACTION
   { ISOLATION LEVEL level | access_mode}

Example

Assume we have created a table using the CREATE statement as shown below −

CREATE TABLE EMPLOYEE(
   FIRST_NAME VARCHAR(20),
   LAST_NAME VARCHAR(20),
   AGE INT,
   INCOME INT
);

If we have a .csv file, and an .xml file with the following contents

data.xml

<rowgt;
   <FIRST_NAMEgt;Javed</FIRST_NAMEgt;
   <LAST_NAMEgt;Syed</LAST_NAMEgt;
   <AGEgt;25</AGEgt;
   <INCOMEgt;9855</INCOMEgt;
</rowgt;
<rowgt;
   <FIRST_NAMEgt;Abhinav</FIRST_NAMEgt;
   <LAST_NAMEgt;Gomatam</LAST_NAMEgt;
   <AGEgt;30</AGEgt;
   <INCOMEgt;7000</INCOMEgt;
</rowgt;

data.csv

'Krishna','Sharma',19,2000
'Raj','Kandukuri',20,7000

Following MySQL transaction tries to insert contents of these files in to the table −

START TRANSACTION;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
LOAD DATA INFILE "C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/data.csv" 
into table employee
   FIELDS TERMINATED BY ','
   LINES TERMINATED BY '\n';
   SAVEPOINT mysavepoint;
load xml infile "C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/data.xml" 
into table employee ROWS IDENTIFIED BY '<row>';

if you verify the contents of the above table, you can observe all the inserted records as −

SELECT * FROM EMPLOYEE;

Output

The above query produces the following output −

FIRST_NAME LAST_NAME AGE INCOME
'Krishna' 'Sharma' 19 2000
'Raj' 'Kandukuri' 20 7000
Javed Syed 25 9855
Abhinav Gomatam 30 7000

Following statement reverts the changes made to the last savepoint −

ROLLBACK TO SAVEPOINT mysavepoint;

After this if you verify the contents, you can observe only two records −

select * FROM EMPLOYEE;

Output

Following is the output of the above mysql query −

FIRST_NAME LAST_NAME AGE INCOME
'Krishna' 'Sharma' 19 2000
'Raj' 'Kandukuri' 20 7000
Advertisements