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 |
|
lm007
Starting Member
5 Posts |
 Posted - 2008-07-04 : 14:53:57
|
| I am running some very simple queries to learn about locking. When I use dm_tran_locks the lock information is what I expect. However, when I use the "Activity Monitor"/"Locks By Object" the information displayed is not what I expect and it differs from what is shown by dm_tran_locks. For example:Step 1. Run: use AdventureWorks BEGIN TRANSACTION SELECT * FROM Person.Address WITH (HOLDLOCK) WHERE AddressId > 2 AND AddressId < 5 Step 2. Run: SELECT resource_type, request_mode, resource_description, * FROM sys.dm_tran_locks WHERE resource_type <> 'DATABASE'This will show resource_type request_mode resource_description------------------------------- --------------------OBJECT IS KEY RangeS-S (0400b4b7d951) PAGE IS 1:9304 KEY RangeS-S (0500d1d065e9) KEY RangeS-S (03000d8f0ecc) (5 row(s) affected)Step 3. View Activity Monitor / Locks By Object Here I don’t see any Range lock or any KEY lock. Why the differences and why it appears that "Activity Monitor"/"Locks By Object" shows incorrect data? |
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-07-04 : 19:38:19
|
| Those info are snapshot of specific time, did you run query and look at activity monitor at same time? |
 |
|
|
lm007
Starting Member
5 Posts |
Posted - 2008-07-05 : 02:24:17
|
| The scenario was created so that the results are not snapshoot specific time. The SELECT query at step 1 is preceded by BEGIN TRANSACTION and does not include a COMMIT/ROLLBACK. Also it uses the HOLDLOCK. All these will make the locks stable. Running the query at step 2 (the one that selects from dm_tran_locks) or refreshing the ActivityMonitor show that indeed the locks remain the same. Just that ActivityMonitorand and dm_tran_locks show different results. |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-07-05 : 22:44:38
|
| Did you look at locks by processes? |
|
|
|
lm007
Starting Member
5 Posts |
Posted - 2008-07-06 : 14:33:52
|
| Thank you. You are right; “Locks By Process” shows the locks I expected to see. I realize now that "Locks By Object" also shows the locks just that I have to choose the appropriate object to look at – in this case AdventureWorks.Address. On the top of the page there is a dropdown from where I can select the object I want to see the locks on and I did not do that - the default object is “(internal)”. |
|
|
|
|
|
|
Activity Monitor vs. dm_tran_locks