- MySQL Basics
- MySQL - Home
- MySQL - Introduction
- MySQL - Features
- MySQL - Versions
- MySQL - Variables
- MySQL - Installation
- MySQL - Administration
- MySQL - PHP Syntax
- MySQL - Node.js Syntax
- MySQL - Java Syntax
- MySQL - Python Syntax
- MySQL - Connection
- MySQL - Workbench
- MySQL Databases
- MySQL - Create Database
- MySQL - Drop Database
- MySQL - Select Database
- MySQL - Show Database
- MySQL - Copy Database
- MySQL - Database Export
- MySQL - Database Import
- MySQL - Database Info
- MySQL Users
- MySQL - Create Users
- MySQL - Drop Users
- MySQL - Show Users
- MySQL - Change Password
- MySQL - Grant Privileges
- MySQL - Show Privileges
- MySQL - Revoke Privileges
- MySQL - Lock User Account
- MySQL - Unlock User Account
- MySQL Tables
- MySQL - Create Tables
- MySQL - Show Tables
- MySQL - Alter Tables
- MySQL - Rename Tables
- MySQL - Clone Tables
- MySQL - Truncate Tables
- MySQL - Temporary Tables
- MySQL - Repair Tables
- MySQL - Describe Tables
- MySQL - Add/Delete Columns
- MySQL - Show Columns
- MySQL - Rename Columns
- MySQL - Table Locking
- MySQL - Drop Tables
- MySQL - Derived Tables
- MySQL Queries
- MySQL - Queries
- MySQL - Constraints
- MySQL - Insert Query
- MySQL - Select Query
- MySQL - Update Query
- MySQL - Delete Query
- MySQL - Replace Query
- MySQL - Insert Ignore
- MySQL - Insert on Duplicate Key Update
- MySQL - Insert Into Select
- MySQL Indexes
- MySQL - Indexes
- MySQL - Create Index
- MySQL - Drop Index
- MySQL - Show Indexes
- MySQL - Unique Index
- MySQL - Clustered Index
- MySQL - Non-Clustered Index
- MySQL Operators and Clauses
- MySQL - Where Clause
- MySQL - Limit Clause
- MySQL - Distinct Clause
- MySQL - Order By Clause
- MySQL - Group By Clause
- MySQL - Having Clause
- MySQL - AND Operator
- MySQL - OR Operator
- MySQL - Like Operator
- MySQL - IN Operator
- MySQL - ANY Operator
- MySQL - EXISTS Operator
- MySQL - NOT Operator
- MySQL - NOT EQUAL Operator
- MySQL - IS NULL Operator
- MySQL - IS NOT NULL Operator
- MySQL - Between Operator
- MySQL - UNION Operator
- MySQL - UNION vs UNION ALL
- MySQL - MINUS Operator
- MySQL - INTERSECT Operator
- MySQL - INTERVAL Operator
- MySQL Joins
- MySQL - Using Joins
- MySQL - Inner Join
- MySQL - Left Join
- MySQL - Right Join
- MySQL - Cross Join
- MySQL - Full Join
- MySQL - Self Join
- MySQL - Delete Join
- MySQL - Update Join
- MySQL - Union vs Join
- MySQL Keys
- MySQL - Unique Key
- MySQL - Primary Key
- MySQL - Foreign Key
- MySQL - Composite Key
- MySQL - Alternate Key
- MySQL Triggers
- MySQL - Triggers
- MySQL - Create Trigger
- MySQL - Show Trigger
- MySQL - Drop Trigger
- MySQL - Before Insert Trigger
- MySQL - After Insert Trigger
- MySQL - Before Update Trigger
- MySQL - After Update Trigger
- MySQL - Before Delete Trigger
- MySQL - After Delete Trigger
- MySQL Data Types
- MySQL - Data Types
- MySQL - VARCHAR
- MySQL - BOOLEAN
- MySQL - ENUM
- MySQL - DECIMAL
- MySQL - INT
- MySQL - FLOAT
- MySQL - BIT
- MySQL - TINYINT
- MySQL - BLOB
- MySQL - SET
- MySQL Regular Expressions
- MySQL - Regular Expressions
- MySQL - RLIKE Operator
- MySQL - NOT LIKE Operator
- MySQL - NOT REGEXP Operator
- MySQL - regexp_instr() Function
- MySQL - regexp_like() Function
- MySQL - regexp_replace() Function
- MySQL - regexp_substr() Function
- MySQL Fulltext Search
- MySQL - Fulltext Search
- MySQL - Natural Language Fulltext Search
- MySQL - Boolean Fulltext Search
- MySQL - Query Expansion Fulltext Search
- MySQL - ngram Fulltext Parser
- MySQL Functions & Operators
- MySQL - Date and Time Functions
- MySQL - Arithmetic Operators
- MySQL - Numeric Functions
- MySQL - String Functions
- MySQL - Aggregate Functions
- MySQL Misc Concepts
- MySQL - NULL Values
- MySQL - Transactions
- MySQL - Using Sequences
- MySQL - Handling Duplicates
- MySQL - SQL Injection
- MySQL - SubQuery
- MySQL - Comments
- MySQL - Check Constraints
- MySQL - Storage Engines
- MySQL - Export Table into CSV File
- MySQL - Import CSV File into Database
- MySQL - UUID
- MySQL - Common Table Expressions
- MySQL - On Delete Cascade
- MySQL - Upsert
- MySQL - Horizontal Partitioning
- MySQL - Vertical Partitioning
- MySQL - Cursor
- MySQL - Stored Functions
- MySQL - Signal
- MySQL - Resignal
- MySQL - Character Set
- MySQL - Collation
- MySQL - Wildcards
- MySQL - Alias
- MySQL - ROLLUP
- MySQL - Today Date
- MySQL - Literals
- MySQL - Stored Procedure
- MySQL - Explain
- MySQL - JSON
- MySQL - Standard Deviation
- MySQL - Find Duplicate Records
- MySQL - Delete Duplicate Records
- MySQL - Select Random Records
- MySQL - Show Processlist
- MySQL - Change Column Type
- MySQL - Reset Auto-Increment
- MySQL - Coalesce() Function
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 |