Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Sometime, it combines with a huge of "Waiting for global read lock" in the SHOW FULL PROCESSLIST\G and my database is locked completely. Cannot restart with the init script, so I must do a kill -9 <PID> (it causes a lot of crashed tables).

InnoDB Lock Monitor was enabled follow this guide. information_schema.innodb_locks is empty and belows is the innodb_trx table:

This question came from our site for system and network administrators.

Sorry, I'm a bit unclear on what you're asking. Are you asking how to restart using the init script (instead of kill -9), or are you asking how to avoid the deadlock? Also, which version of MySQL?
–
Derek DowneyDec 22 '11 at 14:19

I would like to find out the root cause of deadlocks.
–
quantaDec 22 '11 at 14:50

2 Answers
2

This particular deadlock is being caused by attempting to read from your v3_cam_date table for inserting rows into your usertmp table while another thread is updating the v3_cam_date table. Indicated by this statement:

This documentation page has some useful steps in dealing with deadlocks, but perhaps the most useful tip in your situation might be to use the READ COMMITTED transaction isolation level when doing your insert into usrtmp table.

This basically means that what you are selecting is only the committed values, not any changes that are happening from your LOAD DATA INFILE statement if it hasn't finished committing.

DEADLOCK EXPLANATION

If the underlying tables use the InnoDB Storage Engine, row locks (even on the same row) can never block reads, but deadlocks are still possible during writes. With AUTOCOMMIT=0 set in /etc/my.cnf, each DML statement (INSERT,UPDATE, and DELETE) will be executed as a single line transaction. Individual row locks are issued. Thus, 50 DB Connections can go after 50 different rows and nothing tragic happens.

Where can deadlocks come in?

Since the PRIMARY KEY of InnoDB tables is contained within the Clustered Index (internally known as the gen_clust_index), the row data is tightly coupled with the index entries. Any index made against columns not part of the PRIMARY KEY are cataloged with tow basic items, the column value and the gen_clust_index key. At times, update indexes columns in InnoDB may cause what I jokingly call index constipation. That's occurs when two or more locks are generated on index entries stored close to one another. This is possible in a heavily trafficked website.

I once helped a Developer see why this can happen in the DBA StackExchange. That developer made code changes afterwards. Here were those posts: