My understanding is that SQL2k handles locking automatically. Is that sufficient in real-world situations or do you use manual locking as the need arises? I am writing a fairly simple ASP/SQL database for < 10 users.

Peter Riley

SQL Server indeed handles locking automatically. I would guess that for 99.9% (or more!) of the cases this is sufficient. There are ways to tell the lock manager to handle locking differently than it otherwise might. See "locking, hints" in BOL.

Be careful if you use these hints that you know what you are doing. Frequently, you'll make things worse if you override the default behavior. You'll either adversely affect concurrency, or you'll return dirty data you might not really want.

Generally speaking, less than 10 users does not a concurrency problem make, so I would strongly recommend you leave things alone. Concentrate on getting the transactions right, and leave the locking to the system.

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

Thanks, Jeff. I was unsure because many of the code samples I have found include something like adLockReadOnly as part of the database connection. Maybe that's an Access thing though?

Peter Riley

Well, 'adLockReadOnly' is a value for the ADO Recordset LockType property, which affects the behavior of the cursor (recordset) and is not, strictly speaking, a SQL Server thing (or an Access thing).

The various settings of this property affect how OLEDB handles recordset editing by establishing and/or maintaining locks (in the database) on recordset rows when the recordset is opened for editing.

I never use recordsets for editing, perferring instead to do all database access through stored procedures, so I never use any setting other than 'adLockReadOnly' which basically declares my recordset read-only, minimizing both client and server resources.

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com