sql - Interrupting Informix Stored Procedures in the middle of 'begin work' and 'commit work' -

admin管理员组

文章数量:1297051

I have a question about Informix. Are there any repercussions when returning a value in the middle of a 'begin work' block? For example:

create or replace procedure updateDataHere(input nvarchar(100)) returning integer as result;
    define o_result int;

    on exception
        rollback work;
    end exception;

    begin work;
    set lock mode to wait 30;

    let o_result = 0;
    
    select limit 1 test into o_result from test_table;

    if o_result == 0 or o_result is null then 
        return -1;
    end if;

    update test_table set columnOne = 'random'
    where test = o_result;
    
    let o_result = dbinfo('sqlca.sqlerrd2');
    if o_result == 0 or o_result is null then
        return -2;
    end if;

    execute procedure insertRemarks("remarks") into o_result;
    if o_result < 1 then
        return -3
    elif o_result is null then
        return -4
    end if;

    commit work;
    return o_result;
end procedure;

Does returning a value in the middle of the block lock the tables being updated? If so, how is it possible to both rollback the work then break out of the block if an error occurs? I am aware of the ROLLBACK WORK statement, but there's not enough examples online to give me a good enough understanding on how it actually works.

I tried both the return method above and ROLLBACK WORK statement like this:

    if o_result == 0 or o_result is null then
        ROLLBACK WORK;
        return -2;
    end if;

But both methods give varying weird results, sometimes the tables lock randomly after a few calls in a row.

I have a question about Informix. Are there any repercussions when returning a value in the middle of a 'begin work' block? For example:

create or replace procedure updateDataHere(input nvarchar(100)) returning integer as result;
    define o_result int;

    on exception
        rollback work;
    end exception;

    begin work;
    set lock mode to wait 30;

    let o_result = 0;
    
    select limit 1 test into o_result from test_table;

    if o_result == 0 or o_result is null then 
        return -1;
    end if;

    update test_table set columnOne = 'random'
    where test = o_result;
    
    let o_result = dbinfo('sqlca.sqlerrd2');
    if o_result == 0 or o_result is null then
        return -2;
    end if;

    execute procedure insertRemarks("remarks") into o_result;
    if o_result < 1 then
        return -3
    elif o_result is null then
        return -4
    end if;

    commit work;
    return o_result;
end procedure;

Does returning a value in the middle of the block lock the tables being updated? If so, how is it possible to both rollback the work then break out of the block if an error occurs? I am aware of the ROLLBACK WORK statement, but there's not enough examples online to give me a good enough understanding on how it actually works.

I tried both the return method above and ROLLBACK WORK statement like this:

    if o_result == 0 or o_result is null then
        ROLLBACK WORK;
        return -2;
    end if;

But both methods give varying weird results, sometimes the tables lock randomly after a few calls in a row.

• sql
• transactions
• informix

Share Improve this question Follow edited Feb 11 at 16:22 Robert 8,6835454 gold badges129129 silver badges169169 bronze badges asked Feb 11 at 16:22 RandomNoobRandomNoob 111 bronze badge

Add a comment  | 

2 Answers 2

Sorted by: Reset to default 0

If you return early, as in the return -1; line, the transaction you started is still in progress.

If you return early, as in the return -2; line, not only is the transaction still in progress but any locks applied by the UPDATE statement will remain in place until the transaction is rolled back or committed.

Similar comments apply after the procedure insertRemarks returns, with the added complication that we don't know whether that procedure does any transaction management.

One way you could improve transaction control would be to add a RETURN to your ON EXCEPTION block, after the ROLLBACK, and by replacing the early returns with appropriate RAISE EXCEPTION statements. You'd probably need to be clever about getting the different values back, but this would clean up the transactions and release locks, etc.

It is possible to do such things in Informix. However this is not the recommended way of writing code. From my experience I've learned that the best way is to issue begin work/commit/rollback always from client and not in the stored procedure.

Otherwise you risk that from time to time you overlook something in your code and either "not in transaction" or "already in transaction" error occur. Of course there are some exceptions but as a general rule transaction management should be made as simple as possible. Also in client code in java it is convenient to annotate your method with Transactional attribute which issues begin/commit/rollback automatically while entering and exiting the procedure. If you will have automated transaction management in the client code and a stored procedure doing the same then problems will arise. You can make method to work correctly with such a procedure. But somebody else could call your method with automated transaction management enabled.

What will happen in case of error? By default error causes exit from procedure and transaction state is not changed. This is a problem because if an error occured after begin work then rollback should be issued. But if an error ocurred before begin work or after commit then rollback causes error. So error handling similar to yours is required. However your error handling has a bug. If rollback was in non-transactional context then procedure will return error "not in transaction" instead of original error. In my company standard way to overcame things like this is to use following construct:

Define  Sql_error Smallint;
Define  Isam_error Smallint;
Define  Txt_error Char(250);

On Exception
Set Sql_error, Isam_error, Txt_error
RollBack Work;
Raise Exception Sql_error, Isam_error, Txt_Error;
End Exception;

本文标签：