Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
sunsanvin
Master Smack Fu Yak Hacker
1274 Posts |
 Posted - 2008-12-05 : 02:11:50
|
| Dear All,i've read with the different types of locks from MSDN. those are pasted below. after running sp_lock, some of these types are displaying with different objectID. as per my knowledge, if different spID's working on same objectID, that will become dead lock...i'm not sure. please give me the idea how can i find the dead lock?The lock type:RID = Lock on a single row in a table identified by a row identifier (RID).KEY = Lock within an index that protects a range of keys in serializable transactions.PAG = Lock on a data or index page.EXT = Lock on an extent.TAB = Lock on an entire table, including all data and indexes.DB = Lock on a database.FIL = Lock on a database file.APP = Lock on an application-specified resource.MD = Locks on metadata, or catalog information.HBT = Lock on a heap or B-Tree index. This information is incomplete in SQL Server.AU = Lock on an allocation unit. This information is incomplete in SQL Server.ArnavEven you learn 1%, Learn it with 100% confidence. |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2008-12-05 : 03:46:11
|
quote:
Originally posted by sunsanvin
as per my knowledge, if different spID's working on same objectID, that will become dead lock...
Not necessarily. Two processes working on the same object causes blocking if their locks are incompatible. It won't be a deadlock though.see - [url]http://sqlinthewild.co.za/index.php/2008/07/08/what-is-a-deadlock/[/url]quote:
i'm not sure. please give me the idea how can i find the dead lock?
You won't see one in the lock views. SQL has a deadlock detector. As soon as it notices one, one of the processes involved is automatically killed and rolled back. The killed process gets an error message saying it was the victim of a deadlock.If you think you're getting deadlocks, enable traceflag 1222 (best by adding it to the SQL startup parameters) and the deadlock graph will be written to the SQL error log.--Gail ShawSQL Server MVP |
 |
|
|
sunsanvin
Master Smack Fu Yak Hacker
1274 Posts |
Posted - 2008-12-08 : 00:33:32
|
| Thanks for the link Gilamaster....is there any procedure to find the deadlocks and kill? or how can we find deadlock using deadlock detector?ArnavEven you learn 1%, Learn it with 100% confidence. |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2008-12-08 : 02:16:40
|
| You don't find and kill deadlocks. SQL does it automatically. As I said, "As soon as it notices one, one of the processes involved is automatically killed and rolled back. "--Gail ShawSQL Server MVP |
|
|
|
sunsanvin
Master Smack Fu Yak Hacker
1274 Posts |
Posted - 2008-12-08 : 04:15:35
|
| Thanks Gila masterArnavEven you learn 1%, Learn it with 100% confidence. |
|
|
|
|
|
|
|
|
how to know dead lock