Re: BEGIN WORK - COMMIT WORK does not seem to work as a transaction????

You have no error/exception handling in your procedure. Look at the ON EXCEPTION and RAISE EXCEPTION SPL statements. You have to perform a ROLLBACK WORK on any exception and return an error from the procedure. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) IIUG Board of Directors (art@iiug.org) See you at the 2010 IIUG Informix Conference April 25-28, 2010 Overland Park (Kansas City), KS www.iiug.org/conf Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Mon, Jan 18, 2010 at 3:24 PM, Gentian Hila <genti.tech@gmail.com> wrote: > We have IDS 9.40. > > I am trying to put a lock on the order based on the certain > criteria(order source) > > When the order is created with that criteria, it inserts the order id > in ord_track table. > > Then periodically a procedure is called and it is supposed to update > the status of the order to on hold and then delete that record so next > time is not put again on hold for a second time. > > The update and delete actions are supposed to work together. If update > fails the delete should fail too. So when I created the stored > procedure, I created it within a transaction block with BEGIN WORK and > COMMIT WORK. (See below). > > However, it appears that some times when the database has put a lock > on the order row, it does not update the row but still delete the > record from ord_track. > > How can force the delete operation to work only on the succesful > return of the update operation and fail when update fails? > > Here is the stored procedure: > > > CREATE PROCEDURE update_hold_status1()> DEFINE v_ord_id INTEGER; > DEFINE v_hold_status SMALLINT; > DEFINE n_hold_status SMALLINT; > DEFINE v_order_source_type SMALLINT; > DEFINE v_def_order_source CHAR(20); > > > SET ISOLATION REPEATABLE READ;> BEGIN WORK; > > > FOREACH ord_track_cursor FOR > SELECT ord_id, order_source_type INTO v_ord_id, v_order_source_type > from ord_track > SELECT hold_status, def_order_source INTO v_hold_status,> v_def_order_source FROM ord where ord_id = v_ord_id; > > IF v_hold_status in (2,3) THEN > LET n_hold_status = 3; > ELSE > LET n_hold_status = 1; > END IF > > IF v_order_source_type = 2 THEN > LET v_def_order_source = 'REP'; > END IF > > UPDATE ord SET hold_status = n_hold_status, def_order_source => v_def_order_source where ord_id = v_ord_id; > > DELETE FROM ord_track where ord_id = v_ord_id;> > > END FOREACH > COMMIT WORK; > SET ISOLATION COMMITTED READ;> > END PROCEDURE > > > > > > Thank you for any suggestion. > > Genti > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list >