Very Computer

1. Pessimistic locking and Lock escalation

We are using the RowLock and updLock locking hints together with Set
Lock_Timeout 0 to create pessimistic locking of rows on a table (SqlServer 7
sp1 from VB6.0 sp3 ADO2.1 NT4sp3 Win98 Clients)

Whilst I am aware that much of the microsoft documentation says "dont lock
over user input" this seems to be the only way to create pessimistic locking
in order to prevent two users beginning to edit the same row.
The problem begins when a user causes an insert or delete, or locks too many
records as SQLServer escalates the rowlock to either page or table locking.
Page or table locking then makes records unavailable to other users.

Can anyone tell me either (1) how to prevent rowlocking escalating to page
or table locking when a delete occurs OR (2) a workable system of gaining an
exclusive lock on a row thus preventing 2 users editing the same row?

Optimistic (last one in wins) locking is not practical as editing a row or
batch of rows may take 1/2 hour. The users cannot spend 1/2 hour editing a
row just to be told "Sorry, another user has already edited your rows -
Byebye changes!"

Our legacy software used change indicators which were set on a row at the
start of an intention to change the row. This is not practical due to the
huge clean up operation to unset change indicators each time the client pc
crashes. We were excited at the concept of sqlserver transactions
automatically rolling back uncommitted changes thus ensuring stable data.

There has to be a simple answer to this, hasn't there?

--
Chris Petchey

2. pgsql/src/backend/parser parse_target.c

3. pessimistic locking --locks disappear with 2 RecordSets

4. YAML????

5. NewEra question regarding manualSF.setText() triggering lock under pessimistic locking

6. Auditing user's access

7. Setting Pessimistic record locking

8. Database is not open message in webbrowser, but it is opened and worked allright before

9. ADO21/JET 4: Making your own pessimistic (record) locking....a suggestion

10. RDO and pessimistic record locking-How?

11. Q: Pessimistic Record Locking

12. Pessimistic Record Locking - Can It Be Done ?

13. Pessimistic record locking using Interbase