transaction without a request
in [Server Configuration]
|
From: Dan Holmes on 18 Mar 2010 11:43 This doesn't return anything SELECT * FROM sys.dm_exec_requests WHERE Session_ID = 187 --this does SELECT * FROM sys.dm_tran_session_transactions WHERE Session_ID = 187 --as does this SELECT * FROM sys.dm_exec_sessions WHERE Session_ID = 187 how can a session be in a transaction without having an active request?
From: Dan Holmes on 18 Mar 2010 11:51 On 3/18/2010 11:43 AM, Dan Holmes wrote: > This doesn't return anything > SELECT * > FROM sys.dm_exec_requests > WHERE Session_ID = 187 > > --this does > SELECT * > FROM sys.dm_tran_session_transactions > WHERE Session_ID = 187 > > --as does this > SELECT * > FROM sys.dm_exec_sessions > WHERE Session_ID = 187 > > > how can a session be in a transaction without having an active request? how can i get from this data to know which table that page belongs to? Select * FROM sys.dm_tran_locks WHERE request_Session_ID = 187 PAGE 42 1:8368 72057596656353280 0 IX PAGE 42 1:8369 72057596656353280 0 IX
From: Kalen Delaney on 18 Mar 2010 12:19 Hi Dan It's very easy to be in a transaction without an active request; many blocking problems result from just this behavior. You can start a transaction and do an update in a batch by itself like this: BEGIN TRAN UPDATE sometable ..... GO Now that session has an open transaction, it is holding locks, but it doesn't have any active request that it is running. As for the page you are asking about, I can't tell for sure what your numbers refer to since you didn't list any column headings, you didn't tell us what query gave you those numbers, and you didn't tell us what version you are running. But I might guess that 72057596656353280 is in the column called resource_associated_entity_id, and then it is a partition_id. So you can use sys.partitions (make sure you are in the right database) to find the row for that partition_id, and the look at the object_id associated with it. The object_name() function will then translate. I don't want to go into more detail since I'm not sure what your numbers refer to, but if you'll actually show us where you're getting those numbers, we can be more help. -- HTH Kalen ---------------------------------------- Kalen Delaney SQL Server MVP www.SQLServerInternals.com "Dan Holmes" <dan.holmes(a)routematch.com> wrote in message news:#d$ryLrxKHA.5132(a)TK2MSFTNGP05.phx.gbl... > On 3/18/2010 11:43 AM, Dan Holmes wrote: >> This doesn't return anything >> SELECT * >> FROM sys.dm_exec_requests >> WHERE Session_ID = 187 >> >> --this does >> SELECT * >> FROM sys.dm_tran_session_transactions >> WHERE Session_ID = 187 >> >> --as does this >> SELECT * >> FROM sys.dm_exec_sessions >> WHERE Session_ID = 187 >> >> >> how can a session be in a transaction without having an active request? > how can i get from this data to know which table that page belongs to? > > > Select * > FROM sys.dm_tran_locks > WHERE request_Session_ID = 187 > > PAGE 42 1:8368 > 72057596656353280 0 IX > PAGE 42 1:8369 > 72057596656353280 0 IX
From: Dan Holmes on 18 Mar 2010 12:33 On 3/18/2010 12:19 PM, Kalen Delaney wrote: > Hi Dan > > It's very easy to be in a transaction without an active request; many > blocking problems result from just this behavior. > > You can start a transaction and do an update in a batch by itself like > this: > > BEGIN TRAN > UPDATE sometable ..... > GO > > Now that session has an open transaction, it is holding locks, but it > doesn't have any active request that it is running. > > > As for the page you are asking about, I can't tell for sure what your > numbers refer to since you didn't list any column headings, you didn't > tell us what query gave you those numbers, and you didn't tell us what > version you are running. > > But I might guess that 72057596656353280 is in the column called > resource_associated_entity_id, and then it is a partition_id. So you can > use sys.partitions (make sure you are in the right database) to find the > row for that partition_id, and the look at the object_id associated with > it. The object_name() function will then translate. > > I don't want to go into more detail since I'm not sure what your numbers > refer to, but if you'll actually show us where you're getting those > numbers, we can be more help. > thank you that is exactly what has happened. I have a situation where a transaction was open but hasn't been commited. I need to get that session committed. Is there a way to hi-jack the session and inject a commit? probably not. The next idea would be to change a proc so that it has an extra commit. I don't know if that will work on an open transaction. Do objects get versioned when a transaction starts on a session? thanks resource_type resource_subtype resource_database_id resource_description resource_associated_entity_id resource_lock_partition request_mode request_type request_status request_reference_count request_lifetime DATABASE 42 0 0 S LOCK GRANT 1 0 KEY 42 (62006b7d13f1) 72057596641476608 0 X LOCK GRANT 0 33554432 KEY 42 (d20121e630bf) 72057596641476608 0 X LOCK GRANT 0 33554432 KEY 42 (090083046091) 72057596641738752 0 X LOCK GRANT 0 33554432 KEY 42 (95007b41c57a) 72057596656877568 0 X LOCK GRANT 0 33554432 KEY 42 (0201e06bc62b) 72057596656877568 0 X LOCK GRANT 0 33554432 KEY 42 (8b00972079f3) 72057596656353280 0 X LOCK GRANT 0 33554432 KEY 42 (2501971ded19) 72057596656877568 0 X LOCK GRANT 0 33554432 KEY 42 (0f004b28fdf8) 72057596641542144 0 X LOCK GRANT 0 33554432 KEY 42 (7200dcb03424) 72057596656353280 0 X LOCK GRANT 0 33554432 KEY 42 (4f007ea60924) 72057596657205248 0 X LOCK GRANT 0 33554432
From: Kalen Delaney on 18 Mar 2010 12:45
You still haven't told me what version you are using, or what query you are running to get the numbers you are showing. Although the columns names are a big clue, it's more polite to not make the people helping you try to guess. It still looks like the big number in resource_associated_entity_id is the partition_id. I think you didn't show all the rows. There should be some for OBJECT locks, and then the resource_associated_entity_id would be the object_id. There is no way to hijack an open transaction, but you can kill it, and that will rollback the transaction and release the locks. -- HTH Kalen ---------------------------------------- Kalen Delaney SQL Server MVP www.SQLServerInternals.com "Dan Holmes" <dan.holmes(a)routematch.com> wrote in message news:4BA255C7.9050809(a)routematch.com... > On 3/18/2010 12:19 PM, Kalen Delaney wrote: >> Hi Dan >> >> It's very easy to be in a transaction without an active request; many >> blocking problems result from just this behavior. >> >> You can start a transaction and do an update in a batch by itself like >> this: >> >> BEGIN TRAN >> UPDATE sometable ..... >> GO >> >> Now that session has an open transaction, it is holding locks, but it >> doesn't have any active request that it is running. >> >> >> As for the page you are asking about, I can't tell for sure what your >> numbers refer to since you didn't list any column headings, you didn't >> tell us what query gave you those numbers, and you didn't tell us what >> version you are running. >> >> But I might guess that 72057596656353280 is in the column called >> resource_associated_entity_id, and then it is a partition_id. So you can >> use sys.partitions (make sure you are in the right database) to find the >> row for that partition_id, and the look at the object_id associated with >> it. The object_name() function will then translate. >> >> I don't want to go into more detail since I'm not sure what your numbers >> refer to, but if you'll actually show us where you're getting those >> numbers, we can be more help. >> > thank you that is exactly what has happened. I have a situation where a > transaction was open but hasn't been commited. I need to get that session > committed. Is there a way to hi-jack the session and inject a commit? > probably not. The next idea would be to change a proc so that it has an > extra commit. I don't know if that will work on an open transaction. Do > objects get versioned when a transaction starts on a session? > > thanks > > resource_type resource_subtype resource_database_id resource_description > resource_associated_entity_id resource_lock_partition request_mode > request_type request_status request_reference_count request_lifetime > DATABASE 42 > 0 0 S LOCK GRANT 1 0 > KEY 42 (62006b7d13f1) > 72057596641476608 0 X LOCK GRANT 0 33554432 > KEY 42 (d20121e630bf) > 72057596641476608 0 X LOCK GRANT 0 33554432 > KEY 42 (090083046091) > 72057596641738752 0 X LOCK GRANT 0 33554432 > KEY 42 (95007b41c57a) > 72057596656877568 0 X LOCK GRANT 0 33554432 > KEY 42 (0201e06bc62b) > 72057596656877568 0 X LOCK GRANT 0 33554432 > KEY 42 (8b00972079f3) > 72057596656353280 0 X LOCK GRANT 0 33554432 > KEY 42 (2501971ded19) > 72057596656877568 0 X LOCK GRANT 0 33554432 > KEY 42 (0f004b28fdf8) > 72057596641542144 0 X LOCK GRANT 0 33554432 > KEY 42 (7200dcb03424) > 72057596656353280 0 X LOCK GRANT 0 33554432 > KEY 42 (4f007ea60924) > 72057596657205248 0 X LOCK GRANT 0 33554432 |