The Deadlock is one of a common issue happens Relational databases such as MySQL, Oracle, PostgreSQL & DB2. In reality, these databases give the best performance with the high concurrency workload. In addition, there is a chance of happening Deadlock with a high concurrency workload.
Deadlock Definition:
A Deadlock in MySQL happens when two or more transactions mutually hold and request for locks, engendering a cycle of dependencies. In General, MySQL detects the Deadlock Condition and rollback one of the Transactions to Break Deadlock. As a result, It returns the following error messages.
ERROR 1213 (40001): Deadlock found when trying to get lock;
try restarting transactionWhat are the problems with DB Deadlock?
It will fix Deadlock issues permanently if execute DB transactions in a sequential order. In reality, it is not a good solution because it degrades DB performance what it gains with high concurrency work-load.
Generally, MySQL saves The LATEST DETECTED DEADLOCK information inside “SHOW ENGINE INNODB STATUS”. This information helps to figure out the root cause of the issue. There are
------------------------
LATEST DETECTED DEADLOCK
------------------------
141017 8:04:35
*** (1) TRANSACTION:
TRANSACTION 0 1803, ACTIVE 11 sec, process no 3857, OS thread id 140048507971328
starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 368
MySQL thread id 2, query id 89 localhost msandbox updating
DELETE FROM t WHERE i = 1
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 4555 n bits 72 index `GEN_CLUST_INDEX` of table
`mysql/t` trx id 0 1803 lock_mode X waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 6; hex 000000000200; asc ;; 1: len 6; hex 000000000709; asc
;; 2: len 7; hex 80000000320110; asc 2 ;; 3: len 4; hex 80000001; asc ;
;
*** (2) TRANSACTION:
TRANSACTION 0 1802, ACTIVE 33 sec, process no 3857, OS thread id 140048508237568
starting index read, thread declared inside InnoDB 500
mysql tables in use 1, locked 1
4 lock struct(s), heap size 1216
MySQL thread id 1, query id 90 localhost msandbox updating
DELETE FROM t WHERE i = 1
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 0 page no 4555 n bits 72 index `GEN_CLUST_INDEX` of table
`mysql/t` trx id 0 1802 lock mode S
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;;
Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 6; hex 000000000200; asc ;; 1: len 6; hex 000000000709; asc
;; 2: len 7; hex 80000000320110; asc 2 ;; 3: len 4; hex 80000001; asc ;
;
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 4555 n bits 72 index `GEN_CLUST_INDEX` of table
`mysql/t` trx id 0 1802 lock_mode X waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 6; hex 000000000200; asc ;; 1: len 6; hex 000000000709; asc
;; 2: len 7; hex 80000000320110; asc 2 ;; 3: len 4; hex 80000001; asc ;
;
*** WE ROLL BACK TRANSACTION (2)What are the problems with Deadlocks?
By Default, MySQL holds transactions until 50 sec to detects Deadlock. On the negative side, it causes DB server stall and DB service outage if there are many deadlocks happen continuously. Under those circumstances, there are two reasons cause Deadlocks
- Long-running transactions
- Long-running SQL queries & Bad Indexes
Long Running Transactions:
It is one of the reasons if the application starts transactions but holds it a long time without commit causes Deadlocks. In this cases, It is a symptom to show INSERT commands inside the DEADLOCK section of “SHOW ENGINE INNODB STATUS” command output.
In addition, the following performance schema tables store transaction events in mysql:
events_transactions_current: The current transaction event for each thread.events_transactions_history: The most recent transaction events that have ended per thread.events_transactions_history_long: The most recent transaction events that have ended globally (across all threads).
In this case, The application team has to check why there is a delay between statements inside a transaction. Also, I suggest using a DB stored procedures to avoid delay between statements in a transaction.
Long-running Queries:
If the RECENT DETECTED DEADLOCK section shows long-running queries (other than INSERT) then it is a problem with Long-running queries(slow quereis). Important to realize, bad indexing and wrongly optimization SQL queries are root causes of the deadlocks.
Solution:
Most importantly, It needs to optimize SQL Queries to fix deadlocks. In general, it supposes to done by DB Architects or performance experts. on the other hand, I suggest you use SmartWorkbench to fix deadlock issues in case of DB exerts unavailability. The SmartWorkbench is a great tool for MySQL which can detect deadlock and try to fix the deadlocks.
The SmartWorkbench is MySQL GUI tools with advance features and MySQL Debugger(1) is one of them. Most importantly, It can automatically detect all types of MySQL production issues and provide solutions. It Opens the Debugger module after clicking on the debugger icon(1) from the menu.
It starts to check deadlocks for every 15 sec and records details. Also, It provides solutions if it causes with long-running queries. It optimizes long-running and recommends good indexes to speed up them to fix deadlocks.
It is always the best practice to review and optimize SQL queries by DB architects or performance experts to avoid such issues on production. The SmartWorkbench also can do Query optimization and boost performance.
SQL Query Optimization:
Query Optimization is nothing but selecting a good index for your queries. In fact, it is difficult to select a good index out of N!*(N-1)+N possible indexes. Here N is no.of columns in WHERE, GROUP BY and ORDER BY clauses. In the first place, it needs to create a good index for getting the best performance whenever developing the DB queries. Most impatiently, It should choose the right columns with the right order in an index based on the index behavior and their design.
Indexes are very sensitive their behavior will be change based on filter types, logical operator between filter conditions, columns cardinality, apply functions on the filter column, arithmetic operations with filter columns, JOIN condition, Grouping function, GROUP BY clause columns and ORDER BY clause columns order.
There are high chances to make mistakes in the query optimization area and missing the good indexes if the query optimized done by nonprofessional. Generally, It supposed to be done by a DB performance expert or DB architect.
Query Optimization Tools:
There are MySQL Query Optimization tools available in the market since 2017. SmartWorkbench is one of my favorite tools having many advanced features such as Query Optimization, Slow query log analyzing, SQL code generator with Dynamic Query Builder and MySQL production issues Debugger. I recommend you to use SmartWorkbench query performance tools if your project doesn’t have a DB performance expert/ DB Architect. Also, it helps to cross verify if you are learning MySQL with the tool until you become an expert.
Query Optimization using SmartWorkbech tool
It is easy to identify such problems using SmartWorkbnech. It can trigger a warning after you have developed the SQL query ends with a semicolon(;). The tool automatically analyzes the query and gives a warning if the query doesn’t have an optimal index or if it goes for a full table scan in the MySQL optimizer Query execution plan.
If you click that warning icon(1) or Query Optimizer icon (2) Then it will open the Query Optimizer module.
Run the analyzer(3) button then it will recommend optimal indexes(4), Query rewrite recommendations(5,7) and expected performance improvement(6). After that, if you click the “Create Index” button then it will add recommended indexes and provide a consolidated performance improvement report with all details such as percentage of improvement in scanning records, percentage of query execution time improvement for each state of query execution. It is very useful to identify significant performance improvement before and after Query optimization. It has given more 100X performance most of my SQL Queries.
