Hi all,
I have a SQL to lock a set of rows for the duration of transaction using FOR UPDATE WAIT
[CODE]
SELECT seqno, trandt, procdatetime FROM MSG
WHERE authnm = :pi_autnm
AND org = :pi_org';
FOR UPDATE WAIT ' || v_update_wait;
[/CODE]
For each session when this SQL being called, I would like turn it to sleep for certain duration by calling another procedure upd_sleep( duration). This procedure will insert some info such as traceKey, DB datetime into a temp table when procedure being called each time. Eventually, I would like to concurrently execute the entire procedure with multiple session/thread ( minimum 2 ), therefore the traceKey should be incremented by one when sleep procedure being called each time.
Let said the sleep duration is 180 secs How can I achieve it with further detail info logged in every 30 secs such as the following
traceKey procdatetime desc
-------------------------------------------------------------------------
1 2013/07/13 21:02:44 thread 1 arrived.
2 2013/07/13 21:03:14 thread 1 in sleeping
3 2013/07/13 21:03:44 thread 2 arrived and waiting
4 2013/07/13 21:04:14 thread 1 sleeping
5 2013/07/13 21:04:44 thread 1 sleeping
6 2013/07/13 21:05:14 thread 1 sleeping
7 2013/07/13 21:05:44 thread 1 released, thread 2 turn
8 2013/07/13 21:06:14 thread 2 sleeping