ORACLE-009: Stored Procedure plus lock

Recently encountered a situation, you need to restrict a stored procedure only one process is executing, the last execution completes after the next execution. This is similar to the thread synchronization problem in program development.

Summarized under one, can be implemented by the following methods.

1, set a variable, or a field in the table is the identity bit, execution is set to a value, after execution and then set to the original value.

The requirement of this method is to set the location of the identification bit and determine the location of this identity bit time interval can not be too long, otherwise it is not up to the requirements.

2, using row lock to achieve.

For example, build a table t_test, insert a few data. Where the stored procedure begins

Select S.idinto v_snfrom t_test s for update;

v_sn-is defined as a variable, because in the stored procedure directly select for Update this form, will compile the error, therefore needs to add into.

Then at the end of the stored procedure, commit or rollback. Of course, to be more insured, you can use exception trapping.

To prevent deadlocks, of course, you can add a wait time after the for update. At this time the stored procedure will report an exception and end, preventing long-time locking.

The specific script is as follows:

Create or Replace procedure Test_pro  is    v_sn varchar2;  Begin          Select S.id     to V_sn    from t_test s for update wait 10;--10s        --Own execution script        commit;--or rollback;< C8/>exception when      OTHERS then        BEGIN          ROLLBACK;        End;end Test_pro;

This allows for general blocking stored procedures.

ORACLE-009: Stored Procedure plus lock