How mysql locks tables for updates and inserts

Posted on 2014-01-08
Medium Priority
Last Modified: 2014-01-08

I am trying to get a better understanding of how mysql locks tables during inserts and updates.

If one database connecting is writing/updating a record and creating a row-level lock on that record and another database connection then tries to access the same record:

1) can second database access the record even if it is for a select? I presume not
2) How long does it wait for the record to be released or how many retries does it have before it gives up. Can these properties be configured in some database variables?

Question by:andieje
1 Comment
LVL 61

Accepted Solution

Kevin Cross earned 2000 total points
ID: 39765622
http://dev.mysql.com/doc/refman/5.6/en/commit.html (TRANSACTION isolation)
http://dev.mysql.com/doc/refman/5.6/en/lock-tables.html (LOCK tables)
http://dev.mysql.com/doc/refman/5.5/en/innodb-lock-modes.html (INNODB lock modes)

The above should help, but to answer your questions my understanding is another session can read from locked row using read consistent transactions.  It will give the committed value of the record and not the change in process.

Regarding the wait time, there is the innodb_lock_wait_timeout configuration.

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

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.
Containers like Docker and Rocket are getting more popular every day. In my conversations with customers, they consistently ask what containers are and how they can use them in their environment. If you’re as curious as most people, read on. . .
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

627 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