Updating column having index causing Deadlock 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
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.