Tuesday, February 10, 2009

What's in my JMS queue?

If you have setup a JMS queue with Oracle's Advanced Queuing, a question that pops up regularly is: "What's currently in the queue?". Now that can't be hard - and it isn't - yet I spent a silly amount of time getting it done. Hopefully this blog post will prevent others spending too much time.

To setup the JMS queue, create a queue table with payload type sys.aq$_jms_text_message, and a queue in that queue table:

rwijk@ORA11GR1> begin
2 dbms_aqadm.create_queue_table
3 ( queue_table => 'my_queue_table'
4 , queue_payload_type => 'sys.aq$_jms_text_message'
5 );
6 dbms_aqadm.create_queue
7 ( queue_name => 'my_queue'
8 , queue_table => 'my_queue_table'
9 );
10 dbms_aqadm.start_queue
11 ( queue_name => 'my_queue'
12 );
13 end;
14 /

PL/SQL procedure successfully completed.


Next, put a message on the queue:

rwijk@ORA11GR1> declare
2 l_enqueue_options dbms_aq.enqueue_options_t;
3 l_message_properties dbms_aq.message_properties_t;
4 l_message sys.aq$_jms_text_message;
5 l_msgid raw(16);
6 begin
7 l_message := sys.aq$_jms_text_message.construct;
8 l_message.set_text(xmltype('<emp><ename>ROB</ename></emp>').getClobVal());
9 dbms_aq.enqueue
10 ( queue_name => 'my_queue'
11 , enqueue_options => l_enqueue_options
12 , message_properties => l_message_properties
13 , payload => l_message
14 , msgid => l_msgid
15 );
16 commit;
17 end;
18 /

PL/SQL procedure successfully completed.


You can query a queue by querying the queue table and filtering on the q_name column, like this:

rwijk@ORA11GR1> select msgid
2 , enq_time
3 , enq_uid
4 , user_data
5 from my_queue_table
6 where q_name = 'MY_QUEUE'
7 /

MSGID
--------------------------------
ENQ_TIME
---------------------------------------------------------------------------
ENQ_UID
------------------------------
USER_DATA(HEADER(REPLYTO(NAME, ADDRESS, PROTOCOL), TYPE, USERID, APPID, GROUPID,
--------------------------------------------------------------------------------
40279F37C785499CB386CC47ABCAAB1C
10-FEB-09 11.59.16.312000 PM
RWIJK
AQ$_JMS_TEXT_MESSAGE(AQ$_JMS_HEADER(NULL, NULL, NULL, NULL, NULL, NULL, NULL), 2
9, '<emp><ename>ROB</ename></emp>', NULL)


1 row selected.


At work I use an older SQL*Plus version, that is not able to show the contents of the user_data column, because of some LOB content. No problem, as I am really only interested in the text_vc field of the sys.aq$_jms_text_message object. So just query that field:

rwijk@ORA11GR1> desc my_queue_table
Name Null? Type
----------------------------------------- -------- ------------------------
Q_NAME VARCHAR2(30)
MSGID NOT NULL RAW(16)
CORRID VARCHAR2(128)
PRIORITY NUMBER
STATE NUMBER
DELAY TIMESTAMP(6)
EXPIRATION NUMBER
TIME_MANAGER_INFO TIMESTAMP(6)
LOCAL_ORDER_NO NUMBER
CHAIN_NO NUMBER
CSCN NUMBER
DSCN NUMBER
ENQ_TIME TIMESTAMP(6)
ENQ_UID VARCHAR2(30)
ENQ_TID VARCHAR2(30)
DEQ_TIME TIMESTAMP(6)
DEQ_UID VARCHAR2(30)
DEQ_TID VARCHAR2(30)
RETRY_COUNT NUMBER
EXCEPTION_QSCHEMA VARCHAR2(30)
EXCEPTION_QUEUE VARCHAR2(30)
STEP_NO NUMBER
RECIPIENT_KEY NUMBER
DEQUEUE_MSGID RAW(16)
SENDER_NAME VARCHAR2(30)
SENDER_ADDRESS VARCHAR2(1024)
SENDER_PROTOCOL NUMBER
USER_DATA SYS.AQ$_JMS_TEXT_MESSAGE
USER_PROP SYS.ANYDATA

rwijk@ORA11GR1> select msgid
2 , enq_time
3 , enq_uid
4 , user_data.text_vc
5 from my_queue_table
6 where q_name = 'MY_QUEUE'
7 /
, user_data.text_vc
*
ERROR at line 4:
ORA-00904: "USER_DATA"."TEXT_VC": invalid identifier


And this message puzzled me for a quite a while. After an extensive search, an AskTom thread provided the answer. A very simple answer: you need to prefix the expression with a table alias:

rwijk@ORA11GR1> select msgid
2 , enq_time
3 , enq_uid
4 , qt.user_data.text_vc
5 from my_queue_table qt
6 where q_name = 'MY_QUEUE'
7 /

MSGID
--------------------------------
ENQ_TIME
---------------------------------------------------------------------------
ENQ_UID
------------------------------
USER_DATA.TEXT_VC
--------------------------------------------------------------------------------
40279F37C785499CB386CC47ABCAAB1C
10-FEB-09 11.59.16.312000 PM
RWIJK
<emp><ename>ROB</ename></emp>


1 row selected.

Saturday, February 7, 2009

FOR UPDATE SKIP LOCKED

The behaviour of the "for update skip locked" clause is not what I expected it to be. I expected it to try to lock all rows when opening a cursor, just like a regular "for update", and then just skipping the locked rows. Below is a test to show how it works in reality. The setup:

A table with three rows:

rwijk@ORA11GR1> create table t (id)
2 as
3 select level
4 from dual
5 connect by level <= 3
6 /

Tabel is aangemaakt.


A procedure to check if a record of this table t is locked:

rwijk@ORA11GR1> create procedure check_record_locked
2 ( p_id in t.id%type
3 )
4 is
5 cursor c
6 is
7 select 'dummy'
8 from t
9 where id = p_id
10 for update nowait
11 ;
12 e_resource_busy exception;
13 pragma exception_init(e_resource_busy,-54);
14 begin
15 open c;
16 close c;
17 dbms_output.put_line('Record ' || to_char(p_id) || ' is not locked.');
18 rollback;
19 exception
20 when e_resource_busy then
21 dbms_output.put_line('Record ' || to_char(p_id) || ' is locked.');
22 end check_record_locked;
23 /

Procedure is aangemaakt.


First, let's examine the default behaviour of the regular "for update" clause. During the 10 seconds sleep, I copy, paste and execute the code in the comments in another SQL*Plus session.

rwijk@ORA11GR1> declare
2 cursor c is select id from t where id <= 2 for update;
3 l_id t.id%type;
4 begin
5 open c;
6 fetch c into l_id;
7 dbms_lock.sleep(10);
8 --
9 -- In another session run:
10 --
11 -- begin
12 -- check_record_locked(1);
13 -- check_record_locked(2);
14 -- check_record_locked(3);
15 -- end;
16 -- /
17 --
18 close c;
19 end;
20 /

PL/SQL-procedure is geslaagd.


The output from the other session:

rwijk@ORA11GR1> begin
2 check_record_locked(1);
3 check_record_locked(2);
4 check_record_locked(3);
5 end;
6 /
Record 1 is locked.
Record 2 is locked.
Record 3 is not locked.

PL/SQL-procedure is geslaagd.


As expected, the opening of the cursor locks rows 1 and 2. Whether the record is fetched or not, has no influence.

Now a similar test with the "for update skip locked" clause:

rwijk@ORA11GR1> rollback
2 /

Rollback is voltooid.

rwijk@ORA11GR1> declare
2 cursor c is select id from t where id <= 2 for update skip locked;
3 l_id t.id%type;
4 begin
5 open c;
6 dbms_lock.sleep(10);
7 --
8 -- In another session run:
9 --
10 -- begin
11 -- check_record_locked(1);
12 -- check_record_locked(2);
13 -- check_record_locked(3);
14 -- dbms_lock.sleep(10);
15 -- check_record_locked(1);
16 -- check_record_locked(2);
17 -- check_record_locked(3);
18 -- dbms_lock.sleep(10);
19 -- check_record_locked(1);
20 -- check_record_locked(2);
21 -- check_record_locked(3);
22 -- end;
23 -- /
24 --
25 fetch c into l_id;
26 dbms_lock.sleep(10);
27 fetch c into l_id;
28 dbms_lock.sleep(10);
29 close c;
30 end;
31 /

PL/SQL-procedure is geslaagd.


During the first sleep of 10 seconds, the code in the comments is again copied, pasted and executed in a second SQL*Plus session. Note that it prints the situation after just having opened the cursor, after the first fetch and after the second fetch. This is the output:

rwijk@ORA11GR1> begin
2 check_record_locked(1);
3 check_record_locked(2);
4 check_record_locked(3);
5 dbms_lock.sleep(10);
6 check_record_locked(1);
7 check_record_locked(2);
8 check_record_locked(3);
9 dbms_lock.sleep(10);
10 check_record_locked(1);
11 check_record_locked(2);
12 check_record_locked(3);
13 end;
14 /
Record 1 is not locked.
Record 2 is not locked.
Record 3 is not locked.
Record 1 is locked.
Record 2 is not locked.
Record 3 is not locked.
Record 1 is locked.
Record 2 is locked.
Record 3 is not locked.

PL/SQL-procedure is geslaagd.


So opening a cursor with the "for update skip locked" clause doesn't lock a single row. Fetching a row does lock the row, as can be seen in the output. This was the first surprise: I couldn't find text in the documentation telling about this behaviour.

But won't this behaviour lead to a giant "lost update" problem? When you change a record in a second session between the open and the fetch of the cursor - which is allowed because the record is not locked - the cursor could overwrite this change because it has retrieved the value from before the update. Let's check how this works. The setup is a table with bank accounts containing 5 records with an identifying number, an amount and an indicator telling whether interest has been calculated already:

rwijk@ORA11GR1> create table bankaccounts(nr,amount,interest_calculated_indicator)
2 as
3 select level
4 , 100
5 , 'N'
6 from dual
7 connect by level <= 5
8 /

Tabel is aangemaakt.


First a test using the "for update" clause. A piece of code that calculates interest by multiplying the amount of all bankaccounts with the indicator set to 'N' by 5%. During the sleep of 10 seconds, the commented code is copied, pasted and executed in a second SQL*Plus session:

rwijk@ORA11GR1> declare
2 cursor c
3 is
4 select nr
5 , amount
6 from bankaccounts
7 where interest_calculated_indicator = 'N'
8 for update
9 ;
10 type ta_nr is table of bankaccounts.nr%type;
11 type ta_amount is table of bankaccounts.amount%type;
12 a_nr ta_nr;
13 a_amount ta_amount;
14 begin
15 open c;
16 dbms_lock.sleep(10);
17 --
18 -- In another session, try this:
19 --
20 -- update bankaccounts set amount = amount + 100 where nr = 2
21 -- /
22 -- commit
23 -- /
24 -- select * from bankaccounts
25 -- /
26 --
27 loop
28 fetch c bulk collect into a_nr,a_amount limit 2;
29 forall i in 1..a_nr.count
30 update bankaccounts
31 set amount = a_amount(i) * 1.05
32 , interest_calculated_indicator = 'Y'
33 where nr = a_nr(i)
34 ;
35 exit when a_nr.count <> 2;
36 end loop;
37 end;
38 /

PL/SQL-procedure is geslaagd.


The second session has to wait with the update. So all amounts are set to 105.

rwijk@ORA11GR1> select * from bankaccounts
2 /

NR AMOUNT I
---------- ---------- -
1 105 Y
2 105 Y
3 105 Y
4 105 Y
5 105 Y

5 rijen zijn geselecteerd.

rwijk@ORA11GR1> commit
2 /

Commit is voltooid.


After this commit, the second session continues and produces this output:

rwijk@ORA11GR1> update bankaccounts set amount = amount + 100 where nr = 2
2 /

1 rij is bijgewerkt.

rwijk@ORA11GR1> commit
2 /

Commit is voltooid.

rwijk@ORA11GR1> select * from bankaccounts
2 /

NR AMOUNT I
---------- ---------- -
1 105 Y
2 205 Y
3 105 Y
4 105 Y
5 105 Y

5 rijen zijn geselecteerd.


Everything as expected. But now the same code using a "for update skip locked". First restore the situation to its initial state:

rwijk@ORA11GR1> remark  Restore situation
rwijk@ORA11GR1> update bankaccounts
2 set amount = 100
3 , interest_calculated_indicator = 'N'
4 /

5 rijen zijn bijgewerkt.

rwijk@ORA11GR1> commit
2 /

Commit is voltooid.


And execute the same code with a "for update skip locked":

rwijk@ORA11GR1> declare
2 cursor c
3 is
4 select nr
5 , amount
6 from bankaccounts
7 where interest_calculated_indicator = 'N'
8 for update skip locked
9 ;
10 type ta_nr is table of bankaccounts.nr%type;
11 type ta_amount is table of bankaccounts.amount%type;
12 a_nr ta_nr;
13 a_amount ta_amount;
14 begin
15 open c;
16 dbms_lock.sleep(10);
17 --
18 -- In another session, try this:
19 --
20 -- update bankaccounts set amount = amount + 100 where nr = 2
21 -- /
22 -- commit
23 -- /
24 -- select * from bankaccounts
25 -- /
26 --
27 loop
28 fetch c bulk collect into a_nr,a_amount limit 2;
29 forall i in 1..a_nr.count
30 update bankaccounts
31 set amount = a_amount(i) * 1.05
32 , interest_calculated_indicator = 'Y'
33 where nr = a_nr(i)
34 ;
35 exit when a_nr.count <> 2;
36 end loop;
37 end;
38 /

PL/SQL-procedure is geslaagd.


The second session doesn't have to wait, and the immediate response in this second SQL*Plus session is:

rwijk@ORA11GR1> update bankaccounts set amount = amount + 100 where nr = 2
2 /

1 rij is bijgewerkt.

rwijk@ORA11GR1> commit
2 /

Commit is voltooid.

rwijk@ORA11GR1> select * from bankaccounts
2 /

NR AMOUNT I
---------- ---------- -
1 100 N
2 200 N
3 100 N
4 100 N
5 100 N

5 rijen zijn geselecteerd.


Now I thought the output in the first session would show a lost update: the amount in record 2 being 105. But it didn't:

rwijk@ORA11GR1> select * from bankaccounts
2 /

NR AMOUNT I
---------- ---------- -
1 105 Y
2 200 N
3 105 Y
4 105 Y
5 105 Y

5 rijen zijn geselecteerd.


It just didn't process the second row. So a "for update skip locked" doesn't only skip the locked rows, it also skips the rows that were modified between the opening and the fetching, thus avoiding the classic "lost update".

Thanks to James Padfield for hinting at this behaviour.