2

Please pardon a bit of a newbie question - I'm a developer attempting to play the role of a DBA, and a bit over my head at the moment.

The main issue I'm trying to deal with at the moment is that MySQL is showing me a lot of deadlock errors. We've been running for months without any, and after adding what we thought were fairly innocuous changes, we're suddenly seeing a dozen an hour.

The specific error we're seeing (table/column names scrubbed, we're using some JDBI you'll see in there as well):

------------------------
LATEST DETECTED DEADLOCK
------------------------
2014-05-21 19:11:13 2b4c3602a700
*** (1) TRANSACTION:
TRANSACTION 1327203423, ACTIVE 0 sec starting index read
mysql tables in use 3, locked 3
LOCK WAIT 4 lock struct(s), heap size 1248, 3 row lock(s)
MySQL thread id 6182129, OS thread handle 0x2b4c36683700, query id 2061701269 [host] [ip] [user] updating
/* WriteDAO.update */ UPDATE A        SET t = 0, createdDate = '2014-05-21 19:11:13'        WHERE uid = 1 AND tid = 100
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 109 page no 84 n bits 584 index `idx_A_tid` of table `core`.`A` trx id 1327203423 lock_mode X waiting
Record lock, heap no 167 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 8; hex 8000000000002740; asc       '@;;
 1: len 8; hex 8000000000002747; asc       'G;;

*** (2) TRANSACTION:
TRANSACTION 1327203438, ACTIVE 0 sec fetching rows
mysql tables in use 3, locked 3
6 lock struct(s), heap size 1248, 6 row lock(s)
MySQL thread id 6182028, OS thread handle 0x2b4c3602a700, query id 2061701279 [host] [ip] [user] updating
/* WriteDAO.update */ UPDATE A        SET t = 0, createdDate = '2014-05-21 19:11:13'        WHERE uid = 2 AND tid = 100
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 109 page no 84 n bits 584 index `idx_A_tid` of table `core`.`A` trx id 1327203438 lock_mode X
Record lock, heap no 167 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 8; hex 8000000000002740; asc       '@;;
 1: len 8; hex 8000000000002747; asc       'G;;

Record lock, heap no 168 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 8; hex 8000000000002740; asc       '@;;
 1: len 8; hex 800000000000274e; asc       'N;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 109 page no 6 n bits 344 index `PRIMARY` of table `core`.`A` trx id 1327203438 lock_mode X locks rec but not gap waiting
Record lock, heap no 63 PHYSICAL RECORD: n_fields 7; compact format; info bits 0
 0: len 8; hex 800000000000274e; asc       'N;;
 1: len 6; hex 00004f13d872; asc   O  r;;
 2: len 7; hex 42000001e02f43; asc B    /C;;
 3: len 8; hex 8000000000002771; asc       'q;;
 4: len 8; hex 8000000000002740; asc       '@;;
 5: len 8; hex 8000000000000000; asc         ;;
 6: len 5; hex 9992eb085c; asc     \;;

*** WE ROLL BACK TRANSACTION (1)

The table A is fairly simple, with two indexes at the end:

