Transaction Concurrency and Recovery in My SQL Jamshid
- Slides: 37
Transaction Concurrency and Recovery in My. SQL Jamshid Mozafari. Jamshid@gmail. com
Introduction Locks Two-phase Isolation level My. SQL engines: • • • Inno. DB My. ISAM Memory CSV Archive Blackhole NDB Merge Federated Dead. Lock Backup Full Backup Feature Incremental Backup My. ISAM Memory Log Files Inno. DB Recovery Archive NDB Storage limits Transactions Locking granularity MVCC 256 TB No Table No RAM No Table No 64 TB Yes Row Yes None No Row No 384 EB Yes Row No Geospatial data type support Geospatial indexing support B-tree indexes T-tree indexes Hash indexes Yes Yes No No Yes No No Yes Full-text search indexes Yes No No Clustered indexes Data caches Index caches No No Yes No N/A Yes Yes No No Yes Compressed data Yes No Cluster database support No No Yes Replication support Yes Yes Yes Foreign key support Backup / point-in-time recovery No No Yes Yes Yes • In the Inno. DB transaction model, the goal is to combine the best properties of a multi-versioning database with traditional two-phase locking. • Inno. DB performs locking at the row level and runs queries as nonlocking consistent reads by default.
Introduction Locks Two-phase Isolation level Dead. Lock Backup Full Backup Incremental Backup Log Files Recovery Autocommit: If autocommit mode is disabled within a session with SET autocommit = 0, the session always has a transaction open. A COMMIT or ROLLBACK statement ends the current transaction and a new one starts.
Introduction Locks Two-phase Isolation level Dead. Lock My. SQL Locks: • • Shared and Exclusive Locks Intention Locks Record Locks Gap Locks Next-Key Locks Insert Intention Locks AUTO-INC Locks Predicate Locks for Spatial Indexes Backup Full Backup Incremental Backup Log Files Recovery
Introduction Locks Two-phase Isolation level Dead. Lock Backup Full Backup Incremental Backup Log Files My. SQL Locks: Query Lock SELECT. . . FROM Nothing(consistent read) SELECT. . . FROM. . . LOCK IN SHARE MODE shared next-key SELECT. . . FROM. . . FOR UPDATE exclusive next-key UPDATE. . . WHERE. . . exclusive next-key DELETE FROM. . . WHERE. . . exclusive next-key INSERT exclusive Recovery
Introduction Locks Two-phase Isolation level Dead. Lock Backup Full Backup Incremental Backup Log Files SET TRANSACTION Syntax: SET [GLOBAL | SESSION] TRANSACTION transaction_characteristic [, transaction_characteristic]. . . transaction_characteristic: ISOLATION LEVEL level | READ WRITE | READ ONLY level: REPEATABLE READ | READ COMMITTED | READ UNCOMMITTED | SERIALIZABLE Example: • • SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE; • The default isolation level for Inno. DB is REPEATABLE READ. Recovery
Introduction Locks Two-phase Isolation level Dead. Lock Backup Full Backup Incremental Backup READ UNCOMMITTED: READ UNCOMMITTED 1. sql: SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; start transaction; select * from mydb. info; do sleep(8); select * from mydb. info; do sleep(5); select * from mydb. info; commit; READ UNCOMMITTED 2. sql: SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; start transaction; update mydb. info set lname='Barzide' where id=5; do sleep(10); rollback; Log Files Recovery
Introduction Locks Two-phase Isolation level Dead. Lock READ UNCOMMITTED: Backup Full Backup Incremental Backup Log Files Recovery
Introduction Locks Two-phase Isolation level Dead. Lock Backup Full Backup Incremental Backup READ COMMITTED: READ COMMITTED 1. sql: SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; start transaction; select * from mydb. info; do sleep(8); select * from mydb. info; do sleep(5); select * from mydb. info; commit; READ COMMITTED 2. sql: SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; start transaction; update mydb. info set lname='Barzide' where id=5; do sleep(10); commit; Log Files Recovery
Introduction Locks Two-phase Isolation level READ COMMITTED: Dead. Lock Backup Full Backup Incremental Backup Log Files Recovery
Introduction Locks Two-phase Isolation level Dead. Lock Backup Full Backup Incremental Backup REPEATABLE READ: REPEATABLE READ 1. sql: SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; start transaction; select * from mydb. info; do sleep(10); select * from mydb. info; commit; REPEATABLE READ 2. sql: SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; start transaction; update mydb. info set lname='Barzide' where id=5; commit; Log Files Recovery
Introduction Locks Two-phase Isolation level REPEATABLE READ: Dead. Lock Backup Full Backup Incremental Backup Log Files Recovery
Introduction Locks Two-phase Isolation level Dead. Lock Backup Full Backup Incremental Backup Log Files SERIALIZABLE: SERIALIZABLE 1. sql: SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE; start transaction; select * from mydb. info; do sleep(10); select * from mydb. info; commit; SERIALIZABLE 2. sql: SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE; start transaction; update mydb. info set lname='Barzide' where id=5; commit; • This level is like REPEATABLE READ, but Inno. DB implicitly converts all plain SELECT statements to SELECT. . . LOCK IN SHARE MODE. Recovery
Introduction Locks Two-phase SERIALIZABLE: Isolation level Dead. Lock Backup Full Backup Incremental Backup Log Files Recovery
Introduction Locks Two-phase Isolation level Dead. Lock Backup Full Backup Incremental Backup SET Syntax(Deadlock Detection): SET variable_assignment [, variable_assignment]. . . variable_assignment: user_var_name = expr | param_name = expr | local_var_name = expr | [GLOBAL | SESSION] system_var_name = expr | [@@global. | @@session. | @@] system_var_name = expr Example: • • Set global innodb_deadlock_detect=1; Set global innodb_deadlock_detect=0; • The default deadlock detection value for Inno. DB is ON. Log Files Recovery
Introduction Locks Two-phase Isolation level Dead. Lock Backup Full Backup Deadlock(Deadlock Detection): DEADLOCK 1. sql: start transaction; select * from mydb. info where id=5 lock in share mode; do sleep(10); update mydb. info set lname='Barzide' where id=5; select * from mydb. info; commit; DEADLOCK 2. sql: start transaction; update mydb. info set lname='Mozafari' where id=5; commit; Incremental Backup Log Files Recovery
Introduction Locks Two-phase Isolation level Dead. Lock Backup Full Backup Incremental Backup Log Files Recovery Deadlock(Deadlock Detection): Deadlock 1 Deadlock 2
Introduction Locks Two-phase Isolation level Dead. Lock Backup Deadlock(Deadlock Detection): Full Backup Incremental Backup Log Files Recovery
Introduction Locks Two-phase Isolation level Dead. Lock Backup Full Backup Incremental Backup Deadlock(Without Deadlock Detection): DEADLOCK 1. sql: set global innodb_deadlock_detect=0; start transaction; select * from mydb. info where id=5 lock in share mode; do sleep(10); update mydb. info set lname='Barzide' where id=5; select * from mydb. info; commit; DEADLOCK 2. sql: set global innodb_deadlock_detect=0; start transaction; update mydb. info set lname='Mozafari' where id=5; commit; Log Files Recovery
Introduction Locks Two-phase Isolation level Dead. Lock Backup Full Backup Incremental Backup Log Files Recovery Deadlock(Without Deadlock Detection): Deadlock 1 Deadlock 2
Introduction Locks Two-phase Backup type: • Physical Backup • Logical Backup Isolation level Dead. Lock Backup Full Backup Incremental Backup Log Files Recovery
Introduction Locks Two-phase Isolation level Full Backup: • Database Backup • All Database Backup Dead. Lock Backup Full Backup Incremental Backup Log Files Recovery
Introduction Locks Two-phase Full Backup: Isolation level Dead. Lock Backup Full Backup Incremental Backup Log Files Recovery
Introduction Locks Two-phase Isolation level Dead. Lock Enable Incremental Backup: • Edit my. ini file Backup Full Backup Incremental Backup Log Files Recovery
Introduction Locks Two-phase Isolation level Dead. Lock Enable Incremental Backup: • Restart My. SQL Service • Create Incremental Logs Backup Full Backup Incremental Backup Log Files Recovery
Introduction Locks Two-phase Isolation level Incremental Backup: Dead. Lock Backup Full Backup Incremental Backup Log Files Recovery
Introduction Locks Two-phase Isolation level Incremental Backup: • Binary Logs • Master Log Dead. Lock Backup Full Backup Incremental Backup Log Files Recovery
Introduction Locks Two-phase Isolation level Full Backup: • Full Backup with flush logs: Dead. Lock Backup Full Backup Incremental Backup Log Files Recovery
Introduction Locks Two-phase Full Backup: Isolation level Dead. Lock Backup Full Backup Incremental Backup Log Files Recovery
Introduction Locks Two-phase Isolation level Dead. Lock Full Backup: • Full Backup with delete master logs: Backup Full Backup Incremental Backup Log Files Recovery
Introduction Locks Two-phase Isolation level Incremental Log Files: • Content of inc. Backup. 00001: Dead. Lock Backup Full Backup Incremental Backup Log Files Recovery
Introduction Locks Two-phase Isolation level Incremental Log Files: • Content of inc. Backup. 00002: Dead. Lock Backup Full Backup Incremental Backup Log Files Recovery
Introduction Locks Two-phase Isolation level Recovery Full Backup: • Recovery backup. sql: Dead. Lock Backup Full Backup Incremental Backup Log Files Recovery
Introduction Locks Two-phase Isolation level Dead. Lock Backup Full Backup Incremental Backup Log Files Recovery Incremental Backup: • Recovery inc. Backup. 000001: insert into my. DB. info values(6, 'Javad', 'Talebi'); delete from my. DB. info where id = 5; update my. DB. info set lname='Karimi' where id=4;
Introduction Locks Two-phase Isolation level Dead. Lock Backup Full Backup Incremental Backup Log Files Recovery Incremental Backup: • Recovery inc. Backup. 000002: insert into my. DB. info values(7, ‘Farshid', ‘Eslami');
Reference: • http: //dev. mysql. com • High Performance My. SQL, Third Edition – O’Reilly • http: //www. techflirt. com/mysql-binary-log-tutorial
