Table locking problem

[fcd123]

There is a table which stores the last control number for a specific control key.First I read this table to get the last generated number. After this I check within the same table which contains say order no,print date,file no.etc., whether a records exists for the order no. If the record exists I update the new values else I insert the record. When more than two users are using this session simultaneously, the last number is not picked up,probably because of the locking by an update of this table which is at the same time being run by another user. Hence the new number is not generated. Is there any way of locking/releasing the lock of the table ?

I am also attaching the logic :-

if tssma301.tfrm = tcyesno.yes then
        reprint = "REPRINT"
                check.reprint.tsgal045()
        else
        reprint = ""
                pr.fino=0
                get.franc.code()                               
                if tmp.suno <> ""
                then                                            
                        check.last.fino()
                        check.fresh.print.tsgal045()
                else
                        write.file()                            
                endif
        endif

function get.franc.code()                              
{
        select  tsgal046.*
        from    tsgal046
        where   tsgal046._index1 = {:tssma301.cctp}
        and     (tsgal046.f_care <= :tssma301.care and
                 tsgal046.t_care >= :tssma301.care)
        as set with 1 rows
        selectdo
                tmp.suno = tsgal046.suno
        endselect
}

function check.fresh.print.tsgal045()
{
        select  tsgal045.*
        from    tsgal045 for update
        where   tsgal045._index2 = {:tssma301.orno}
        selectdo
                update.rec()
        selectempty
                insert.rec()
        endselect
}
function insert.rec()
{
        tsgal045.suno = tmp.suno
        tsgal045.orno = tssma301.orno
        pr.fino = pr.fino + 1
        tsgal045.fino = pr.fino
        tsgal045.login = login.name
        tsgal045.cctp = tssma301.cctp                   
        tsgal045.prdt = date.num()
        tsgal045.care = tssma301.care                   
        tsgal045.cloc = tssma301.cloc
        db.insert(ttsgal045,db.retry,db.skip.dupl)
        commit.transaction()
}

function update.rec()
{
        tsgal045.suno = tmp.suno
        pr.fino = pr.fino + 1
        tsgal045.fino = pr.fino
        tsgal045.login = login.name
        tsgal045.cctp = tssma301.cctp                   
        tsgal045.prdt = date.num()
        tsgal045.care = tssma301.care                   
        tsgal045.cloc = tssma301.cloc
        db.update(ttsgal045,db.retry)
        commit.transaction()
}

[mark_h]

The only thing I see is you say db.retry in the insert and update, but I do not see where you set the db.retry.point() at - I would add it before the select tsgal045 for update statement.

Mark

[toolswizard]

You can also apply an application lock. This is a logical lock between co operating programs. You would set the lock before reading the next available number, validate, increment, and same the new value, then release the lock as you continue processing. The other user would attempt to do the same, and you can instruct the session to wait until the lock is freed before continuing. Check most of the order processing sessions which use the first free number table, and the function source file for more information.