Pessimistic locking of record?

Question

I am creating a WCF Web Service, for a Silverlight application, and I need to have a record to be Read/Write Locked when Modified.

I am using MySQL version 5.5.11.

To be more specific, i would like to prevent a request from reading data from a Row when it is being modified.

The two SQL commands for UPDATE and SELECT are actually pretty simple, something like:

Update(should lock for write/read):

UPDATE user SET user = ..... WHERE id = .....

Select(should not be able to read when locked from the query above):

SELECT * FROM user WHERE id = .....

Here is what i tried but it doesn't seem to work or lock anything at all:

START TRANSACTION;
   SELECT user
   FROM user
   WHERE id = 'the user id'
   FOR UPDATE;

   UPDATE user
   SET user = 'the user data'
   WHERE id = 'the user id';
COMMIT;

Why even perform this in a transaction? `UPDATE` runs atomically all on its own. That said, what you're trying should still work; what makes you think that it doesn't? — eggyal, Jun 06 '12 at 17:55

score 0 · Answer 1 · answered Jun 06 '12 at 18:00

0

How are you determining that it's not locking the record?

When a query is run over a table with locks on it, it will wait for the locks to be released or eventually timeout. Your update transaction would happen so fast that you'd never even be able to tell that it was locked.

The only way you'd be able to tell there was a problem is if you had a query that ran after your transaction started, but returned the original value for user instead of the updated value. Has that happened?

I would have just put this in a comment but it was too long, but I'll update this with a more complete answer based off your response.

answered Jun 06 '12 at 18:00

Brandon Moore

8,590
15
65
120

Hi, should the SQL Query, in my original post, be also preventing other query from reading data from this row? What seem to have happen in my case is that while a record was in the process of being modified, another record was able to read data from it AND/OR returned the old value. This is a web application, don't know if it matter. – SlashJ Jun 06 '12 at 18:12
Your transaction would last a small fraction of a second. The likelihood of another query even being able to start during that small space of time is pretty small to begin with. Maybe the query that's returning the old value started before your update transaction started. It doesn't sound like you've verified that this is really the issue yet, and my suspicion is that you problem lies elsewhere. – Brandon Moore Jun 06 '12 at 18:31
@Armz Also, I don't use mysql so I'm not an expert on how it does locking, but I think you are probably misunderstanding when you need to use FOR UPDATE. If you are only updating one table then the rows should be locked and you don't need to lock them yourself. The problem is when you need to, for example, make sure multiple tasks occur before releasing the lock on a table. To keep the rows locked in between multiple update statements you would use FOR UPDATE to keep them locked the whole time. But for a single update statement it is redundant and unnecessary I believe. – Brandon Moore Jun 06 '12 at 18:37

score 0 · Answer 2 · answered Jun 06 '12 at 18:25

0

MySql uses multi-versioned concurrency control by default (and this is a very, very good behavior, instead of MSSQL). Try to use locking reads (LOCK IN SHARE MODE) to achieve what you want.

answered Jun 06 '12 at 18:25

Dennis

37,026
10
82
150

Pessimistic locking of record?

2 Answers2