Solved

How mysql locks tables for updates and inserts

Posted on 2014-01-08
1
409 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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

This guide whil teach how to setup live replication (database mirroring) on 2 servers for backup or other purposes. In our example situation we have this network schema (see atachment). We need to replicate EVERY executed SQL query on server 1 to…
Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
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…

785 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