I have multiprocessing C program use oracle PL/SQL, The parent process should lock the table

lock table tbl1 in exclusive mode nowait;

and childs can ONLY commit .

if the childs commit work , does that means locking on the table will be released ?

Note : i need to prevent another instance of the program from running by check if the table is locked then exit.

CodePudding user response：

Commit (or rollback) will release the lock.

LOCK TABLE documentation says:

A locked table remains locked until you either commit your transaction or roll it back, either entirely or to a savepoint before you locked the table.

Note that lock will be released if user - who locked the table - commits, not if anyone else commits. Their commit (if that's what "child" means in this context) won't have any affect.

CodePudding user response：

That architecture doesn't make sense. If the children are running in separate sessions, they'd have no way to use a lock acquired by the parent. If the children are running in the same session than the first child to commit will release the lock.

If the goal is merely to prevent two instances of the same application from running at the same time, use the dbms_lock.request method to acquire a custom lock. Something like

l_success := dbms_lock.request( lockhandle => 'MyProcess' );

Your parent process can happily hold that lock while the children do whatever they want and then release the lock when all the children complete.