Single entry lock instead of table lock on SQL-Server? how?

recall

Hello,

I heard that I can configure SQL-Server, so that it locks only ONE single entry but not the whole table if a user access the table!

How can I do this ?

Thanks

bbrown

SQL will use record level locking only if you are accessing the table with the primary key.

Examples:

Customer.GET(CustID);

Customer.SETRANGE("No.", CustID)
Customer.FIND

IF you are accessing several records with a repeat, SQL may change to a page lock.

recall

hmmm... but I heard that it's possible to change the default behaviour of the SQL-Server ?!

bbrown

Not globally that I am aware of. Why would you want to?

Your code can have an impact on locking.

SQL uses multigranular locking. SQL will choose the locking level appropriate to the task.

The available levels are (lowest to highest granularity:

RID
Key
Page
Extent
Table
DB

The lower levels (RID) require more server resources. If your table has 10,000 records, it will require more memory to hold 10,000 record locks than 1 table lock.