Prevent same row from being updated by another user

[TakaShi^^]

Hi there,

I have a question about concurrency in Java - JDBC.

Currently i have this SQL query, i don't use Hibernate in my codes so im looking for other alternative. I read about optimistic locking but not sure how to implement it

When two user click Update button at the same time.

select * from myTable where id = 1
update myTable set test = '1' where id = 1

When user A execute the following

select * from myTable where id = 1
update myTable set test = '1' where id = 1

The test column updated to 1

When user B execute the following

select * from myTable where id = 1
update myTable set test = '2' where id = 1

The test column updated to 2

In this case, I'm trying to prevent user B from updating test column to 2 as the row has already been updated to 1 by user A

Is there a way I can prevent user B from updating?

 

[cMu]

use the USER ID in your UPDATE statement

 

[-Jumong-]

You can also put if its default 0 where test=0 this way it will only update values with test=0

 

[cyberinferno]

You can use the ROWLOCK feature which is available in most SQL databases to lock the row while one update is under progress so that only one process updates the row at a time!