Strategy to approach the deadlocks?

Question

I came across the database deadlock at production. On anaylsis i found locks were taken in reverse order by two thread. For example :-

1)Thread 1 :- Its first got the exclusive row lock on table 1 rows and then trying to acquire shared row lock on table 2 rows.

2)Thread 2:- Its first got the exclusive row lock on table 2 rows and then trying to acquire shared row lock on table 1 rows.

Hence dead lock happened.

Fortunately i was able to solve it under thread 2 by acquiring the lock in same order as in thread 1 i.e now both the threads tries to acquire the lock first on table 1 and then on table 2. So deadlock solved.

I was fortunate here it was was possible to change the lock order here as it involved the minor change. But i am sure in some of the scenarios it is not possible either because of business functionality or it may require major change in application. So i could think two other ways to solve these kind of deadlock(whether it is database locks or java locks). They are :-

Another way i can think of are

1) Pessimistic Locking where to acquire lock in advance on required resorces

2) keeping the reentrant lock in cache before starting operation in thread 1 and thread 2 can't proceed till first thread release the lock. But i am not convinced with this approach as it will fill the application with this kind of code. Also developer can forget to release lock.

So i am wondering what are the other alternative ways to solve deadlocks. Any ideas ?

Answer 1

You can create table(locktable) in database and store primary keys of locked records in this table.
Before update main tables try to find record in locktable.
If no record found you will create and select for update record in locktable.
Other threads and applications check record in locktable before updating main tables.
Bonus of this way - you applications don't lock data in main tables, and other process may select data from main tables.

How do you ensure the serializable access to this table?

All applications insert lockrecords and lock pessimistic(make select for update) this records.