After a BeginTrans (with ADO 2.7 / SQL Server 7), and an operation (edit, add, or delete) on a table, any other user can even open that table until the CommitTrans or RollbackTrans.
They get a time-out error.

As the user that started the transaction, commits or rolls back, the other users can finally view the results of their pending queries.

I would like not to lock ALL the table during a transaction.
Is this possibile?

Many thanks in advance.

Yes, No, Maybe...

I am more familiar for MS-SQL 2000, but I would guess that 7 is similar or the same.

You can give locking hints to SQL Server.

e.g.

  UPDATE myTable WITH (ROWLOCK)
or
  UPDATE myTable WITH (PAGELOCK)

SQL Server goes into detail on locking hints.

Here is the trick. If too many rows are affected, it will lock the table anyway. Also if you suggest a rowlock, it has been my experience that SQL Server will jump to a tablelock, even if all the affected rows are in one page, so you really want to know the size of what you are updating before determining which lock to use.

The best thing to do if you havn't already, is to make sure your transaction is fast. Don't start it until you need it, and commit or rollback ASAP.


John R Lick
[email protected]