0

In mysql 5.7 documentation, it says that for READ-COMMITTED isolation level, there wouldn't be any next-key locks or gap locks placed on an update query. But I'm getting frequent deadlocks for the following query.

UPDATE customer SET status ='SUCCESS' WHERE subscriber_id IN (146,148,150,152,153,154,155,156,157,158)

The subscriber_id is an indexed field but not unique.

When I checked for the innodb status following was found.

For the first transaction

(1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 57008 page no 6 n bits 224 index subscriber_id of table customer.subscriber_id /* Partition monthly_rest */ trx id 1340735 lock_mode X locks rec but not gap waiting

Record lock, heap no 144 PHYSICAL RECORD: n_fields 2; compact format; > info bits 0
0: len 8; hex 8000000000000093; asc ;;
1: len 5; hex 999b870acb; asc ;;

For the second transaction

*** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 57008 page no 6 n bits 224 index subscriber_id of table customer.subscriber_id /* Partition monthly_rest */ trx id 1340730 lock_mode X locks rec but not gap

Record lock, heap no 136 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 8; hex 8000000000000093; asc ;;
1: len 5; hex 999b870acb; asc ;;

I've only put an excerpt of the lock details here.

It seems like the subscriber_id field has been locked with a next-key lock.

Is it possible even with READ-COMMITTED in mysql 5.7 to get a next-key lock ?

Could that be due to using a non-unique key ?

It would be an immense help if someone can provide an explanation

psaw.mora
  • 868
  • 1
  • 7
  • 18
  • Many questions get "noisy" when people paste too much, but I think it might be best if you don't redact anything, here. Based on what is shown, it appears that the contention is over a lock on the row for 0x93 (decimal 147) which does suggest a next-key lock, since that value isn't here. Consider adding the full output in case there is something else that may be useful -- it seems like there must be. – Michael - sqlbot Nov 18 '16 at 12:31
  • I'll paste the complete output asap. Is it possible in MySql 5.7 to get a next-key lock with an isolation level of READ-COMMITTED ? – psaw.mora Nov 18 '16 at 12:57
  • Here: http://dev.mysql.com/doc/refman/5.7/en/innodb-locks-set.html they write: `UPDATE ... WHERE ... sets an exclusive next-key lock on every record the search encounters. However, only an index record lock is required for statements that lock rows using a unique index to search for a unique row.` – krokodilko Nov 19 '16 at 21:41
  • Thanks for the reply. This helped a lot. – psaw.mora Nov 21 '16 at 05:18

0 Answers0