By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,929 Members | 1,401 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,929 IT Pros & Developers. It's quick & easy.

Blocking and Transactions OK?

P: n/a
I'm wondering if I'm doing this right. Wondering about the transactions
and error handling. (Do I even need to put BEGIN TRANSACTION AND
COMMIT TRANSACTION in there?)

I think that this sproc is causing occasional blocking:

Alter Procedure sprocINSERTSTUFF
@Col1Data int = Null,
@Col2Data nvarchar(255) = Null,
@Col3Data ntext = Null,
@UniqueID int OUTPUT

AS

set nocount on
set xact_abort on

DECLARE @err int
DECLARE @ServerDate DateTime
SELECT @ServerDate = GETUTCDATE()

BEGIN TRANSACTION
INSERT INTO
tblStuff (Col1, Col2, Col3, DateCreated, etc.)
VALUES
(@Col1Data, @Col2Data, @Col3Data, @ServerDate, etc.)

SELECT @err = @@error IF @err <> 0 BEGIN ROLLBACK TRANSACTION RETURN
@err END

SELECT @UniqueID = SCOPE_IDENTITY()

COMMIT TRANSACTION

BEGIN TRANSACTION

INSERT INTO
tblMoreStuff (UniqueID, DateCreated, Col1, Col2, Col3)
Values
(@UniqueID, @ServerDate, @Col1Data, @Col2Data, 'Text Label: ' +
isnull(Cast(@Col3Data AS nvarchar(4000)),'<none>')

SELECT @err = @@error IF @err <> 0 BEGIN ROLLBACK TRANSACTION RETURN
@err END

COMMIT TRANSACTION
SELECT @err = @@error IF @err <> 0 RETURN @err

Sep 23 '05 #1
Share this Question
Share on Google+
9 Replies


P: n/a
laurenq uantrell (la*************@hotmail.com) writes:
I'm wondering if I'm doing this right. Wondering about the transactions
and error handling. (Do I even need to put BEGIN TRANSACTION AND
COMMIT TRANSACTION in there?)
Depends on your business requirements. If it's OK that a row gets
inserter into tblStuff, but not any row in tblMoreStuff, you can take
it out entirely. If you want both rows or none of the rows inserted,
you should take out the COMMIT and the BEGIN in the middle, to make
it one single transaction.
I think that this sproc is causing occasional blocking:
Since it's only two plain insert statements, that's a bit surprising.
But if there is a long-running trigger on one of the tables you could
get blocking.
@Col3Data ntext = Null,
...
'Text Label: ' +
isnull(Cast(@Col3Data AS nvarchar(4000)),'<none>')
...


What's the point with accepting an ntext parameter, if you truncate it
anyway?
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

Sep 23 '05 #2

P: n/a
Erland,
Thanks for your reply.
@Col3Data ntext = Null,
...
'Text Label: ' +
isnull(Cast(@Col3Data AS nvarchar(4000)),'<none>')
... What's the point with accepting an ntext parameter, if you truncate it anyway?


The second table is used to hold a history of changes to the first
table tblStuff and in this particular case it's not necessary (or
desirable) to have the unlimited text in any ntext column stored, so
it's truncated.

Sep 23 '05 #3

P: n/a
I forgot to mention in my front end application (MS Access 2K) I use
the following:

On Error GoTo myErr:
' Execute the sproc that runs the Insert above
myErr:
CurrentProject.Connection.Execute "IF @@trancount > 0 ROLLBACK
TRANSACTION", , adExecuteNoRecords

I am wondering if this should be sufficient to not leave an open
transaction in the event of an error.

Sep 23 '05 #4

P: n/a
laurenq uantrell (la*************@hotmail.com) writes:
I forgot to mention in my front end application (MS Access 2K) I use
the following:

On Error GoTo myErr:
' Execute the sproc that runs the Insert above
myErr:
CurrentProject.Connection.Execute "IF @@trancount > 0 ROLLBACK
TRANSACTION", , adExecuteNoRecords

I am wondering if this should be sufficient to not leave an open
transaction in the event of an error.


In theory maybe. But good software practice is that every module
cleans up after itself, and does not rely on somebody else to do it.
Least of all one should trust an application that uses ADO.

