To Nha Notes | April 4, 2023, 11:15 a.m.
Transaction isolation is one of the foundations of database processing. Isolation is the I in the acronym ACID; the isolation level is the setting that fine-tunes the balance between performance and reliability, consistency, and reproducibility of results when multiple transactions are making changes and performing queries at the same time.
InnoDB offers all four transaction isolation levels described by the SQL:1992 standard: READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, and SERIALIZABLE. The default isolation level for InnoDB is REPEATABLE READ.
For example, we may get lock when create table from select. It is a bit tricky, but we can skip this locknig by updating tracsaction isolation as below.
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED ;
-- CREATE TABLE FROM SELECT HERE
COMMIT;