SQL Server Lock Manager :
Avoids the lock conflict
The Lock Manager is responsible for deciding the
appropriate lock type
(six bus shared, intent(sex),exclusive,bulkupdate,update,schema
update, )
& appropriate granularity of locks (row, page, table, and so on)
according to the type of operation being performed and the amount of data
being affected.
For example, a CREATE INDEX statement might lock the entire table,
whereas an UPDATE statement might lock only a specific row.
For shared data, the Lock Manager manages row locks, page locks, and table
locks on
tables, as well as data pages, text pages, and leaf level index pages.
Internally, the Lock Manager uses latches to manage locking on index rows
and pages,
controlling access to internal data structures, and in some cases, for
retrieving individual rows of data.
Latches provide better system performance because THEY ARE LESS RESOURCE
INTENSIVE THAN LOCKS.
Latches also provide greater concurrency than locks.
Latches are typically used for operations
such as page splits, deletion of index rows, movement of rows in an index,
and so
on.
The main difference between a lock and a latch is that a lock is held for
the duration
of the transaction, and a latch is held only for the duration of the
operation for which it is
required. Locks
are used to ENSURE THE LOGICAL CONSISTENSY OF THE DATA, WHEREAS LATCHES ARE
USED TO ENSURE THE PHYSYICAL CONSISTENCY of the data and the data
structures.
----------------------------------------------------------------------------
----------------
you can monitor the locks through :
sp_lock @pr1,@pr2
@pr1 is the process id and @pr2 is the another process id which applies at
the same time.
Pr - do it s m (mnemonic! programme do it in sulla modda.)
--- which process applied [PR]
where [DO IT] and what type of lock it has occured.
SPID |
DBID | OBJID | INDID | TYPE | RESOURCE | MODE |
STATUS.
0-data tab s/is/
grant
1 - cl
wait
>1 - ncl
convert
USING PROFILER : LOCK DEAD LOCK AND LOCK ESCALATION.
PERFORMANCE MONITOR :
LOCK REQUESTS/SEC -- a more no indicates that system holding
more locks
but it not neccasorily is a cause of
alarm,since the system
having more concurrent users.
LOCK TIMEOUT/SEC : IF ITS HIGH (BLOCKING LOCKS)
SET THE MAX LOCKTIME OUT TO HIGH.
LOCK WAIT TIME : HIGH VALUE MEANS LONG RUNNING OR DEAD
LOCKS ARE THERE.
DEAD LOCKS/SEC : MORE NO.OF DEAD LOCKS.
--- LOCK TYPES
SHARED -- shared locks are applied to 1st page , if its moved to
next page
then it will release the 1st page and goes to the next
page
if any one who is going to update a row where a share lock
is there then
he has to wait, So sql server provides a hint called (HOLD
LOCK)
Other Users Can also share this
UPDATE LOCK--
is between the exclusive and shared lock
means if your trying to update a row.. then first it has to
search for a row and
then it has to update.. so till its updates that it will put
that update lock
It not only means for update for inserts also !
how because for example if you are going to insert a new row
and
on an index page.. then first it has to search where exactly
it has to insert.
then it escalates the update lock to exclusive lock.
Other Users Can also share this
Exclusive: while updating.. and no one can even read this.
Intent :
Actually its not an locking mode.it tells to the lock
manager that higer
level of granularity the type of lock held at lower level.
for example there is an intentshared lock is on a row or a
page specifies that
it will prevents other process to acquire a lock on that
table.
IS
IX
SIX-- means that IS is converted to IX
----------------------------------------------------------------------------
-------------------
how actually it manages.
sql server keeps all the lock information in memory in its internal
lock structure.
The syslock info is actually in the master database but the info is
stored in the memory.
when you query the syslockinfo then it will populates the data from
the memory.
------------------------
How can you identify whether its Dead locked or not!
use sp_who and sp_lock
----------------------------------------SP_WHO------------------------------
----------
sp_who
pr_id blk_id db_id cmd
look into the blk_id you can come to know which pr_id is blocking this
process.
then sp_lock that pr_id value and the blk_id value.
select @@lock_timeout
set lock_timeout -1 --- infinity
0 --- immediate
--------
how sql server handles the dead locks.
the lock_monitor thread checks whether any waiting locks are there
in system
for every 5 seconds.
When it detects a dead lock then it will aborts the transaction for
one of
involved process.
sql server returns an error 1205 means a dead lock was happened.
* if you observe the sp_who and sp_lock its not enough since its
already releases that.
use these commands
DBCC TRACEON (3604)
DBCC TRACEON (1204)
THEN IT WILL WRITE THE INFO TO THE SQL SERVER ERROR LOG
WHENEVER A DEAD LOCK HAPPENDS.You can come to know how the
lock_monitor behaves,.
which process is waiting and what causes the dead lock and
including the t-sql statements.
DBCC TRACEON (1204) --- wont write in error log.. it will directly
gives the output.
for example fire the below block
[
use maintanance
go
DBCC TRACEON (1200)
go
begin tran
delete from test where iid = 1
go
--------------
OPTMISING LOCKING(using timestamp)
so in an oltp zone.. where no.of transactions and no.of select statements
will happens.
so how can made an effecient application with having deadlock
this is called the OPTMISTIC LOCKING
create a pk and a timestamp col
---- client reads a row
declare @ts_val
set @ts_val = tstmp_col
where where customerid = 'alfki'
---- client update a row with the time stamp values.
update customers
set companyname = 'agilisys'
where customerid = 'alfki'
and ts_col = t_sequel(timestamp,@ts_val)
t_sequel is a function which will give false if both the values
are not same(means some one modified the below data.)
]
-----Original Message-----
From: Edwin Uy
[mailto:mssqldba-ezmlmshield-x39128483.[Email address protected]
Sent: Tuesday, June 29, 2004 5:54 AM
To: LazyDBA Discussion
Subject: Simulating DEADLOCKS for Performance Tuning/Troubleshooting
Hi all,
I know, I've asked this before but unfortunately, my mail box got
cleanup/archive by accident. Am trying to check on performance
tuning/troubleshooting, can anyone tell me how to simulate a deadlock
which I will of course kill the other session that is causing it.
Cheers,
EDWIN ONG UY
\\|//
(o o)
^"^"^"^"^"^oOOo"^(_)^"oOOo^"^"^"^"^"^"^"^"^"^"^
"Choose a job you love, and you will never have to work a day in your
life." --Confucius
"How do I work? I grope." -- Albert Einstein
"Experience is the best teacher, enrolling in the class is the biggest
problem." -- Anonymous
---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
Website : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
For additional commands, e-mail: mssqldba-[Email address protected]
MS Sql Server LazyDBA home page