Re: How do DBMS handle optimistic locking?

On Jul 28, 5:03 pm, Gilles Ganault <nos..._at_nospam.com> wrote:
> Hello,
>
> I was wondering: How do server-based DBMS (PostgreSQL, Oracle,
> etc.) handle concurrent updates with optimistic locking?
>
> Say user A sends a SELECT over the LAN to the server to grab an
> employee's personal details, and feed them to a grid object on the
> client. While he's making changes to it, user B is also making changes
> to the same employee, and sends his update while User A is still
> working.
>
> When user A sends his updates... the DBMS server will generate an
> error, and ask user A to choose which changes to keep: His or User
> B's.
>
> How do DBMS's handle this? Do they use some kind of ticket to keep
> track of what state the table was in when user A sent his SELECT, and
> when the client wants to perform an UPDATE, the DBMS compares this
> ticket to the local ticket, and sends an error if they don't match?
>
> Thank you.

This is one of those things that vendors are allowed to implement differently. And because the do, it is one of those things than makes porting between RDBMSs a challenge.

Oracle's model is described here:
http://download.oracle.com/docs/cd/B19306_01/server.102/b14220/consist.htm#i13945 In Oracle, if the developer want to use pessimistic locking, [s]he can use the 'FOR UPDATE' clause of the select statement to reserve the row during the read. Received on Sun Jul 29 2007 - 02:24:05 CEST