Fwd: $431: MS SQL Record Locking

[Bastiaan Olij]

Hi Ben,

Things have probably changed in recent years (I haven't used MS SQL for
over 6 years now) but as I recall MS SQL inherited page locking from
Sybase. Once data is locked it can't be access and any attempt to access
the data outside of the session that locked the data would result in a
block and eventually deadlock.

The approach we often took was to write our software in such a way it
would only apply changes to columns that were changed instead of
updating the entire record to what was stored in memory removing much of
the need to lock data. The other common thing to do was to make sure you
timestamped records so you could see if another user had changed the
data before you commit your own changes.

In the end though, for those parts of the system where we needed to
prevent two users accessing and modifying the same record we rolled our
own locking system. We simply had a table called FLOCK with a column for
the table name, a column for the primary key and a column for process id
that locked the record.

We simply checked if there was a record in this table for whatever
entity we wanted to lock and if not, insert that record.
There was some cleanup behind the scenes to ensure any locks were freed
up when a session terminates.

Cheers,

Bas

On 29/08/14 3:53 PM, Ben Price wrote:
> Hi List,
>
> I am really struggling with record locking and MS SQL
>
> The application I am working on uses modal enter data
>
> on DB2
> on opening window  "select for update" is run on an cursor instance
> sessionobj which locks the record
> user can then modify data
> The user then has 2 options kBMok and kBMCancel
> If user clicks kBMok this issues an "update record" on cursor instance
> sessionobj which releases the lock
> If user clicks kBMCancel this just closes window. Lock is released
> when cursor goes out of scope
>
> on MS SQL
> Select for update does not exist in MS SQL and the only way I have
> found to lock records is using manual transactions. Deadlocks are
> produced if a sessionobj goes out of scope without issuing a "commit
> tran" or "rollback tran"
>
> I have 500+ windows in the application so changing them all to close
> the transaction in the right spot (eg needs to be called before any
> methods are called on parent windows will take many days even weeks to
> change and test)
>
> Does anyone know how to how to get MS SQL record locking working with
> modal enter data?
>
> Does anyone know how to ensure a scroll_locks (pessimistic) cursor is
> created in omnis instead of a (optimistic) cursor that does not lock
> records.
>
> All the best,
>
> Ben Price