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;

Answer 1

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.

Answer 2

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.