Solved

Problem in table unlocking

Posted on 2011-09-03
2
364 Views
Last Modified: 2012-05-12
Some times while dml operation is done on a table, during that process that table is implicitly locked. But it is not implicitly unlocking. This is not happening to all the tables in the module, but only to a specific table. We are working on Mysql 5.1 server and the table type is InnoDB. The table is locked till you explicitly unlock it. I am not getting solution to it.

Any one please let me know why this is happening and what is the solution.
0
Comment
Question by:darien_software
2 Comments
 
LVL 21

Accepted Solution

by:
oleggold earned 500 total points
Comment Utility
from
http://forums.mysql.com/read.php?97,151989,151989
"The correct way to use LOCK TABLES with transactional tables, such as InnoDB tables, is to set AUTOCOMMIT = 0 and not to call UNLOCK TABLES until you commit the transaction explicitly. When you call LOCK TABLES, InnoDB internally takes its own table lock, and MySQL takes its own table lock. InnoDB releases its table lock at the next commit, but for MySQL to release its table lock, you have to call UNLOCK TABLES. You should not have AUTOCOMMIT = 1, because then InnoDB releases its table lock immediately after the call of LOCK TABLES, and deadlocks can very easily happen."
0
 

Author Comment

by:darien_software
Comment Utility
Thanks oleggold.
I want a little more clarification from you. I have gone through the link you have provided. I am unable to understand the statement 'InnoDB releases the lock at the next commit'. What is next commit ?
0

Featured Post

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Join & Write a Comment

Foreword This is an old article.  Instead of using the MySQL extension that was used in the original code examples, please choose one of the currently supported database extensions instead.  More information is available here: MySQLi / PDO (http://…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now