Any limits in number of actions in a single transaction??

Question

Any limits in number of actions in a single transaction??

boeledi

Ten Centuries

Points: 1151
More actions
November 8, 2010 at 5:04 am

#388185

Dear All,
Environment: VS2008 pro, SQLServer 2008
Mode: Debug mode
Context: development of a Web site
I have to run transaction during which I need to apply many modifications to several tables.
In fact, I am working on 9 tables during the transaction and on the 9 tables I can apply several inserts, updates and deletes.
I initiate the transaction as follows:
Try
'Open database and a transaction
Using conDB As New SqlConnection(ThisConfig.getDBConnectionString())
conDB.Open()
Dim transDB As SqlTransaction = conDB.BeginTransaction(IsolationLevel.ReadCommitted)
...
and use a series of Stored Procedure, accessed like this:
'Map the stored procedure to invoke
Using cmd As New SqlCommand(<stored proc name>, vConDB, vTransaction)
cmd.CommandType = CommandType.StoredProcedure
-----
During the transaction (via another thread -- another user requesting the site to do something), when I need to invoke a stored procedure which simply needs to retrieve a series of records from one of the tables involved in the transaction (still on-going), I receive a Timeout.
The invocation of this stored procedure is not linked to the transaction and is built as follows:
Try
Using conDB As New SqlConnection(ThisConfig.getDBConnectionString())
conDB.Open()
'Map the stored procedure to invoke
Using cmd As New SqlCommand(<stored proc name>, conDB)
cmd.CommandType = CommandType.StoredProcedure
'Map the parameters to send to the stored procedure
cmd.Parameters.Add(New SqlParameter("@in_param1", vParam1))
cmd.Parameters.Add(New SqlParameter("@in_param2", vParam2))
'Execute the stored procedure
Using reader As SqlDataReader = cmd.ExecuteReader <== Timeout HERE
If (reader IsNot Nothing) Then
While (reader.Read())
If (Not IsDBNull(reader("key1"))) Then
... do something ...
End If
End While
End If
End Using
End Using
End Using
Catch Ex As Exception
Throw Ex
End Try
This stored procedure is written like this:
CREATE PROCEDURE [dbo].[<stored proc name>]
(
@in_param1bigint,
@in_param2int
)
AS
BEGIN
SET NOCOUNT ON;
SELECT record_id FROM tbl_mytable WHERE param1 = @in_param1 AND param2 = @in_param2;
END
Therefore my question... Is there any limitation to the number of actions you can perform on several tables within a same big transaction?
In advance, many thanks
Didier

Viewing 3 posts - 1 through 3 (of 3 total)

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

Jeff Moden SSC Guru Points: 1004638 More actions · Answer 1

First, "TRY" doesn't start a transaction. You have to use a BEGIN TRANSACTION/COMMIT pair to encapsulate all that you wish to do in a transaction. And no, there's no real limit to what you can do in a transaction but it IS generally considered to be a best practice to limit the length and scope of a transaction to as little as possible to prevent large amounts of blocking, possible deadlocks, and... timeouts.

Also, you don't really need an explicit transaction (BEGIN TRANSACTION) to get a timeout on a busy system. For example, if you have an index where the first column is based on something with very low cardinality (such as a BIT datatype), large numbers of "page splits" may occur within the index. If it's a clustered index, the splitting will occur at the page level. If it's a non-clustered index, the splitting can occur at the (IIRC) EXTENT (8 pages) level. It takes a relatively long period of time for such splits to complete and are quite sensitive to blocking... which can cause massive amounts of timeouts on a busy system.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

GSquared SSC Guru Points: 260824 More actions · Answer 2

First, managing the transactions from the calling code, is generally a really bad idea. You're most often much better off to manage the transactions in the stored procedures. I'd recommend looking at creating an overall stored procedure that you pass your parameters to, which then calls your procs and manages the transaction within the server. Otherwise, unless I'm mistaken, a lost connection can result in a hung transaction that may have to be manually killed and then re-run. I could be mistaken about that, but I think that's how it works.

Second, no, there really isn't a limit on the number of actions in a single transaction. There are limits, based on how much disk space your transaction log file has available, but in practice, you'll almost certainly not run into them unless you have disk space issues.

What I'd do in this case is take a look at the active connections at the server level, and see what's being blocked by what. Start with sp_who or with the various activity reports, Activity Monitor, and see what's blocking what. Timeouts might be caused by blocks, but they can also be caused by a huge number of other factors. Till you know what's blocking what, you can't really start solving the problem.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon