A deadlock in MySQL happens when two or more transactions mutually hold and request for locks, creating a cycle of dependencies. … InnoDB automatically detects transaction deadlocks, rollbacks a transaction immediately and returns an error. It uses a metric to pick the easiest transaction to rollback. MyISAM engine locks the entire table from read/write requests.
For example you have a near realtime table that you need to update via etl every 10 min , and that same table is used by your clients to read the current status of say batch processes status.
Avoid the mysql deadlock error by re-loading table in near-realtime. First to check if your tables are locked run as root or db admin :
show processlist ;
How do you write to a table while someone’s reading it without deadlock? Simple: load a staging table of the same table structure and do a rename swap in mysql.
Here’s the steps in list format
- create a table with the same structure of the original one.
- have etl load that table instead.
- do some data validity checks.
- do rename table swap code: a->c, b->a, c->b (a = prod, b = stage, c=temp)
example mysql code:
-
create table current_status_stage like current_status_stage;
-
— load table from source system via mysql command line or otherwise. ex csv
-
LOAD DATA INFILE "/tmp/current_status.csv" INTO TABLE current_status_stage COLUMNS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '"' LINES TERMINATED BY '\n'; -
RENAME TABLE reporting.current_status TO reporting.current_status_old, reporting.current_status_stage To reporting.current_status;RENAME TABLE reporting.current_status_old TO reporting.current_status_stage