Look at it this way: you have this handling in many stored procedures.
Maybe you happen to forget to insert it in some place. And you have
this handling in many places in your client code. Maybe you acceidently
leave it in some place. So keep a double safeguard.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

Sep 24 '05 #5

P: n/a
>Look at it this way: you have this handling in many stored procedures.
Maybe you happen to forget to insert it in some place. And you have
this handling in many places in your client code. Maybe you acceidently
leave it in some place. So keep a double safeguard.


I'm not sure what you'rer suggesting...

I thought that this would clear up any error in the sproc:

SELECT @err = @@error IF @err <> 0 BEGIN ROLLBACK TRANSACTION RETURN
@err END
COMMIT TRANSACTION
SELECT @err = @@error IF @err <> 0 RETURN @err
Are you suggesting I put:

IF @@trancount > 0 ROLLBACK TRANSACTION

inthe sproc as well and not check for that in the front-end
application?

Sep 24 '05 #6

P: n/a
>Look at it this way: you have this handling in many stored procedures.
Maybe you happen to forget to insert it in some place. And you have
this handling in many places in your client code. Maybe you acceidently
leave it in some place. So keep a double safeguard.


I'm not sure what you'rer suggesting...

I thought that this would clear up any error in the sproc:

SELECT @err = @@error IF @err <> 0 BEGIN ROLLBACK TRANSACTION RETURN
@err END
COMMIT TRANSACTION
SELECT @err = @@error IF @err <> 0 RETURN @err
Are you suggesting I put:

IF @@trancount > 0 ROLLBACK TRANSACTION

inthe sproc as well and not check for that in the front-end
application?

Sep 24 '05 #7

P: n/a
laurenq uantrell (la*************@hotmail.com) writes:
Look at it this way: you have this handling in many stored procedures.
Maybe you happen to forget to insert it in some place. And you have
this handling in many places in your client code. Maybe you acceidently
leave it in some place. So keep a double safeguard.


I'm not sure what you'rer suggesting...

I thought that this would clear up any error in the sproc:

SELECT @err = @@error IF @err <> 0 BEGIN ROLLBACK TRANSACTION RETURN
@err END
COMMIT TRANSACTION
SELECT @err = @@error IF @err <> 0 RETURN @err
Are you suggesting I put:

IF @@trancount > 0 ROLLBACK TRANSACTION

inthe sproc as well and not check for that in the front-end
application?


So I'm suggesting that you should keep things as they are. (But make
sure that your transaction scope is the right one. The procedure
you posted seemed funny to me with a COMMIT in the middle.)
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

Sep 24 '05 #8

P: n/a
Erland. Thanks for the help and the good avice. The COMMIT in the
middle in this situation is because the first insert puts a new row of
data into a table, the second insert records the row/column info into a
row in a history table. In this situation, I want to be sure that the
new record is recorded in the primary data table even if the history is
not. If there is a failure on the second insert, I don't want to
rollback the first insert, even though the records might not end up in
sync (original and history) this would be corrected hopefully the next
time the record is updated.
I have considered and rejected using triggers for this purpose even
though that has been suggested.
Thanks again for your help in m understanding of how these things work!
lq

Sep 24 '05 #9

P: n/a
laurenq uantrell (la*************@hotmail.com) writes:
Erland. Thanks for the help and the good avice. The COMMIT in the
middle in this situation is because the first insert puts a new row of
data into a table, the second insert records the row/column info into a
row in a history table. In this situation, I want to be sure that the
new record is recorded in the primary data table even if the history is
not. If there is a failure on the second insert, I don't want to
rollback the first insert, even though the records might not end up in
sync (original and history) this would be corrected hopefully the next
time the record is updated.
Good. I just wanted to make sure that it's on purpose.

(I remember a system I worked with looooong ago. There was a stored
proecdure that filled up a table, and it was one long transaction.
Unfortunately, it tended to fill up the transaction log. (This was
Sybase 4.x, no autogrow.) My colleagues found the fix - they inserted
some COMMIT/BEGIN. Oh well.)
I have considered and rejected using triggers for this purpose even
though that has been suggested.


Well, that would buy you a transaction for the current and history table.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

Sep 24 '05 #10

This discussion thread is closed

Replies have been disabled for this discussion.