How to implement row locking in MYSQL

[Rizi004]

Hello Everyone,

i have table that have some code list if user enter same code from 2 different systems and enter at the same time both user's get data, what i want is the first one will get the data the second one will only see the message that this code is already used.

the status of the colum is 'available' but if code is valid second query update the status to 'taken' but due to concurrent request two user get the same prize id.

Stored function is best for this or http://dev.mysql.com/doc/refman/5.5/en/innodb-locking-reads.html

Thanks
Rizwan

[ryantroop]

You would likely need a second table that keeps track of the "state" of the record being read. Either that, or a column that flags a "read" state. Using a separate table, however, you can also relate a user id or other relevant information.

I am not 100% certain, but I do not think there is a built in way to lock a row. You may be able to be clever and use some sort of trigger, but that just goes back to having a read state anyway...

The article you refer to is talking about dirty reads, meaning you are updating a column in the same row you are reading, which usually would cause a deadlock (or a table lock) until the update is completed, and then give you a clean read of the updated material.

This would be used in something like the column that flags the row as read. However, I personally would not go this route...

[Rizi004]

Thanks for your reply

but the situation is i have 3 tables one have user registration and one is prize timer and third is for winner

Now the contest have high traffic more than 20 user registered in one minute so winner depends time if time is save in database like 4:10:06 AM so no one will be the winner before 4:10:05 AM so i want to read the time and update the time status to taken. if 3 request come at 4:10:05AM so only first one will read other request will read the next row not that row.

After reading the time row i have to update the status of that row.

i have developed a stored function and it works 95% correct but sometime when request come at same time than two user's get the same prize id.

Thanks
Rizwan

[ryantroop]

It is highly unlikely you will get a time stamp that perfectly matches another down to the millisecond. However, you can always do your query something like

select MIN(id) where date = 'mydate'

That way if you have an exact match, only the first id will be returned, and since it's first in the table they were obviously first to submit.

[rubberman]

Having multiple readers of a table simultaneously is not a problem and you should not try to lock the table. Having one read it while another is trying to write to it is another issue. Most database implementations will deal with this automatically. If one is updating, but another tries to read the same record, if the system is set to allow "dirty reads", then the second will get the old version of the table until the writer has committed the changes. If it isn't so set, then the reader in such a case will be queued until the data is committed, but if the reader started before the writer, then it will get the old data, and the writer will be blocked until the read is complete. Basic DBMS stuff. MySQL deals with this pretty well as I recall, but a lot of the behavior is predicated upon the configuration options of the database instance that you are using, and there are a lot of those options.

After reading your second posting on this, it is obvious that there is a "dirty read" situation happening. If you can alter the configuration of the database to disallow this, then you don't need to do anything.