Hacer debug a Locks en base de datos MySQL

By Darío Rivera

Posted On 2023-06-12 in MySQL

When working with relational databases, you may have had to perform a transaction to ensure the integrity of the data. When your application has a significant number of transactions or you have performed one incorrectly, you have probably seen an error message similar to the following:

SQLSTATE[HY000]: General error: 1205 Lock wait timeout exceeded

This error indicates that there is a lock which can be caused by several reasons. One of them could be the non-confirmation of a transaction, that is, commit() or rollback() hasn't been executed. To try to determine the SQL statements in the environment you are working in, the most sensible thing is to look for the MySQL processing list to observe which statements are blocking others. To do this, just use the following command.

show full processlist\G
*************************** 1. row ***************************
     Id: 4
   User: event_scheduler
   Host: localhost
     db: NULL
Command: Daemon
   Time: 260933
  State: Waiting on empty queue
   Info: NULL
*************************** 2. row ***************************
     Id: 1116
   User: root
   Host: localhost
     db: NULL
Command: Query
   Time: 0
  State: starting
   Info: show full processlist
*************************** 3. row ***************************
     Id: 1125
   User: erp_user
   Host: localhost:55498
     db: erp
Command: Execute
   Time: 0
  State: update
   Info: insert into users values `users` values (1, `steave`, 1, now())
5 rows in set (0.00 sec)

As you can see in the previous result, there may be connections waiting, connections started, or connections currently running a process such as number 3. When a lock occurs in the database, generally two or three statements will appear, from which you can draw conclusions about which one is causing the lock. See you next time.

Categories