May 27, 2015 at 2:44 pm
is there any difference..
begin tran
select *From table
begin tran
update table set id=some_value
if we run both and exec DBCC opentran I can only see the second statement..why does it not considering select statement...Please advice on this
May 27, 2015 at 2:47 pm
select statements are not technically a transaction, since it is not modifying any data. They can still cause locking/blocks though. sp_who2 will show you all running processes, including selects.
May 27, 2015 at 3:05 pm
To clarify a bit, let's visit the documentation (always great fun! :-))
From https://msdn.microsoft.com/en-us/library/ms182792.aspx, on DBCC OPENTRAN:
Results are displayed only if there is an active transaction that exists in the log or if the database contains replication information. An informational message is displayed if there are no active transactions in the log.
Now from https://msdn.microsoft.com/en-us/library/ms188929.aspx:
Although BEGIN TRANSACTION starts a local transaction, it is not recorded in the transaction log until the application subsequently performs an action that must be recorded in the log, such as executing an INSERT, UPDATE, or DELETE statement.
The trick, then, is that since OPENTRAN will only show results if an active transaction exists in the log, and a transaction is not recorded as such in the transaction log until it does something else that has to be logged, a transaction that has run only a SELECT will not show up in OPENTRAN.
You could see it via the transaction related DMVs, though. So, if in one query window you run:
SELECT * INTO #tmp FROM sys.objects
BEGIN TRANSACTION
SELECT * FROM #tmp
And in another window run:
SELECT st.session_id , dt.*
FROM sys.dm_tran_database_transactions dt
INNER JOIN sys.dm_tran_session_transactions st
ON dt.transaction_id=st.transaction_id
You would then see a row for the transaction started by the session of the first query.
Cheers!
May 27, 2015 at 3:09 pm
Great reply, Jacob. I need to start referencing the documentation in my answers.
May 27, 2015 at 3:31 pm
Thanks lot....
and sys.sysprocess also takes the info from log ?
May 27, 2015 at 3:55 pm
Adam Angelini (5/27/2015)
Great reply, Jacob. I need to start referencing the documentation in my answers.
Thanks much!
dastagiri16 (5/27/2015)
Thanks lot....and sys.sysprocess also takes the info from log ?
The deprecated sys.sysprocesses, along with sys.dm_exec_requests and sys.dm_exec_sessions will show the session running only a SELECT within an explicit transaction as having an open transaction.
You can check the open_tran column of sysprocesses or the open_transaction_count column of the other two DMVs. In the example scenario I posted above, you would only be able to see the first query in sysprocesses and sys.dm_exec_sessions, since the first query wouldn't be currently executing, and thus would not show up in sys.dm_exec_requests.
Cheers!
May 28, 2015 at 8:34 am
hi,
can we know the duration since the transaction started ?
begin tran
select *From test
May 28, 2015 at 9:20 am
You definitely can. You have to be careful, because not all of the transaction-related DMVs will record a start time for the SELECT-only transaction.
If you run the SELECT-only transaction in one query window, and run the following query, you'll see the start time and the duration in seconds. You can tweak the DATEDIFF to get duration as you see fit:
SELECT st.session_id ,
at.transaction_begin_time,
TransactionDurationSeconds=DATEDIFF(second,at.transaction_begin_time,GETDATE())
FROM sys.dm_tran_session_transactions st
INNER JOIN sys.dm_tran_active_transactions at
ON at.transaction_id=st.transaction_id
Cheers!
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply