Optimistic locking in a single update statement

Question

If I have two threads updating a database in separate transactions with statements that look like

Thread 1

UPDATE people set id='12346' WHERE name='Jeff'

Thread 2

UPDATE people SET name = 'Jeff' WHERE id='12345'

Obviously there is a race here. If Thread 1 changes the id before Thread 2, 12345 will be an invalid ID (assuming no one else is using it).

What I'm wondering is, when using optimistic locking, is this a situation in which an optimistic lock error may be thrown (e.g. a stale record error)? Does the UPDATE/WHERE constitute a read and write or is each UPDATE executed an atomic statement (or does it vary by database)?

updates are atomic statements on SQL Server, and I believe most other major RDBMS — Mitch Wheat, Nov 06 '12 at 01:12
Duplicate? http://stackoverflow.com/questions/2133393/is-update-with-nested-select-atomic-operation — durron597, Nov 06 '12 at 01:14
That looks to be a slightly more specific question. This one appears to pertain only to the handling of concurrent updates. — Gian, Nov 06 '12 at 01:16

score 1 · Accepted Answer · answered Nov 06 '12 at 01:12

1

It would be very poor form for a DBMS to not implement updates as atomic statements, but it's worth verifying this for you particular environment to be sure.

answered Nov 06 '12 at 01:12

Gian

13,735
44
51

Thanks, that'w what I was assuming but wanted to confirm. – Jeff Storey Nov 06 '12 at 01:13

Optimistic locking in a single update statement

1 Answers1