Database Reference
In-Depth Information
8 FOR UPDATE
9
SKIP LOCKED
;
10 begin
11 open c;
12 fetch c into l_rec;
13 if ( c%found )
14 then
15 dbms_output.put_line( 'I got row ' || l_rec.id || ', ' || l_rec.payload );
16 end if;
17 close c;
18 end;
19 /
I got row 2, payload 2
PL/SQL procedure successfully completed.
EODA@ORA12CR1> declare
2 pragma autonomous_transaction;
3 l_rec t%rowtype;
4 cursor c
5 is
6 select *
7 from t
8 where decode(processed_flag,'N','N') = 'N'
9 FOR UPDATE
10
SKIP LOCKED
;
11 begin
12 open c;
13 fetch c into l_rec;
14 if ( c%found )
15 then
16 dbms_output.put_line( 'I got row ' || l_rec.id || ', ' || l_rec.payload );
17 end if;
18 close c;
19 commit;
20 end;
21 /
I got row 4, payload 4
PL/SQL procedure successfully completed.
Both of the preceding “solutions” would help to solve the second serialization problem my client was having
when processing messages. But how much easier would the solution have been if my client had just used Advanced
Queuing and invoked
DBMS_AQ.DEQUEUE
? To fix the serialization issue for the message producer, we had to implement
a function-based index. To fix the serialization issue for the consumer, we had to use that function-based index to
retrieve the records and write code. So we fixed their major problem, caused by not fully understanding the tools they
were using and found only after lots of looking and study since the system was not nicely instrumented. What we
hadn't fixed yet were the following issues:
•
The application was built without a single consideration for scaling at the database level.
•
The application was performing functionality (the queue table) that the database
already
supplied in a highly concurrent and scalable fashion.
I'm referring to the Advance Queuing
(AQ) software that is burned into the database, functionality they were trying to reinvent.
