open transaction on none Editable table

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » open transaction on none Editable table

open transaction on none Editable table

Rate Topic

Display Mode

Topic Options

Author

Message

ashkan siroos

Posted Sunday, September 30, 2012 8:34 AM

SSC Journeyman

Group: General Forum Members
Last Login: Sunday, May 19, 2013 5:15 AM
Points: 93, Visits: 230

Dear Experts,
We have some tables that we call them LKs.( look up tables)
It is obvious that we have no insert, update or delete on these tables.
Yesterday we've started a job which is doing some works on data but it randomly thrown timeout.
I've checked the db and I see that a transaction is running on the LK table!!!!

To be honest, until now I never thought about an open transaction on selects. why is this happened? What is it going on here?! Sad

Please Help me!

Thanks and regards,
Ashkan

Post #1366256

GilaMonster

Posted Sunday, September 30, 2012 8:45 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 3:18 PM
Points: 37,744, Visits: 30,025

ashkan siroos (9/30/2012)

We have some tables that we call them LKs.( look up tables)
It is obvious that we have no insert, update or delete on these tables.

Why is it obvious? Are those tables on a read only filegroup? If not, what's preventing changes?

I've checked the db and I see that a transaction is running on the LK table!!!!

How did you identify that?

To be honest, until now I never thought about an open transaction on selects. why is this happened? What is it going on here?! Sad

No idea. Insufficient information. How did you determine there's an open transaction with locks held on that table? What type of locks? Did you identify what session holds those locks, where it is being run from and what command it's running?

Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1366258

ashkan siroos

Posted Sunday, September 30, 2012 9:05 AM

SSC Journeyman

Group: General Forum Members
Last Login: Sunday, May 19, 2013 5:15 AM
Points: 93, Visits: 230

Dear GilaMonster,

Why is it obvious? Are those tables on a read only filegroup? If not, what's preventing changes?

I'm sorry , we only insert to our tables throw code, and we didn't write any code to insert into these tables, and nobody is allowed to execute any query on DB except me and my manager.

How did you identify that?

Post #1366261

GilaMonster

Posted Sunday, September 30, 2012 9:54 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 3:18 PM
Points: 37,744, Visits: 30,025

ashkan siroos (9/30/2012)

How did you identify that?

I ran the "dbcc opentran" command.
it returned an open transaction that didn't killed after 5 minutes, and it has the select statement on it

and please tell me how can I find type of lock?[/quote]

DBCC OpenTran doesn't give you any info on what tables or what statements are involved in a transaction, so how did you identify that the transaction affected your lookup tables and that it was a select?

For info on identifying causes of blocking, chapter 6 of http://www.simple-talk.com/books/sql-books/troubleshooting-sql-server-a-guide-for-the-accidental-dba/

Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1366266

ashkan siroos

Posted Sunday, September 30, 2012 10:14 AM

SSC Journeyman

Group: General Forum Members
Last Login: Sunday, May 19, 2013 5:15 AM
Points: 93, Visits: 230

I use dbcc opentran to define what is the id of the open transaction and then I used this query to see the information of that transaction

SELECT *
FROM sys.dm_tran_session_transactions tst INNER JOIN sys.dm_exec_connections ec ON tst.session_id = ec.session_id
CROSS APPLY sys.dm_exec_sql_text(ec.most_recent_sql_handle)

Thanks and regards,
Ashkan

Post #1366270

GilaMonster

Posted Sunday, September 30, 2012 4:10 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 3:18 PM
Points: 37,744, Visits: 30,025

All that gave you was the last command that the session in question ran, not all of them. If there were multiple commands run in the transaction, that query would have returned just the very last one, which may not have been responsible for the locks held.

The query would also have given you the host name and application, so you can start your investigation there, find out what is starting but not committing a transaction and what it was doing within that tran.

Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1366299

« Prev Topic | Next Topic »

Permissions