Question on explicit transaction

Question

Question on explicit transaction

ucbus 92278

Mr or Mrs. 500

Points: 554
More actions
May 19, 2010 at 3:01 pm

#222714

Hello:
I have two processes.
PROC1: Legacy PC code that uses ODBC connection to SQL Server 2005.
PROC2: .NET code.
Both PROC1 and PROC2 run on different machines.
The database tables are structured Parent, child1, child2, Grandchid 11,Grandchid 12,Grandchid 21,Grandchid 22.
PROC1 issues a explicit Commit using BEGIN TRANSACTION, then goes on to insert records in the tree structure. Once it is done with all grandc hildern, childern, and parent record, it commits the root,if successful or rolls back if it fails.
During the same time PROC2 queries Parent table to SELECTs record thru say Query1. The records selected by Query1 need not include the records being inserted. The query does not contain any hints.
I am finding the query1 is literally getting blocked while PROC1 is processing the records, i.e., between BEGIN TRAN and COMMIT/ROLLBACK for each root note PROC1 is inserting.
Since I am a newbie, would like to know if it is an expected behavior. I would have expected PROC2 to retrieve the results while PROC1 is inserting, not totally getting blocked and timed out.
How to set the transaction so that other processes may be able to access the records. Please let me know

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

SQLRNNR SSC Guru Points: 281334 More actions · Answer 1

This is not uncommon behavior. It could be mitigated through a little tuning so the processes perform better.

There are also some isolation levels available in SQL server that may help with this situation. You can read about them here:

http://www.sql-server-performance.com/articles/dba/isolation_levels_2005_p1.aspx

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

ucbus 92278 Mr or Mrs. 500 Points: 554 More actions · Answer 2

While I am going thru the article, my question is why the PROC2 beging blcoked from even running the query. It was just waiting for PROC1 to issue commit. I have gone thru the article and did not find a reference. May be need to go thru again.

just to clarify:

PROC1 calls a stored proc that BEIGNs TRAN

then it calls multiple sps to insert records into the child, Grand child and finally parent

then it issues "COMMIT/ROLLBACK" as the case may be.

During the whole process PROC2 waits for PROC1 to finish. But WHY?

SQLRNNR SSC Guru Points: 281334 More actions · Answer 3

The article speaks to isolation levels. IN your case, you may want to consider read uncommitted. There are cautions to be used when using that isolation level.

The transaction that is not committed is blocking the read. The read query may need to read the row that the transaction has locked or there may be a table lock while the transaction is waiting to be committed.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

ucbus 92278 Mr or Mrs. 500 Points: 554 More actions · Answer 4

there may be a table lock while the transaction is waiting to be committed.

My exact thinking. What causes a table lock? how PROC1 which is issung BEGIN TRAN can ensure that there will NOT be a table lock.

Thanks for your input

SQLRNNR SSC Guru Points: 281334 More actions · Answer 5

It is a natural progression that SQL server uses to improve performance. If you are locking a row for long enough it will escalate into a table lock.

Thus, to prevent a table lock, performance tuning the insert statements and the explicit transaction so it runs faster. If a table lock should occur you may want to look into the use of an isolation level.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Jeffrey Williams SSC Guru Points: 90314 More actions · Answer 6

While reviewing the transaction isolation levels, review the snapshot isolation section in further detail. If you decide to use that - be sure you understand the additional requirements and performance impact.

With this, writes won't block reads - but it does have a performance impact and might not be something that you can use.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs