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-)
- Anybody can ask a question
- Anybody can answer
- The best answers are voted up and rise to the top
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:
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:
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.