Such lock-breaking may prevent the database from enforcing atomicity on the SQL queries that generated the lock.
Fixing your programme that created the locks is the appropriate response to this attack. But when money is involved, a quick kick will get things rolling once more.
1) Enter MySQL
mysql -u your_user -p
2) Let’s see the list of locked tables
mysql> show open tables where in_use>0;
3) Let’s see the list of the current processes, one of them is locking your table(s)
mysql> show processlist;
4) Kill one of these processes
mysql> kill <put_process_id_here>;
You are utilising a transaction; autocommit merely causes transactions to commit automatically at the end of the statement; it does not stop transactions.
You may be updating every record in the database, therefore it’s possible that another thread is holding a record lock on a record for an excessively lengthy period of time, causing your thread to time out. Alternatively, you might do multiple (2+) UPDATE queries on the same row in the same transaction.
By sending a request for more information about the event, you
SHOW ENGINE INNODB STATUS
after the event (in SQL editor). Ideally do this on a quiet test-machine.
mysql> set innodb_lock_wait_timeout=100;
Query OK, 0 rows affected (0.02 sec)
mysql> show variables like ‘innodb_lock_wait_timeout’;
+————————–+——-+
| Variable_name | Value |
+————————–+——-+
| innodb_lock_wait_timeout | 100 |
+————————–+——-+
Reset the lock at this point. If another transaction is locking yours, you have 100 seconds to SHOW ENGINE INNODB STATUS\G the database to find out which one it is.
The query is unable to be executed because of something. Most likely, one of the tables in your query is being updated, added to, or deleted by another query. You must ascertain what that is.
SHOW PROCESSLIST;
Once you locate the blocking process, find its id and run :
KILL {id};
Re-run your initial query.
Yuvraj Raulji
[ eCommerce Consultant ]
Yuvraj Raulji is an experienced e-commerce consultant and developer, specializing in Magento 2 and Next.js solutions. With a strong focus on innovation and client satisfaction, Yuvraj crafts high-performance web applications tailored to business needs. His expertise empowers businesses to excel in today’s rapidly evolving digital landscape.
© Copyright - yuvrajraulji.com