SQL Server

In a multi-user environment, there are two models for updating data in a database: optimistic concurrency and pessimistic concurrency.
Pessimistic concurrency involves locking the data at the database when you read it. You essentially lock the database record and don't allow anyone to touch it until you are done modifying and saving it back to the database. Here you have 100% assurance that nobody will modify the record and while you check it have it checked out, out. aAnother person will have to wait until you have made the your changes.
By default, SQL Server controls lock escalation, but you can control it yourself by using lock optimizer hints. Here are some lock escalation hints you may want to consider:
· ROWLOCK This hint guides tells SQL Server to use row-level locking instead of page locks for INSERTS. By default, SQL Server may perform as a page-level lock instead of a less intrusive row-level lock when inserting data. By using this hint, you can guide tell SQL Server to always start out using row-level locking. But, this hint does not prevent lock escalation if the number of locks exceeds SQL Server's lock threshold.
· SERIALIZABLE (equivalent to HOLDLOCK) applies only to the table specified and only for the duration of the transaction, and it will hold a shared lock for this duration instead of releasing it as soon as the required table, data page, row or data is no longer required.
· TABLOCK specifies that a table lock to be used instead of a page or row level lock. This lock will be remained held until the end of the statement.
· TABLOCKX specifies that an exclusive lock will be applied held on the table until the end of the statement or transaction, and will prevent others from reading or updating the table.
· UPDLOCK specifies that update locks will be used instead of shared locks, and will hold the locks until the end of the statement or transaction.
· XLOCK specifies that an exclusive lock be used and kept activated held until the end of the end of the transaction on all data being processed by the statement. The granularity of XLOCK will be adjusted if it is used with the PAGLOCK or TABLOCK hints.