Solved

How mysql locks tables for updates and inserts

Posted on 2014-01-08
1
413 Views
Last Modified: 2014-01-08
Hello

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?

Thanks
0
Comment
Question by:andieje
1 Comment
 
LVL 59

Accepted Solution

by:
Kevin Cross earned 500 total points
ID: 39765622
REFMAN:
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.
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Foreword In the years since this article was written, numerous hacking attacks have targeted password-protected web sites.  The storage of client passwords has become a subject of much discussion, some of it useful and some of it misguided.  Of cou…
When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

821 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