MySQL Transactions and Locks 

Table of Contents 1. DB Transactions 2. ACID Property in DBMS 3. Locks 4. Transaction Isolation Levels 5. REPEATABLE READ -> READ COMMITTED 

Transactions in MySQL ● Group of tasks ● Must Maintain ACID properties 

ACID ● Atomicity ○ Each transaction is either ‘all or none’ ○ The very reason for rollbacks ● Consistency ○ Transactions should bring the DB to one VALID state to another ○ VALID : adheres to all defined rules such as constraints, triggers and cascade ● Isolation ○ Concurrent transactions happen as if they are executed serially. ○ Each transactions happen as if they are ‘isolated’ from other transactions ● Durability ○ Once a transaction commits, results are stored permanently (Gets stored in disk) 

Transactions in MySQL ● Group of small tasks on a DB ● Must Maintain ACID properties ● Serializability 

Transactions in MySQL 

Transactions in MySQL ● Autocommit ○ SET autocommit = 0 -> session keeps the transaction open, until an explicit COMMIT or ROLLBACK ○ SET autocommit = 1 -> COMMIT for each SQL statement that update/modifies a table ○ When to use autocommit = 0? ■ Manual Replication or Dumping data ■ When transactions are guaranteed to be short ● Commit / Rollback ○ Self-explanatory ○ Releases locks 

Locks in MySQL ● Row Locks ( Simple INSERT ) ○ Index-record locks ● Gap Locks ○ Between index records, or Gap before the first or after the last index record ○ Used in REPEATABLE READ, but not in READ-COMMITTED ● Next Key Locks ( Row + Gap locks ) ○ Index-record lock + Gap lock on the gap preceding the index record ● Insert Intention Lock ○ Gap lock set by INSERT operations prior to row insertion. ● AUTO-INC Locks ○ Default : consecutive lock mode 

● Repeatable Read ( InnoDB Default ) ○ Consistent Reads i. Within a transaction, Sets and keeps the snapshot established by the first read ii. Trx read view will not see trx with id >= 22142679487, sees < 22142661616 ○ For unique index with unique search condition, only locks single index record ○ For range searches, InnoDB locks the index range scanned ( Gap locks and Next-key Locks ) Transaction Isolation Levels 

Transaction Isolation Levels ● Repeatable Read ( InnoDB Default ) ● Read Committed a. Only differences comes from LOCKS i. No Gap Locks! b. Each consistent read within a transaction sets and reads its own fresh snapshot. i. SELECTs need to scan more and more previous versions of the rows, and performance suffers. 

Transaction Isolation Levels ● Repeatable Read ( InnoDB Default ) ● Read Committed ● Read Uncommitted a. Read Committed + possible dirty reads b. Nope... ● Serializable a. SELECT … FROM requires locks! b. Stay away from it 

Repeatable Read -> Read Committed ● Why? a. Hung transactions from application client b. As the InnoDB transaction history grows, SELECTs need to scan more and more previous versions of the rows, and performance suffers. c. https://bugs.mysql.com/bug.php?id=75540 

REPEATABLE READ vs READ COMMITTED : During high traffic situation 1. Undo logs piling up 2. SELECTs have to look at old old snapshots until the hung transaction commits or aborts 3. Read throughput is decreased significantly 4. Connections pile up 5. Unable to make client connections 

Bottom Line 1. Detect hung transactions and close them properly 2. SET autocommit = 1; 3. REPEATABLE READ -> READ COMMITTED if the application allows for dirty reads within the same transaction 

References ● Differences between READ-COMMITTED and REPEATABLE-READ transaction isolation levels - https://www.percona.com/blog/2012/08/28/differences-between-read-committed-and-repeatable-read-tran saction-isolation-levels/ ● Innodb Locks - https://www.slideshare.net/billkarwin/innodb-locking-explained-with-stick-figures ● Isolation levels - https://dev.mysql.com/doc/refman/5.7/en/innodb-transaction-isolation-levels.html