question on Transactions and Commit

If you start a transaction with BEGIN TRAN and then, for some reason this is
interrupted and not recovered from (in other words, no ROLLBACK or no
COMMIT), what happens? Is there a time after which this will timeout? If
so, where is it set and what is the deafault?
I have a situation where i believe this happened, and now queries to the
table simply hang, do not return and do not give any error message. More
specifically if the query contains a WHERE clause. For instance, the query
SELECT * from Table returns quickly (it is a small table). But SELECT *
from Table WHERE column='xxx' is the one that hangs.
Any advice is appreciated. Thank you.I would expect it to actually be the other way around. But in any case an
open tran will stay that way for as long as the connection that started it
is valid. Your application that begins the tran should always handle
situations sucha s that. You can check to see if there is an open tran in
any db by running DBCC OPENTRAN(). If you find one that is hanging around
long after it should be you can kill that SPID and the tran will be rolled
back automatically. You can also use sp_who2 to see if you are begin blocked
and by who. sp_lock will also show any currently held locks.
Andrew J. Kelly SQL MVP
"THOMAS CONLON" <someone@.verizon.net> wrote in message
news:bJlgg.2906$td6.2754@.trnddc02...
> If you start a transaction with BEGIN TRAN and then, for some reason this
> is interrupted and not recovered from (in other words, no ROLLBACK or no
> COMMIT), what happens? Is there a time after which this will timeout? If
> so, where is it set and what is the deafault?
> I have a situation where i believe this happened, and now queries to the
> table simply hang, do not return and do not give any error message. More
> specifically if the query contains a WHERE clause. For instance, the
> query SELECT * from Table returns quickly (it is a small table). But
> SELECT * from Table WHERE column='xxx' is the one that hangs.
> Any advice is appreciated. Thank you.
>|||THOMAS CONLON (someone@.verizon.net) writes:
> If you start a transaction with BEGIN TRAN and then, for some reason
> this is interrupted and not recovered from (in other words, no ROLLBACK
> or no COMMIT), what happens? Is there a time after which this will
> timeout? If so, where is it set and what is the deafault?
There is no timeout. If there is never any COMMIT or ROLLBACK explicitly,
there will be a ROLLBACK when the connection is physically disconnected.
Physical disconnection happens when the application exits. It can also
happen if the application code issues a disconnect. However, usually
connection is in force in the client API. This causes the physical
connection to be retained for 60 seconds, unless it can be reused in this
time frame. If a connection is reused from the pool, the rollback will
occur on reuse.

> I have a situation where i believe this happened, and now queries to the
> table simply hang, do not return and do not give any error message.
> More specifically if the query contains a WHERE clause. For instance,
> the query SELECT * from Table returns quickly (it is a small table).
> But SELECT * from Table WHERE column='xxx' is the one that hangs.
Note that if you get a "Timeout expired" in your application, you must
submit a IF @.@.trancount > 0 ROLLBACK TRANSACTION". The timeout is
something the client API raises, and SQL Server does not know what it
means, and will keep the transaction alive.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Question on client side and server side transactions

