What are the key points to avoid table locks?

Question

MySQL is using at my company and most of the database administration works on trying to solve the table locks. What are the key points to avoid table locks(I mean at database side and even at software -Java-)

Answer 1

First a quick recap on what a deadlock is. Let's say you have two sessions, 1 and 2, and two resources, A and B. The following sequence then occurs:

1. Session 1 gets a lock on resource A
2. Session 2 gets a lock on resource B
3. Session 1 requests a lock on resource B, but has to wait until session 2 has released it. We are fine up until this point
4. Session 2 requests a lock on resource A - but session 1 is blocked and can never go forwards to release it. Deadlock occurs.

There is only a very limited amount your DBA can do about this because this situation originates in the application, it merely manifests itself in the database. It is best to adopt a convention of always locking resource in the same order. If we had the convention of always locking in the order A, B then our scenario would be:

1. Session 1 gets a lock on resource A
2. Session 2 requests a lock on A, but has to wait for session 1
3. Session 1 gets a lock on resource B, completes its work, and releases both
4. Session 2 gets locks on both A and B, completes its work, and releases both. No deadlocks.

Answer 2

You can also change your lock granularity by changing your MYSQL DB engine. InnoDB gives your row level locking which is finer when compared to MyISAM's table level locking.