CODEWITHSUNDEEP
mysqlindexinginnodb
divyang
divyang

Reputation: 21

lock wait timout in InnoDb

I have created a mysql table with (date,name,id,money_spent)columns having (date,name,id) as the primary key (since this combination is always unique) and InnoDb is the storage engine .

date - Date datatype, name - varchar(30), id - varchar(100),

I have a logic where in multiple update queries are fired in parallel . For Eg (considering these 2 for now )

T1 :

 update test_database.users set money_spend = 1000
     where date between "2020-01-01" and "2020-01-10"
       and name = "John";

T2 :

update test_database.users set money_spend = 1005
     where date between "2020-01-01" and "2020-01-10"
       and name = "Tammy";

Running these 2 Transaction in parallel causes T2 to timeout because of the lock acquired by T1.

transaction isolation level - repeatable read isolation level

InnoDb Engine Status


LOCK WAIT 19543 lock struct(s), heap size 376, 1407774 row lock(s)
MySQL thread id 105402, OS thread handle 0x2ba11d68a700, query id 16862373 10.11.3.212 connect-dev Sending data
update test_database.users set money_spend = 1005 where date between "2020-01-01" and "2020-01-10" and name = "Tammy";
------- TRX HAS BEEN WAITING 2 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 154606 page no 243093 n bits 78 index `PRIMARY` of table `test_database`.`users` trx id 44023639357 lock_mode X waiting
Record lock, heap no 78 PHYSICAL RECORD: n_fields 30; compact format; info bits 0
 0: len=3; bufptr=0x2b9fea4e2d5e; hex= 8fc95b; asc   [;;
 1: len=8; bufptr=0x2b9fea4e2d61; hex= 4170706e65787573; asc John;;
 2: len=8; bufptr=0x2b9fea4e2d69; hex= 3230393835373832; asc 20985782;;
 3: len=6; bufptr=0x2b9fea4e2d71; hex= 000a3f47ff22; asc   ?G ";;
 4: len=7; bufptr=0x2b9fea4e2d77; hex= 0f011040350d5b; asc    @5 [;;

Looking at the innoDb status it looks like T2 is trying to acquire a lock on the record having "John" as the name but since records with "John" as the name are already locked by T1 , T2 times out .

Question : Since InnoDb supports row level locking shouldn't transaction(T1) acquire a lock on only the records having date between "2020-01-01" and "2020-01-10" and name as "John".Why is it giving a time out for the T2 transaction since the name part of the index is different (T2 is having "Tammy" as the name)

Upvotes: 0

Views: 95

Answers (1)

Rick James
Rick James

Reputation: 142366

Rearrange the PK to PRIMARY KEY(name, date, id). This way, all the rows needed (in either query) will be adjacent in the table. That is more efficient than having the rows scattered (with (date, name, ...)).

Speeding up the queries will help one query finish before another one threatens to conflict with it.

Having the rows consecutive avoids some "gap locking" situations. (I don't know if this is relevant here, but it seems to be.)

The column tested with = needs to come before` the range test.

More indexing tips: http://mysql.rjweb.org/doc.php/index_cookbook_mysql

If id is an AUTO_INCREMENT, then the pattern is "let's put some columns first in the PK in order to speed up some queries, then tack id on the end to make sure it is unique. (I don't know if that is what you are doing; but it is not relevant to the immediate discussion.)

Upvotes: 2

Related Questions