A LOCK is a mechanism that prevents destructive interaction between two simultaneous transactions or sessions trying to access the Same Database Object (Table).
A LOCK can be achieved in two ways
a)Implicit locking
b)Explicit Locking
The session remains in a waiting state until one of the sessions is either committed or rolled back.
Sql Server implicitly creates a deadlock situation if a transaction is done on the same table in different sessions. This default locking mechanism is called implicit or automatic locking.
A lock is held until the transaction is complete; this is referred to as data concurrency.
A key reason for locking is to ensure that all valid processes are always able to access the original data as it was at the time the query was initiated. This is referred to as read consistency.
In addition to the type of locking (Implicit or Explicit), Sql Server also provides two different levels of locking
1.Row Level Locking
2.Table Level Locking.
With Table Level Locking, the entire table is locked against any kind of update or insert actions from another process. Once a given process has locked a table, that process is the only one that can change rows in the table.
With Row Level Locking any specified row or rows in a table can be locked (any unlocked rows are still available for updates or deletes). The locked rows can be updated only by the process that initiated the locking.
