is there a way get a summary of locks held by a SQL statement?

[Former Member]

Hi community,

I'd like to know if there are ways to get a list or a summary of locks held by a SQL statement?

I was thinking of executing sp_sysmon during my unitary tests but the output might be 'polluted' by other SQL queries running at the same time.

I was thinking of using monLocks but I'm worried I could mess the data returned by monLock (for example if I search for a specific SPID, how do I know I'm viewing the correct SPID because I think SPID values can be reused - this actually opens a new question how can I uniquely identify a SQL statement execution if SPID is not enough)

Last, using sp_object_stats but this means I'll need to run this test on an environment where we could reproduce concurrency access which might not be easy except on production.

This question is raised because I have a client that is concerned about locking that could happen if a table is access by a SPID through an UPDATE/INSERT statement an other SPID in SELECT statement

Thanks for your comments

Simon

[former_member188958]

Perhaps switch 1212 or 1217 would do the trick? 1212 traces acquisition of every lock granted and released on user and system tables, 1217 does the same but only for user tables.

Use in conjunction with either switch 3604 (send output to the client) or 3605 (send output to the errorlog) depending on how you want to harvest the output.

The output will look like this:

1> set switch on 3604
2> go
Switch 3604 ('print_output_to_client') is turned on.
All supplied switches are successfully turned on.
1> set switch on 1212 with override
2> go
lock_release shared address db=0 obj=0 ptn=500, pg=1411496608 row=0
splk=0x44ab1a00 ctx=0x0 suff=0 fileindex=0 (Unknown) lineno=0
lock_release shared address db=0 obj=0 ptn=500, pg=1411392944 row=0
splk=0x44ab1a00 ctx=0x0 suff=0 fileindex=0 (Unknown) lineno=0
lock_do_logical shared page db=1 obj=36 ptnid=0 pg=3110 row=0 ctx=0x8 suff=0
pspid=30 fileindex=763 (srchindex.c@@/main/asecorona/4 2015-02-24) lineno=1933
LOCK_GRANTED
lock_release shared page db=1 obj=36 ptn=0, pg=3110 row=0 splk=0x44ab1b00
ctx=0x8 suff=0 fileindex=763 (srchindex.c@@/main/asecorona/4 2015-02-24)
lineno=1933
lock_do_logical shared intent db=1 obj=36 ptnid=0 pg=0 row=0 ctx=0x0 suff=0
pspid=30 fileindex=217 (ex_print.c@@/main/asecorona/ase160sp02plx/1 2016-01-10)
lineno=4243 LOCK_GRANTED
lock_release shared address db=0 obj=0 ptn=497, pg=1411496608 row=0
splk=0x44ab1a00 ctx=0x0 suff=0 fileindex=36 (astcrec.c@@/main/15 2014-03-26)
lineno=0
lock_release shared address db=0 obj=0 ptn=497, pg=1411392944 row=0
splk=0x44ab1a00 ctx=0x0 suff=0 fileindex=0 (Unknown) lineno=0
lock_do_logical shared page db=1 obj=36 ptnid=0 pg=3110 row=0 ctx=0x8 suff=0
pspid=30 fileindex=763 (srchindex.c@@/main/asecorona/4 2015-02-24) lineno=1933
LOCK_GRANTED
lock_release shared page db=1 obj=36 ptn=0, pg=3110 row=0 splk=0x44ab1b00
ctx=0x8 suff=0 fileindex=763 (srchindex.c@@/main/asecorona/4 2015-02-24)
lineno=1933
lock_release shared intent db=1 obj=0 ptn=0, pg=36 row=0 splk=0x44ab1a80 ctx=0x0
suff=0 fileindex=217 (ex_print.c@@/main/asecorona/ase160sp02plx/1 2016-01-10)
lineno=4243
Switch 1212 ('print_lock_acquire_and_release') is turned on.
lock_release shared address db=0 obj=0 ptn=500, pg=1411496608 row=0
splk=0x44ab1a00 ctx=0x0 suff=0 fileindex=0 (Unknown) lineno=0
lock_release shared address db=0 obj=0 ptn=500, pg=1411392944 row=0
splk=0x44ab1a00 ctx=0x0 suff=0 fileindex=0 (Unknown) lineno=0
lock_do_logical shared page db=1 obj=36 ptnid=0 pg=3110 row=0 ctx=0x8 suff=0
pspid=30 fileindex=763 (srchindex.c@@/main/asecorona/4 2015-02-24) lineno=1933
LOCK_GRANTED
lock_release shared page db=1 obj=36 ptn=0, pg=3110 row=0 splk=0x44ab1b00
ctx=0x8 suff=0 fileindex=763 (srchindex.c@@/main/asecorona/4 2015-02-24)
lineno=1933
lock_do_logical shared intent db=1 obj=36 ptnid=0 pg=0 row=0 ctx=0x0 suff=0
pspid=30 fileindex=217 (ex_print.c@@/main/asecorona/ase160sp02plx/1 2016-01-10)
lineno=4243 LOCK_GRANTED
lock_release shared address db=0 obj=0 ptn=497, pg=1411496608 row=0
splk=0x44ab1a00 ctx=0x0 suff=0 fileindex=36 (astcrec.c@@/main/15 2014-03-26)
lineno=0
lock_release shared address db=0 obj=0 ptn=497, pg=1411392944 row=0
splk=0x44ab1a00 ctx=0x0 suff=0 fileindex=0 (Unknown) lineno=0
lock_do_logical shared page db=1 obj=36 ptnid=0 pg=3110 row=0 ctx=0x8 suff=0
pspid=30 fileindex=763 (srchindex.c@@/main/asecorona/4 2015-02-24) lineno=1933
LOCK_GRANTED
lock_release shared page db=1 obj=36 ptn=0, pg=3110 row=0 splk=0x44ab1b00
ctx=0x8 suff=0 fileindex=763 (srchindex.c@@/main/asecorona/4 2015-02-24)
lineno=1933
lock_release shared intent db=1 obj=0 ptn=0, pg=36 row=0 splk=0x44ab1a80 ctx=0x0
suff=0 fileindex=217 (ex_print.c@@/main/asecorona/ase160sp02plx/1 2016-01-10)
lineno=4243
All supplied switches are successfully turned on.
1> select * from sysusers
2> go
lock_do_logical shared intent db=4 obj=3 ptnid=0 pg=0 row=0 ctx=0x0 suff=0
pspid=30 fileindex=113 (colnames.c@@/main/asecorona/ase160sp02plx/6 2016-04-10)
lineno=6380 LOCK_GRANTED
lock_do_logical shared intent db=4 obj=3 ptnid=0, pg=0 suff=0 pspid=30
fileindex=9 (all_expand.c) lineno=214 LOCK_CACHED_NOTNEEDED
lock_do_logical shared row db=4 obj=3 ptnid=0 pg=45 row=35 ctx=0x0 suff=0
pspid=30 fileindex=53 (bt_getnext.c@@/main/asecorona/ase160sp02plx/1 2015-11-06)
lineno=4024 LOCK_GRANTED
lock_do_logical shared row db=4 obj=3 ptnid=0 pg=45 row=36 ctx=0x0 suff=0
pspid=30 fileindex=53 (bt_getnext.c@@/main/asecorona/ase160sp02plx/1 2015-11-06)
lineno=4024 LOCK_GRANTED
lock_release shared row db=4 obj=3 ptn=0, pg=45 row=35 splk=0x44ab1b00 ctx=0x0
suff=0 fileindex=53 (bt_getnext.c@@/main/asecorona/ase160sp02plx/1 2015-11-06)
lineno=4024
[...]

-bret

[Former Member]

Thank you Bret,

This is what I was looking for.

Thanks again

Simon

[Former Member]

Thanks Mark,

Now my two-cents question 'is there a way to display the KPID that executed my statement?'

I can retrieve my SPID, with the KPID I could clearly identify my information in MDA tables.

Thanks

Simon

[Mark_A_Parsons]

select KPID from master..monProcess[Lookup|Activity] where SPID = @@spid

[Former Member]

Thank you Bret and Mark,

Your comments answered my question.

Best,

Simon