0

My update statement:

Update XyzTable set state ='second' where pgmNumber = 'prog123' and state = 'first'

Both state and pgmNumber columns has individual index.

Deadlock is happening even if above query running concurrently with different pgmNumber.

There is single row for each pgmNumber

What would be the best way to resolve the issue

5
  • Innodb should not lock the hole table, what are you using for your table?
    – nbk
    May 8, 2020 at 11:20
  • It's mysql db, state column is having index so two concurrent updates are getting in deadlock because of page lock due to state column index May 8, 2020 at 11:23
  • Please add more details. For example, output of LATEST DETECTED DEADLOCK section from INNODB STATUS. Do your transactions contain more than one statement? May 8, 2020 at 13:01
  • Yes it contains more than one statement & it would be difficult to separate them out. Page level lock is happening because of state index column, removing index will solve the problem but we can't do, is there some other way? May 8, 2020 at 14:50
  • Dup of stackoverflow.com/questions/61677988/… , where it probably belongs.
    – Rick James
    May 10, 2020 at 5:46

1 Answer 1

1

What would be the best way to resolve the issue

You should create a compound index (unique if appropriate) on (pgmNumber, state). The index only on state is useless because of the low cardinality of that domain.

Generally only one index can be used for executing statements like yours, and in your scenario: "Both state and pgmNumber columns has individual index" it causes locks on more rows than necessary, thus leading to deadlocks.

5
  • Thanks @mustaccio, but it's pretty large table & we can not do any change on index level as its being used at many places & by many team. So would there be any other solution May 8, 2020 at 11:56
  • 2
    Neither the table size nor it being used by other applications has any bearing on your ability to create proper indexes for it. Deadlocks are an indication of flaws in your application or database design, they cannot be resolved by magic.
    – mustaccio
    May 8, 2020 at 12:29
  • Yeah I understand that but if changes then it may cause issue to other apps, if we use index hint in query like use index pgmNumber or force index pgmNumber will that solve deadlock problem? May 8, 2020 at 13:50
  • That's exactly what you have your test environment for, is it not
    – mustaccio
    May 8, 2020 at 14:33
  • It's happening very rarely so hard to reproduce, though we are trying but if some one already tried hint index to avoid deadlock then it would give some idea. What's your opinion will that help? May 8, 2020 at 14:46

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.