Oracle programming Art Study Notes (3)-SELECT FOR UPDATE SKIP LOCKED

 

Consider this scenario: when a task waiting for processing is added to a database table, the PROCESSED_FLAG column of the table has only two values: Y and N. This column value is N (not processed) for records inserted into the table ). When other processes read and process this record, the column value is updated from N to Y.

 

Bitmap indexes are suitable for low-cardinality columns. low-base columns only have a few values. But it is not suitable here. Bitmap indexes are used. A key points to multiple rows, and hundreds or more rows may exist. If you update a bitmap index key, hundreds of records pointed to by this key will be effectively locked together with the row you actually updated.

 

You can create a function-based B * Tree index on function decode (process_flag, 'n', 'n') to return N or NULL, the NULL key is used not to place the B * Tree index feature. Only an index is created for records marked with N.

To support concurrent processing of tasks by multiple sessions, you can use the skip locked feature after Oracle11g R1. It allows multiple sessions to concurrently search for the first unlocked, unprocessed record, and then lock the record for processing.

 

The following is an example.

Create a table, index, and add data:

 

 

Create table t

(Id number primary key,

Processed_flag varchar2 (1 ),

Payload varchar2 (20)

);

 

Create index

T_idx on

T (decode (processed_flag, 'n', 'n '));

 

Insert into t

Select r,

Case when mod (r, 2) = 0 then 'n' 'else' end,

'Payload' | r

From (select level r

From dual

Connect by level <= 5 );

 

 

The data in table T is as follows:

Tony @ ORA11GR2> select * from t;

 

ID PR PAYLOAD

----------------------------------------------------

1 Y payload 1

2 N payload 2

3 Y payload 3

4 N payload 4

5 Y payload 5

 

Execute the following process blocks in two sessions,

 

 

Declare

Rochelle rec t % rowtype;

Cursor c

Is

Select *

From t

Where decode (processed_flag, 'n', 'n') = 'n'

FOR UPDATE

Skip locked;

Begin

Open c;

Fetch c into l_rec;

If (c % found)

Then

Dbms_output.put_line ('I got row' | l_rec.id |', '| l_rec.payload );

End if;

Close c;

End;

/

 

 

In the first session, the result is: I got row 2, payload 2.

In the second session, the result is: I got row 4, payload 4.

 

Before Oracle11g R1, you can use the following methods:

 

Create or replace

Function get_first_unlocked_row

Return t % rowtype

As

Resource_busy exception;

Pragma exception_init (resource_busy,-54 );

Rochelle rec t % rowtype;

Begin

For x in (select rowid rid

From t

Where decode (processed_flag, 'n', 'n') = 'n ')

Loop

Begin

Select * into l_rec

From t

Where rowid = x. rid and processed_flag = 'n'

For update nowait;

Return l_rec;

Exception

When resource_busy then null;

End;

End loop;

Return null;

End;

/

 

 

You can also obtain the same result by executing the following processes in two sessions:

 

Declare

Rochelle rec t % rowtype;

Begin

Rochelle REC: = get_first_unlocked_row;

Dbms_output.put_line ('I got row' | l_rec.id |', '| l_rec.payload );

End;

/

 

* If Advanced Queuing is used and DBMS_AQ.DEQUEUE is called, the solution is easier.

 

From: NowOrNever