Hi All,
Is it required to provide BEGIN TRANSACTION...END TRANSACTION block inside
stored procedure, if a client has already started a transaction? The reason,
I am asking this question is that, though an error occured in a stored
procedure which was called within a transaction (on the client side), the
rest of the statements in the stored procedure were executed, which was not
desired.
I am have opened a connection and started a transaction in vb.net and called
a stored procedure. The stored procedure calls several other stored
procedures and adds/updates records in linked tables. It so happened that,
one temporary table did not exist and that particular statement failed when
I
tried to select records from the temporary table. But the following
statements were executed. Why was the transacton not being honoured? Ideally
,
when a statement failed, the transaction should be aborted; this is what I
understand.
Any suggestions?
Thanks
kdThen you have to check for error on your own with the variable @.@.Error.
BEGIN TRANSACTION
<Dosomething>
IF @.@.Error = 0
COMMIT TRANSACTION
ELSE ROLLBACK TRANSACTION
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"kd" <kd@.discussions.microsoft.com> schrieb im Newsbeitrag
news:0056D0F3-9A2D-46D5-9667-C01FD7D671B2@.microsoft.com...
> Hi All,
> Is it required to provide BEGIN TRANSACTION...END TRANSACTION block inside
> stored procedure, if a client has already started a transaction? The
> reason,
> I am asking this question is that, though an error occured in a stored
> procedure which was called within a transaction (on the client side), the
> rest of the statements in the stored procedure were executed, which was
> not
> desired.
> I am have opened a connection and started a transaction in vb.net and
> called
> a stored procedure. The stored procedure calls several other stored
> procedures and adds/updates records in linked tables. It so happened that,
> one temporary table did not exist and that particular statement failed
> when I
> tried to select records from the temporary table. But the following
> statements were executed. Why was the transacton not being honoured?
> Ideally,
> when a statement failed, the transaction should be aborted; this is what I
> understand.
> Any suggestions?
> Thanks
> kd
>|||The piece you're missing, is that not all errors cause a Stored Proc to
terminate and return... Most, in fact, just set the value of @.@.Error and
continue processing the next statement. If you want the stored Proc t ostop
and return upon encountering an error from a specific statement, you have to
code that yourself, for each statement, by testing the value of @.@.error
immediately after teh statement executes...
Declare @.Err Integer -- At Beginning of SP
/* ******
Other stuff
*********/
<Statement>
Set @.Err = @.@.Error
If @.Err <> 0 Begin
If @.@.TranCount > 0 Rollback
Raiserror('This is message', 16,1)
Return (@.Err)
End
<rest of Stored Proc>
"kd" wrote:

> Hi All,
> Is it required to provide BEGIN TRANSACTION...END TRANSACTION block inside
> stored procedure, if a client has already started a transaction? The reaso
n,
> I am asking this question is that, though an error occured in a stored
> procedure which was called within a transaction (on the client side), the
> rest of the statements in the stored procedure were executed, which was no
t
> desired.
> I am have opened a connection and started a transaction in vb.net and call
ed
> a stored procedure. The stored procedure calls several other stored
> procedures and adds/updates records in linked tables. It so happened that,
> one temporary table did not exist and that particular statement failed whe
n I
> tried to select records from the temporary table. But the following
> statements were executed. Why was the transacton not being honoured? Ideal
ly,
> when a statement failed, the transaction should be aborted; this is what I
> understand.
> Any suggestions?
> Thanks
> kd
>|||Hi,
I don't have BEGIN TRANSACTION..END TRANSACTION block in the stored
procedure. I have begun a transcation in the client side in vb.net.
try
connection.Open()
trans = connection.BeginTransaction()
... 'called stored procedure
trans.Commit()
command.Connection.Close()
Catch ex As Exception
trans.Rollback()
End Try
My first question is this. Does the stored procedure and all the other
stored procedures, UDFs, etc called by the stored procedure be part of the
transaction started on the client side?
My second question is, what are the errors that cause a stored procedure to
terminate and return? I could may be dummy code for the errors and check
whether the transaction started at the client is honoured at the server end.
Regards,
kd
"CBretana" wrote:
> The piece you're missing, is that not all errors cause a Stored Proc to
> terminate and return... Most, in fact, just set the value of @.@.Error and
> continue processing the next statement. If you want the stored Proc t ost
op
> and return upon encountering an error from a specific statement, you have
to
> code that yourself, for each statement, by testing the value of @.@.error
> immediately after teh statement executes...
> Declare @.Err Integer -- At Beginning of SP
> /* ******
> Other stuff
> *********/
> <Statement>
> Set @.Err = @.@.Error
> If @.Err <> 0 Begin
> If @.@.TranCount > 0 Rollback
> Raiserror('This is message', 16,1)
> Return (@.Err)
> End
> <rest of Stored Proc>
> "kd" wrote:
>