here we are referring to lock between deterrent users
whether the need lock how long they hold the locks .
locks :
the tool is used by SQl server concunrancey is called locks
and locks clash in form of blocks
there two type of blocks:
1.dead locks
2. live locks.
locks : is tool used by SQL server to put hold on resources.
live blocking : one person prevent another one from access resource until he release the resource.
dead blocks : here one user is blocking resource process 1 and user 2 is blocking resources 2 but user 1 need prosses 2 but he cannot because its blocked by user2 , and user 2 need processes 1 but he cannot because its blocked by user 1 .
here both are blocking each other.
in this case SQL server will choose a victimise and then kill the session to release resource.
what are causes of blocking :
1. manual concunrancey control done poorly
2. poor indexing
3. poor queries.
now i will create transaction without commit.
while i am executing query select . it will be stuck in executing till i commit transaction
as soon i commit the transaction the select query will work
lets’ run the query again to troubleshoot locks
make note for session number you will see on tap of the SSMS
below command will show overview of process with there status whether they are blocking or not
| select * from sys.sysprocesses |
are main column is block column currently all are zeros so no blocking
you can add below to just show blocked prosses
| select * from sys.sysprocesses where blocked > 0 |
here can see SPID 74 is been block by SPID 60
now we found what’s blocking , now we need to see why is blocking by executing the below command
| dbcc inputbuffer (SPID number) |
the below query is inserting values
Le’s put what is source affecting session is trying to do that cause the lock
he is trying to view all the inters so that why because it getting updated
below command it will show what resource is blocking
| select * from sys.dm_tran_locks |
what concern us is type of lock in column request_mode
x stand for exclusive lock
is intend shared locks , since this spid is only doing select query then it will get (IS) lock
now we can use the below query to get idea about what is this object
| select * from sys.objects where object_id = |
kill processes
as DBA you don’t always know whats is going on and why there delete statement is running for two hours that is causing the blocking
kill command is
| kill SPID |
always before you kill get clear information about process that is blocking