none
Transaction becomes uncommitable RRS feed

  • Question

  •  

    Keywords:

    Transaction becomes uncommitable; try catch; convert cast; error; 3930; 3998; The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.; Uncommittable transaction is detected at the end of the batch. The transaction is rolled back.

     

    I have a long sql batch that runs inside a transaction but one small fragment of this batch makes started transaction uncommitable. The problem is with impossible-casting nvarchar to bigint (also tried other data types – same result). I used try-catch block and because of that I can handle an error, but my transaction becomes uncommitable. I was surprised that instead of “cast(‘m’ as int)”  I can use “77/0” without problems. When I’m trying to commit transaction I have an error “3930 : The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.”, but I need to commit changes.

    Here is the SQL query.

    begin tran

     

          -- ...... some logic here that shoud be inside transaction .....

     

          declare @some_user_value      nvarchar(100)

          set @some_user_value = '12m7' --oops bad value somehow here

     

          declare @some_result    bigint

     

          begin try

     

                -- here is dammed part

                set @some_result = cast(@some_user_value as bigint) + 32

     

          end try

          begin catch

     

                set @some_result = -1

     

          end catch

     

          print 'WHY xact_state() = ' + cast(xact_state() as nvarchar(5))

     

          select @some_result

     

          -- ...... some logic here that shoud be inside transaction .....

     

    Commit

     

    Msg 3930, Level 16, State 1, Line 24

    The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.

    Msg 3998, Level 16, State 1, Line 1

    Uncommittable transaction is detected at the end of the batch. The transaction is rolled back.

    I suppose that probably some execution options should be set, I’ve tried XACT_ABORT, ARITH_ABORT but nothing changed, I can’t commit transaction at the end.

     

    Saturday, October 20, 2007 6:50 PM

Answers

  • You could try using the ISNUMERIC function before attempting to cast to a BIGINT, see below. That way you could avoid using the TRY CATCH construct altogether.

     

    More info on ISNUMERIC:

     

    http://technet.microsoft.com/en-us/library/ms186272.aspx

     

    Note that strings can contain certain non-numeric characters and be treated as numerics by the ISNUMERIC function, so this might not be the most appropriate solution.

     

    Better still, is there a way you can filter out the bad data before it even gets to the stored proc? For example, perform validation against any user-input.

     

    Chris

     

    Code Block

    IF ISNUMERIC(@some_user_value) = 1
     set @some_result = cast(@some_user_value as bigint) + 32
    ELSE
     set @some_result = -1

     

     

    Saturday, October 20, 2007 10:00 PM
  • This is a good start, but it will only help, not completely cure the problem as there are some strings that will still fail:

     

    '10E0'

     

    That will be allowed by isnumeric AND still cause these issues.
    Saturday, October 20, 2007 10:05 PM
    Moderator

All replies

  • You could try using the ISNUMERIC function before attempting to cast to a BIGINT, see below. That way you could avoid using the TRY CATCH construct altogether.

     

    More info on ISNUMERIC:

     

    http://technet.microsoft.com/en-us/library/ms186272.aspx

     

    Note that strings can contain certain non-numeric characters and be treated as numerics by the ISNUMERIC function, so this might not be the most appropriate solution.

     

    Better still, is there a way you can filter out the bad data before it even gets to the stored proc? For example, perform validation against any user-input.

     

    Chris

     

    Code Block

    IF ISNUMERIC(@some_user_value) = 1
     set @some_result = cast(@some_user_value as bigint) + 32
    ELSE
     set @some_result = -1

     

     

    Saturday, October 20, 2007 10:00 PM
  • This is a good start, but it will only help, not completely cure the problem as there are some strings that will still fail:

     

    '10E0'

     

    That will be allowed by isnumeric AND still cause these issues.
    Saturday, October 20, 2007 10:05 PM
    Moderator
  • The way I've found it you have to put the begin transaction and commit inside of the begin try and you have to add a rollback inside the catch.  But the test for numeric is a great way to resolve issues before processing (in most cases).

    looks something like this
    -- ...... some logic here that shoud be inside transaction .....

    declare @some_user_value      nvarchar(100)
    set @some_user_value = '12m7' --oops bad value somehow here

    declare @some_result    bigint
    begin try
        begin tran
                -- here is dammed part
                set @some_result = cast(@some_user_value as bigint) + 32

        Commit
    end try
    begin catch
    rollback
        set @some_result = -1
    end catch

    print 'WHY xact_state() = ' + cast(xact_state() as nvarchar(5))
    select @some_result

    -- ...... some logic here that shoud be inside transaction .....
    Monday, November 19, 2007 5:38 PM