Transaction Levels and States ... uncommittable transactions
I have these descriptions from a book I am reading:
"You can detect the transaction level or state by using two system functions:
@@TRAN COUNT can be queried to find the level of transaction (0, >0, or >1)
XACT_STATE() can be queried to find the state of the transaction (0, 1, -1)
These two functions complement each other: @@TRANCOUNT does not report uncommittable transactions and XACT_STATE() does not report the transaction nesting level."
Question: Could you please give me an example of uncommittable transactions?
--------------------------
More...
(0, >0, or >1):
@@TRANCOUNT = 0, the code is not within a transaction.
@@TRANCOUNT > 0, there is an active transaction.
@@TRANCOUNT > 1, indicates the nesting level of nested transactions.
(0, 1, -1):
XACT_STATE(), does not report the transaction nesting level
XACT_STATE() = 0, no active transaction.
XACT_STATE() = 1, uncommitted transaction, can be committed, but the nesting level is not reported.
XACT_STATE() = -1, there is an uncommitted transaction, but it cannot be committed due to a prior fatal error.
"You can detect the transaction level or state by using two system functions:
@@TRAN COUNT can be queried to find the level of transaction (0, >0, or >1)
XACT_STATE() can be queried to find the state of the transaction (0, 1, -1)
These two functions complement each other: @@TRANCOUNT does not report uncommittable transactions and XACT_STATE() does not report the transaction nesting level."
Question: Could you please give me an example of uncommittable transactions?
--------------------------
More...
(0, >0, or >1):
@@TRANCOUNT = 0, the code is not within a transaction.
@@TRANCOUNT > 0, there is an active transaction.
@@TRANCOUNT > 1, indicates the nesting level of nested transactions.
(0, 1, -1):
XACT_STATE(), does not report the transaction nesting level
XACT_STATE() = 0, no active transaction.
XACT_STATE() = 1, uncommitted transaction, can be committed, but the nesting level is not reported.
XACT_STATE() = -1, there is an uncommitted transaction, but it cannot be committed due to a prior fatal error.
Peter Chan
It means transactions already rolled back
ASKER
I have many links and other resources. I need a simple example to explain what I have asked.
Thanks.
Thanks.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Qlemo:
I don't see how updating two different tables would cause a deadlock. But even if against the same table, it wouldn't deadlock in the same trans -- a trans can't deadlock itself.
For example, add these lines after the INSERT to tableA above:
UPDATE tableA SET col1 = 2;
UPDATE tableA SET col1 = 3;
I don't see how updating two different tables would cause a deadlock. But even if against the same table, it wouldn't deadlock in the same trans -- a trans can't deadlock itself.
For example, add these lines after the INSERT to tableA above:
UPDATE tableA SET col1 = 2;
UPDATE tableA SET col1 = 3;
Scott, my SQLs were hypothetical, and not showing what happens outside of that SQL ;-). Of course the exact SQL I showed does not error out.
ASKER
Hi Scott,
I am sure I am not the only one who always appreciates your solid and very helpful posts.
Regards,
Mike
I am sure I am not the only one who always appreciates your solid and very helpful posts.
Regards,
Mike