Re: Row locking in ODBC

In article <dil.admin.1288.000B981F_at_mhs.unc.edu> dil.admin_at_mhs.unc.edu (David I. Laudicina) writes:
>From: dil.admin_at_mhs.unc.edu (David I. Laudicina)
>Subject: Re: Row locking in ODBC
>Date: Tue, 6 Dec 1994 11:35:34

>>I am developing a application for a client
 

>>using Visual Basic and ODBC and have come
 

>>across an interesting question.
 

>>How does ODBC handle record locking.

>Wouldn't the locking strategy be determined by the database server you are
>accessing rather than ODBC?
>Thx Dave L

With native VB database controls you cannot use Oracle's 'select for update' to lock a row when you retrieve it. So the lock will be applied when the update is issued to the database. As I recall with VB the update is imediately followed with a commit. This means that another user may have queried the row and the first user could elect to make an update based on old data.

User A selects.
User B selects.
User B updates.
User A updates.

This is not always a problem as with some data the likelyhood of two users updating the same row at the same time is low. But if the possibility is high you need to either start using a library like Q&E or Glue so you can lock the row on select. (I've also heard that Oracle Objects has some stuff for VB) Or you could work out something in the application where just before the update you select the row again and compare it to the original values to see if anyone else has modified it.

There are draw backs to the select for update, it prevents anyone else from updating the row. When users query a row onto the screen and wanders off it remains locked. SQLForms handles these issues soemwhat differently, when you query the row it is not locked. If you type in a database field on the form it reselects for update to get the lock. Sometimes you see the slight pause in the form while this happens. The advantage is that the row is not locked unless you actually start changing the data. A user can still cause a problem by querying a row, starting to type, and then wandering off.

Lot to locking, huh?

Lawrence.... Received on Wed Dec 07 1994 - 13:21:32 CET