Database Reference
In-Depth Information
23 return null;
24 end;
25 /
Function created.
in the preceding code, i ran some DDl—the CREATE OR REPLACE FUNCTION . right before DDl runs,
it automatically commits, so there was an implicit COMMIT in there. the rows we've inserted are committed in the
database—and that fact is necessary for the following examples to work correctly. in general, i'll use that fact in the
remainder of the topic. if you run these examples without performing the CREATE OR REPLACE , make sure to COMMIT first!
Note
Now, if we use two different transactions, we can see that both get different records. We also see that both get
different records concurrently (using autonomous transactions once again to demonstrate the concurrency issues):
EODA@ORA12CR1> declare
2 l_rec t%rowtype;
3 begin
4 l_rec := get_first_unlocked_row;
5 dbms_output.put_line( 'I got row ' || l_rec.id || ', ' || l_rec.payload );
6 end;
7 /
I got row 2, payload 2
PL/SQL procedure successfully completed.
EODA@ORA12CR1> declare
2 pragma autonomous_transaction;
3 l_rec t%rowtype;
4 begin
5 l_rec := get_first_unlocked_row;
6 dbms_output.put_line( 'I got row ' || l_rec.id || ', ' || l_rec.payload );
7 commit;
8 end;
9 /
I got row 4, payload 4
PL/SQL procedure successfully completed.
Now, in Oracle 11 g Release 1 and above, we can achieve the preceding logic using the SKIP LOCKED clause. In the
following example we'll do two concurrent transactions again, observing that they each find and lock separate records
concurrently.
EODA@ORA12CR1> declare
2 l_rec t%rowtype;
3 cursor c
4 is
5 select *
6 from t
7 where decode(processed_flag,'N','N') = 'N'
 
Search WWH ::




Custom Search