blocking issue
-
Hi All,
Today, I came across a blocking issue from the application. A very simple INSERT ran and the spid is in "sleeping" "AWAITING_COMMAND" with an open transaction and lock on the table. After 15 hours, then comes a spid which "truncate that table" and it is waiting on Sch-M lock. There is no implicit_Tran on. but don't know why it has one open transaction. I seeing this behavior quite often and eventually had to kill that insert spid. isolation is Read- committed and RCSI is on for that particular db.
<additional_info>
<text_size>-1</text_size>
<language>us_english</language>
<date_format>mdy</date_format>
<date_first>7</date_first>
<quoted_identifier>ON</quoted_identifier>
<arithabort>OFF</arithabort>
<ansi_null_dflt_on>ON</ansi_null_dflt_on>
<ansi_defaults>OFF</ansi_defaults>
<ansi_warnings>ON</ansi_warnings>
<ansi_padding>ON</ansi_padding>
<ansi_nulls>ON</ansi_nulls>
<concat_null_yields_null>ON</concat_null_yields_null>
<transaction_isolation_level>ReadCommitted</transaction_isolation_level>
<lock_timeout>-1</lock_timeout>
<deadlock_priority>0</deadlock_priority>
<row_count>1</row_count>
<command_type>AWAITING COMMAND</command_type>
<sql_handle>0x01000b002c203603d0be7be26602000000000000</sql_handle>
<statement_start_offset>-1</statement_start_offset>
<statement_end_offset>-1</statement_end_offset>
<host_process_id>25578</host_process_id>
<group_id>2</group_id>
<original_login_name>app_ors</original_login_name>
</additional_info>
My questions, by default any connection to sql server will be auto-commit mode , (correct me if I am wrong). I have tested this with a small insert on dept table and as and when the insert is done, its auto-commited by default and there is no open_tran. however, it is "sleeping" and "awaiting_command" as expected. I opened a new ssms window and tried to SELECT * FROM DEPT; I was able to see the new record which means the insert has been auto committed.
In case, of the application WHY it is keeping the transaction open in that spid for a simple insert.
Am I missing anything? what else can we collect or check to know the root cause of that open txn? is there a possibility of another spid has open that txn( i dont think so) !! . please give some ideas.
Regards,
Sam
-
Is there an explicit BEGIN TRANSACTION in the code with no COMMIT?
-
is there way or using DMV we can tell an explicit transaction has been started by the session? we see so much blocking because of such sessions?
I am trying to use sp_whoisactive ,@get_outer_command =1 and selecting the columns [sql_command][sql_text] to get complete batch and currently executing query. In that, I dont find a BEGIN TRAN statement.
-
After doing some research, got this query. For explicit transact, it will show "user_transaction" as txn_name,
if implicit_transaction is ON , then it is showing up as "implicit_transaction" as txn_name.
select
s.spid,
s.open_tran,
s.login_time,
s.last_batch,
at.transaction_begin_time,
t.transaction_id as txnid,
t.is_user_transaction,
at.name as txn_name, ---implicit transaction or "user_transaction"
at.transaction_type,
CASE at.transaction_type
WHEN 1 THEN 'Read/Write'
WHEN 2 THEN 'Read-Only'
WHEN 3 THEN 'System'
WHEN 4 THEN 'Distributed'
ELSE 'WHAT HAVE YOU DONE'
END AS transaction_type1,
at.transaction_state,
CASE at.transaction_state
WHEN 0 THEN 'Uninitialized'
WHEN 1 THEN 'Not Yet Started'
WHEN 2 THEN 'Active'
WHEN 3 THEN 'Ended (Read-Only)'
WHEN 4 THEN 'Committing'
WHEN 5 THEN 'Prepared'
WHEN 6 THEN 'Committed'
WHEN 7 THEN 'Rolling Back'
WHEN 8 THEN 'Rolled Back'
ELSE 'WHAT HAVE YOU DONE'
END AS transaction_state2,
s.[status], --sleeping
s.cmd, --"AWAITING COMMAND"
CASE ss.transaction_isolation_level
WHEN 0 THEN 'Unspecified'
WHEN 1 THEN 'ReadUncommitted'
WHEN 2 THEN 'ReadCommitted'
WHEN 3 THEN 'Repeatable'
WHEN 4 THEN 'Serializable'
WHEN 5 THEN 'Snapshot' END AS transaction_isolation_level,
st.[text] as qry_txt
from sys.sysprocesses s
left outer join sys.dm_exec_sessions ss on s.spid = ss.session_id
left outer join sys.dm_tran_session_transactions t on s.spid =t.session_id
left outer join sys.dm_tran_active_transactions at on t.transaction_id = at.transaction_id
CROSS APPLY sys.dm_exec_sql_text(s.sql_handle) AS st
where s.spid =54 -
One question application team/dev team had is, is there an option available to automatically commit txn's which are opened by the application when using Explicit txn? is there any option like that? I didn't know how to answer that, don't know if that is possible and if they are sure of what they are talking about. Want to know your inputs on this.. any analogy can be used to make them understand that , if they open the txn, they have to close it explicitly??
At that time, all i could think of and suggested if they want that kind of behavior , they need use IMPLICIT TRANSACTION OFF.
Please correct me if I am wrong.
-
I am not sure you would want to automatically commit open explicit transactions. If you lose a connection on update 2 of 3 and you commit then you are left with inconsistent data.
A quick way to look for the open user transactions is DBCC openTran
----------------------------------------------------
-
If you declare an explicit tran then it is your responsibility to either commit or rollback
Cleaning up uncommitted explicit trans by auto committing them is not something you want
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply