Blocking and Transactions OK?

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(@Col3Dat
a 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

laurenq uantrell  (laurenquantrell@hot
mail.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(@Col3Dat
a AS nvarchar(4000)),'<none>')
>...

What's the point with accepting an ntext parameter, if you truncate it
anyway?


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

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

Erland,
Thanks for your reply.

> @Col3Data ntext = Null,
>...
> 'Text Label: ' +
>  isnull(Cast(@Col3Dat
a AS nvarchar(4000)),'<none>')
>...

>What's the point with accepting an ntext parameter, if you truncate it anyway?[/col
or]

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.

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.

laurenq uantrell  (laurenquantrell@hot
mail.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, esquel@sommarskog.se

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

>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?

>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?

laurenq uantrell  (laurenquantrell@hot
mail.com)  writes: 
>
> 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, esquel@sommarskog.se

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

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

laurenq uantrell  (laurenquantrell@hot
mail.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, esquel@sommarskog.se

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