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.
LVL 34
Mike EghtebasDatabase and Application DeveloperAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

HuaMin ChenProblem resolverCommented:
It means transactions already rolled back
Mike EghtebasDatabase and Application DeveloperAuthor Commented:
I have many links and other resources. I need a simple example to explain what I have asked.

Thanks.
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
begin tran
update table1 set x = 1 where y = 2;
update table2 set x = 1 where y = 2;  ---    <- deadlock or lock timeout

Open in new window

Now we have a uncommitable transaction, and need to perform either some correction, like a rtery, or a rollback, to take back all changes of the transaction. Would you do a COMMIT, only parts of the transaction are successfully committed - table2 did not change.
Scott PletcherSenior DBACommented:
Certain types of errors -- such as an invalid column name in a DROP COLUMN -- will cause SQL not to be able to commit the current transaction, even what was successful earlier.  In that case, the only option is to rollback the transaction.  For example:

IF OBJECT_ID('tableA') IS NOT NULL
    DROP TABLE tableA
IF OBJECT_ID('tableB') IS NOT NULL
    DROP TABLE tableB;

CREATE TABLE tableA (
    col1 int NOT NULL
    )
CREATE TABLE tableB
    (
    col1 int PRIMARY KEY,
    col2 nvarchar(100)
    );

BEGIN TRY
    BEGIN TRANSACTION;
        INSERT INTO tableA VALUES(1); --this is successful
        ALTER TABLE tableB -- Causes an error because the column does not exist in the table.
            DROP COLUMN colNotFound;
    -- If no errors, commit the transaction.
    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    SELECT * FROM tableA; --verify the INSERT to tableA worked
    SELECT
        ERROR_NUMBER() as ErrorNumber,
        ERROR_MESSAGE() as ErrorMessage,
        XACT_STATE() AS XACT_STATE;
    --COMMIT TRANSACTION --uncomment this line to see what happens.
    --there is no way to commit the row that was successfully INSERTed to tableA --
    --  it *must* be rolled back and thus "lost" from the table.
    IF XACT_STATE() = -1
        ROLLBACK TRANSACTION
    ELSE
    IF XACT_STATE() = 1
        COMMIT TRANSACTION
    SELECT * FROM tableA; --verify that the row was "lost"
END CATCH;

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Scott PletcherSenior DBACommented:
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;
Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
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.
Mike EghtebasDatabase and Application DeveloperAuthor Commented:
Hi Scott,

I am sure I am not the only one who always appreciates your solid and very helpful posts.

Regards,

Mike
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.