Re: HELP on ODBC and Locking

Graham, Jens

the point is, that in an interactive application, you can't issue locks, since the user will probably load the "record" and go for lunch, thus leaving everyone sitting there with a locked row, which will block their queries.

We usually go for "optimistic locks" with a first-save-wins strategy.

Every one of our tables has a timestamp column, which we modify on each UPDATE or INSERT. Now when we UPDATE, we include the timestamp we obtained through SELECT into the search condition along with the PK.

Thus, the result is:

UPDATE <Table>
SET <Col> = <Value>,
<Timestamp> = <NOW>
WHERE <PKCol> = <PKValue> AND <Timestamp>=<SelectedTimestamp>

This scenario usually works well with customer oder order data which is manipulated manually, because, from an organizatioanl standpoint, two people shouldn't access the same file at the same time.

The collision strategy is first-save-wins, meaning that the user who saves his/her changes wins and the other users get a message that the record has been updated by someone else.

Clemens Vasters
reply: WinWizard_at_msn,com

(no, Vasters is not a common name in Germany, although it might seem so :-)

• * * * Original Message * * * * In article <33ef4ba4.581161_at_news.u-net.com> Graham Miller of a u-net customer writes:

Hello Jens,

If you use 'passthru' SQL, then use the 'for update of ....' clause on your queries to acquire locks on the rows rather than updating them. To avoid the blocking, use the 'NoWait' option on the query / update etc. You need to test the SQLCode to determine if another user already has the row locked.
[...]

Jens.Vasters_at_ac.cybercity.de (Jens Vasters) wrote...

| Hi,
|

 [...]
| The locking method of the ODBC driver is SQL_CONCUR_VALUES. To lock a
| row we make a dummy update on a value, getting the row locked until we
| submit the final commit or rollback.
|
| When the row is already locked by another user (update of user 2
| between select and update of user 1), the update gets blocked until
| the final commit or rollback of user 2. In an interactive application
| this is not acceptable. Have any idea for this problem?
|
| Thanks Volker

• * * * End Original Message * * * *