CREATE TABLE A (
  id          BIGINT NOT NULL AUTO_INCREMENT,
  uid         BIGINT NOT NULL,
  tid         BIGINT NOT NULL,
  t           BIGINT NOT NULL,
  createdDate DATETIME NOT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=10001 DEFAULT CHARACTER SET=utf8 COLLATE=utf8_unicode_ci;

CREATE INDEX idx_A_uid ON A (uid);
CREATE INDEX idx_A_tid ON A (tid);

We aren't running anything against these tables in a transaction, beyond what JDBI transactions do automatically via @SqlUpdate / @SqlQuery / etc annotations, e.g.:

@SqlUpdate("UPDATE A "+
           "       SET t = :t, createdDate = :createdDate "+
           "       WHERE uid = :uid AND tid = :tid")
public int update (@BindBean T t);

All of these queries run quite quickly (slow queries logs don't show anything over a second, and almost everything is much less than that). We don't have all that significant a load yet.

First question:

mysql tables in use 3, locked 3

Why is MySQL locking 3 tables to do an update on one? Does it consider the indexes to be tables as well in this output?

Second question:

RECORD LOCKS space id 109 page no 6 n bits 344 index `PRIMARY` of table `core`.`A` trx id 1327203438 lock_mode X locks rec but not gap waiting

Why is the primary key of A locked? We don't touch that column >at all<, except for the initial row creation. Notice that neither update statement is touching that column, and they should be operating against different rows even. We also use distributed hazelcast to obtain a distributed lock, so only one thread should ever be trying to access a given row for writing in this table at a time. Can a read lock on a row cause the write to deadlock? And even if it could, shouldn't the row level locking address this?

Third question: If MySQL needs to update a row in the index, does it lock the entire index like a table lock? Or does it do row level locking like the table its indexing?

Any suggestions for debugging this?

Thanks

Improve this question

1 Answer 1

Reset to default
3

I would always recommend you to not run UPDATE statements without the PRIMARY KEY in the where clause.

You have 2 transactions : -

TX1 :

UPDATE A
SET t = 0, createdDate = '2014-05-21 19:11:13'        
WHERE uid = 1 AND tid = 100

TX2 :

UPDATE A        
SET t = 0, createdDate = '2014-05-21 19:11:13'        
WHERE uid = 2 AND tid = 100

Both TX's are updating without using the Primary Key.

So Lets say for example the query in TX2 has to update 20 rows. So what it does is it puts an S lock on the entire A table (because it does not know the id's) and then it puts an X lock in the 20 rows one by one as it keeps updating them.

Say TX2 is updating the 13th row right now, and at this time TX1 come and tries to update it, for this reason it needs to put an X lock. So it puts in a request for X lock and it goes into the queue. The queue in MySQL is FIFO (First in First Out). So after TX1 puts in a request for X lock, TX2 gets done updating the 13th row and now puts in a request for X lock to update the 14th row, but it cannot do that because TX2 cannot get its X lock that its waiting for before TX1 gets its X lock. And TX1 cannot get its X lock till TX2 releases its S lock. This is why you have a deadlock. I hope all of that made sense .. I would highly recommend reading this - http://dev.mysql.com/doc/refman/5.0/en/innodb-lock-modes.html

Important point to note is :-

A shared (S) lock permits a transaction to read a row.

An exclusive (X) lock permits a transaction to update or delete a row.

If transaction T1 holds a shared (S) lock on row r, then requests from some distinct transaction T2 for a lock on row r are handled as follows:

A request by T2 for an S lock can be granted immediately. As a result, both T1 and T2 hold an S lock on r.

A request by T2 for an X lock cannot be granted immediately.

SOLUTION:

So the solution to this would be to make updates using the Primary key so that the locks are placed on just the row you want to update and not the table.

TX2 should be (in PHP) - You can convert the logic into any language you want:

$db = Zend_Db_Table::getDefaultAdapter();
$select = "SELECT id From A where uid = 2 AND tid = 100";
$rows = $db->fetchAll($select);

foreach ($rows as $row) {
    $id = $row['id'];
    $update = "UPDATE A SET t=0,createdDate = '2014-05-21 19:11:13' Where id = ".$id;
    $db->query($update);
}

So you should select the id's and update using those id's , so just the row is locked instead of the entire table. You should do the same with TX1. For tables that are high on traffic, this approach is important.

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

I don't know if this is right or wrong yet, but as a non-DBA dev this seems awful... The solution of looking up the primary key for each row to be updated, and then looping in code to update each one defeats a large part of using SQL / RDBMS. If I have thousands of rows to update, I would expect the DB to do that efficiently, rather than burning thousands of database connections, network, CPU, etc - not to mention it would turn a near-atomic operation into one fraught with race conditions (I know I could transactionalize it, but that would lock the entire table even longer)
You are right, situations of deadlocks are rather tricky. But this is an approach you should consider when you are dealing with a table that deals with a lot of traffic. And having personally experienced, this isn't really that slow. I would take that slight bit of overhead rather than having a deadlock..

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.