Does UPDATE wait until record is unlocked?

[bulrush]

Mysql 4.1.11

 

Let's say I have an edit screen where the user1 edits an already existing record. The data is grabbed using a SELECT query. After he is done editing he clicks the Save button which calls an UPDATE query to save the record.

 

Let's say this is a multi-user system. If the record is being edited by user2 when user1's UPDATE query is run, is it locked?

 

Does Mysql try to retry the UPDATE until the record becomes unlocked?

 

I guess my main question is, does Mysql handle locking automatically or does the programmer have to do the locking? Like when user1 edits the record, I do a SELECT statement to get the data. At this point I would like to lock the record so no one else can edit it. (They can view it only.)

 

 

 

[corbin]

Yes, locking in MySQL is handled automatically.

 

http://dev.mysql.com/doc/refman/5.0/en/internal-locking.html

[bulrush]

Ah, so there potentially could be confusion if UserA and UserB are looking/editing the same part at the same time, and UserA hits the Save button, then UserB hits the save button on the same part. UserB's info will overwrite that of UserA.

 

[PFMaBiSmAd]

The internal locking that is described at that link deals solely with how the server manages concurrent queries. It has nothing to do with how your application prevents multiple people from making changes to the same information.

 

In fact, since transactions and locking are per client session and editing (reading, displaying, possibly changing, and updating) information involves multiple page requests, each with a separate database client session, you must manage this at the application level.

 

You need to request/set a piece of data that indicates someone wants to edit the specific information. If the request/set is successful and matches the requesting person, that person has obtained the right to edit the information. When he is done (or with a timeout in case he aborts the operation), the request for the edit is released and someone else requesting the ability to edit that specific information would succeed when they make the request.

 

Edit: I guess I would ask, what type of information is this, because you may in fact not want to update existing information, but add (INSERT) records with each change, both so that you have a record of who made a modification and when and so that you have the source data that goes into a value rather than just a single value that someone could have made an error editing using the read/update method.

[fenway]

In fact, since transactions and locking are per client session and editing (reading, displaying, possibly changing, and updating) information involves multiple page requests, each with a separate database client session, you must manage this at the application level.

Transactions are not per session -- ACID compliance ensures that this is not the case.