Locks in SQL Server 2005

When multi user access database simultaneously or we can say that when two or more user wants to change a piece of information at the same time. Locks are a mechanism for preventing a process from performing an action on an object those conflicts with something already being done on that object. For example user one tries to read date while other user making changing information on the same data. Then guess which data user one see, Changed or unchanged? Data may be logically Incorrect. So dealing with data concurrency in database is a process called locking. SQL Server lock manager do this locking.

Lockable Resources

• Table
• Page
• Key
• Row

Lock Escalation and lock effects on performance
Escalation is all about recognizing that maintaining a finer level of gratuity (means a row lock instead of page lock), when the number of item being locked is small. Escalation is based on the number of locks rather then the number of user in a single time.
Lock Modes: means what level of resources you are locking or what lock mode your query is going to acquire.
1) Shared Lock: is used when you only need to read the data that is you don’t want to change. It prevents user from dirty reads.
2) Exclusive Locks: these are not compatible with any other lock or we can say that we can’t implement this if any other lock exists or vice versa. This is used to preventing from updating, deleting or doing whatever at the same time.
3) Update Lock: are hybrid between shared locks and exclusive locks. It is a special kind of placeholder. This means that until you go to make a physical updates you need a shared lock. At the time of physical update, you will need an exclusive lock.
4) Intents Locks: is a true placeholder and is meant to deal with the object hierarchies. For example when you have a lock established on a row. But some one wants to establish a lock on a page, or extent or modify a table. You would not want another transaction to go around yours by going higher up the hierarchy. Without indent lock the higher level object would not even know that you had the lock at the lower level.
5) Schema Locks: This lock have two main forms:

· Schema Modification Lock (Sch-M):When you have this lock then no query or other create, alter or drop statements can be run against this object for the duration of Sch-M locks.

· Schema stability lock (Sch-s): This is very similar to a shared lock. The purpose of this lock is to prevent a Sch-M since there are already locks for other queries (create, alter, drop statements) active on the object. This is compatible with all other lock types.

6) Bulk Update Lock: is used to allow parallel loading of data that is locked from
any other normal T-Sql statement activities. But multiple bulk insert or BCP operations can be performed at the same time.

7) Serializable/HOLDLOCK: Once a lock is held by a statement in a transaction that lock is not released until the transaction is ended (via rollback or commit). Any insert are also prevented if the inserted record would match the criteria in the where clause in the query that maintain the lock (No Phantoms). This is the highest isolation level and guarantees absolute consistency of data.
8) READUNCOMMITTED/NOLOCK: means no lock or does not honor any other lock. But while a very fast option. It can generate dirty read.

Deadlock
It is not a type of lock in itself where it is a situation when a paradox comes or we can say that when a transaction want a resources and that resources hold by other transaction and both are locked in own side. Means no any transection works.

· By using transaction and lock we can minimize deadlocks and improve the overall performance of our system.