A question about Optimistic Locking

[femto Zheng]

Hello all,for Optimistic Locking, I believe 

the implementation is like this:

rails will issue the following statement to database

Update user set name="a",version=2 where id=1 and version=1

but the problem is, generally database will have transaction Isolation

(I believe Oracle do),

so when Thread 1 issue that statement to database but not commited,

Thread 2 will also issue that statement to database, because transaction Isolation,

thread 2 will not see this user as version 2 but still version 1(unless thread 1 commited)

(otherwise thread 2 is reading uncommitted which is not correct),

then thread 1 commit, thread 2 commit,

then all of a sudden, all threads are happy, the user record is still being modified by 

two threads, the optimistic locking doesn't work,

Is this correct?

Correct me if I'm wrong.

--

http://hi.baidu.com/femto

https://github.com/femto

[Frederick Cheung]

Don't know about oracle but for mysql to update the row you'd have to acquire a lock on it (an exclusive lock if my memory is correct) - the second thread would have to wait for the first transaction to be committed in order to be able to make its update 

Fred