SQL Server Locking

Locking
Shared (S) Used for read operations that do not change or update data, such as a SELECT statement.
Update (U) Used on resources that can be updated. Prevents a common form of deadlock that occurs when multiple sessions are reading, locking, and potentially updating resources later.
Exclusive (X) Used for data-modification operations. Ensures that multiple updates cannot be made to the same resource at the same time.
Intent Used to establish a lock hierarchy. The types of intent locks are:
intent shared (IS),
intent exclusive (IX),
shared with intent exclusive (SIX).
Key-range Protects the range of rows read by a query when using the serializable transaction isolation level. Ensures that other transactions cannot insert rows that would qualify for the queries of the serializable transaction if the queries were run again.

We can see Dead locks using query
(Select * from master.dbo.sysprocesses where Blocked = 1)

You can create Deadlock example as

1. Create a table and insert 2 records in it on 1st query window
Create Table ##Table (a int,b varchar(10))

begin transaction
insert into ##Table values (1,'aslam')
insert into ##Table values (2,'akram')
commit transaction
2. Query in a seperate window to get result
select * from ##Table

this will give you 2 rows
3. Now Insert another record from 1st window like this.
begin transaction
insert into ##Table values (3,'aslam')
4. Again run query from step 2
select * from ##Table

this will not give you any row and query continues execution -- deadlock situation
5. Now run this command
rollback transaction
6. Go to query window and see you got 2 records.


There is a way to get result by avoiding deadlock situation even transaction is open
1. Execute first 3 steps from above
2. Now run this query
select * from ##Table (NOLCOK)

this will show you all 3 records.
3. now execute commamd# 5
and agains select all records you will get 2 records.

It means you can get all records with NOLOCK.