Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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-)

share|improve this question
    
Do you mean a) deadlocks or b) excessive time waiting for a lock? –  Gaius Apr 27 '11 at 11:06
    
@Gaius they are deadlocks. –  kamaci Apr 27 '11 at 11:21
    
You'd better search the site for deadlocks: Deadlocks on DBA.SE. There are plenty questions and suggestions about how to handle them. –  Marian Apr 27 '11 at 12:16

2 Answers 2

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.
share|improve this answer
1  
Also try to keep your transactions as short as possible. If you have slow running queries, tune them up! –  datagod Apr 27 '11 at 15:41
    
That will manifest itself as time spent waiting for a lock - not as a deadlock –  Gaius Apr 27 '11 at 15:46

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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