Part 2: Potentially Violating DML Statements
This is part 3 of a multi-part blog series on locking aspects of assertions. In the first two parts we’ve established why there is a need to enforce a serial execution order on (only) potentially violating DML statements with respect to an assertion. In this fourth part we’ll refine this further, demonstrate how AN enqueues are used to implement this, and explain some of the terminology we’re using.
Let’s continue with the same assertion about purchase orders (PO) that we introduced in part 1.
CREATE ASSERTION purchase_order_1
(NOT EXISTS
(SELECT 'Open order line in canceled purchase order'
FROM ORDERS o,
ORDER_LINES ol,
ORDER_EVENT_HISTORY oeh
WHERE o.ORDER# = ol.ORDER#
AND o.ORDER# = oeh.ORDER#
AND o.TYPE = 'PURCHASE'
AND ol.STATUS = 'OPEN'
AND oeh.EVENT = 'CANCELED')
);Translating this SQL directly into natural language we would say: “There cannot be a purchase order which has both an open order line and a cancellation event.”
Another way of saying this would be: “Every purchase order must either not have an open order line or not have a cancellation event (or not have both).” The assertion can be viewed as an iterated logic AND of the assertion predicate over all order numbers. In formal logic theory this is called a universal quantification over the set of order numbers. Assume there are currently 100 purchase orders in the ORDERS table (with order numbers 1..100), then the assertion would be equivalent to:
“PO 1 must either not have an open order line, or not have a cancellation event”
AND
“PO 2 must either not have an open order line, or not have a cancellation event”
AND
…
AND
“PO 100 must either not have an open order line, or not have a cancellation event”
Viewing the assertion as an iterated logic AND, also reveals that a potentially violating DML statement that involves a certain order number can only affect the predicate of that order number. What this means is that the revalidation query that we need to run, needs to only revalidate that particular order number, as all other predicates in above iterated logic AND cannot have been affected by the DML statement.
Now we can further refine the need for a serial execution order of two potentially violating DML statements for this assertion: only if such statements involve the same order number, should they run in a serial execution order. Internally we call the set over which universal quantification takes place, the set of quantified values (qv’s), and we’re going to use enqueues to implement “qv-level” locking.
As mentioned in the previous part of this blog series, we’re not going to use row or table level locks. Instead we’ll use enqueues to enforce a serial execution order on potentially violating DML statements for the same assertion. Enqueues are shared memory structures that can be locked, and as such used to serialize operations of different database sessions. An enqueue is of a certain type. For the assertions project we’ve introduced a new enqueue type, the AssertioN (AN) enqueue. Within a type an enqueue is identified by two numeric values: ID1 and ID2. What this means is that two enqueues are considered different, if their ID1 or ID2 value differs.
There are various lock-modes that can be acquired on an enqueue: Null, SS, SX, S, SSX, and X. When a session holds “held” (see below), an attempt to get “get” by another session does the following:
Note the special (symmetric) behavior of the SX and S modes: for both, an enqueue can be held in that mode by multiple sessions at the same time. But once an enqueue is held in SX-mode by any session, then no other session can get that enqueue in S-mode, and vice-versa.
To illustrate what enqueues are taken in what modes, let’s look at a concrete example of a potentially violating DML statement for our assertion. Suppose we execute this insert statement which introduces the first open order line for a (not yet canceled) purchase order and requires us to revalidate order 10:
INSERT INTO ORDER_LINES(ORDER#,STATUS) values(10,’OPEN’);The insert executes successfully: enqueues will have been taken and the revalidation query has run. In this case three enqueues will have been taken. Why three? Because we get an enqueue per referenced table in the assertion. The meaning of each enqueue is loosely speaking “lock ORDER# value 10 in this table”. Remember we’re not locking any rows or tables here, we’re just “grabbing” enqueues in certain modes to represent this “locking of value 10 in that table”. I now need to specify two things:
- What are the ID1 and ID2 values of each of these three enqueues that we introduce?
- In what mode do we acquire each of these three enqueues?
The ID1 value for AN enqueues will always be the USER_ASSERTIONS.OBJECT_ID value of the assertion for which we’re serializing DML statements. The ID2 value is determined by hashing a string which involves the table-name, the (join) column-name and join-value 10. The mode will be: SX for the enqueue representing the table that the DML statement operated on, and S for the other two enqueues. So in this case we end up with three enqueues representing:
- ORDER# value 10 in ORDER_LINES acquired in SX-mode
- ORDER# value 10 in ORDERS acquired in S-mode
- ORDER# value 10 in ORDER_EVENT_HISTORY acquired in S-mode
Using the script included at the bottom of this post, we can query v$enqueue_locks to see these enqueues:
SESSION SA TYPE ID1 ID2 P2_TXT LMOD REQU CTIME
---------------- ------------------ ---- ---------- ---------- --------------- ---- ---- ------
AW@23ai_246268 PURCHASE_ORDER_1 AN 85926 0 DDL_LCK S 42
PURCHASE_ORDER_1 AN 85926 1076219119 _QV_LCK S 42
PURCHASE_ORDER_1 AN 85926 2224531680 _QV_LCK S 42
PURCHASE_ORDER_1 AN 85926 3368851146 _QV_LCK SX 42Note there are actually four enqueues taken now. The first enqueue represents a generic DDL lock, it prevents other sessions from performing DDL on the assertion. The 2nd and 3rd enqueues represent the quantified values (qv) locks of value 10 in tables ORDERS and ORDER_EVENT_HISTORY, they are held in S-mode. And the last enqueue represents the qv lock of value 10 in the ORDER_LINES table, held in SX-mode.
Now what happens if another session tries to cancel this purchase order while our open order line has not yet been committed.
INSERT INTO ORDER_EVENT_HISTORY(ORDER#,EVENT) values(10,’CANCELED’);This DML should block and wait for the other transaction to finish. In this session we also try to acquire three qv-level enqueues representing value 10 in each table, but now the mode will be S for tables ORDERS and ORDER_LINES and SX for table ORDER_EVENT_HISTORY. This results in a blocking situation:
SESSION SA TYPE ID1 ID2 P2_TXT LMOD REQU CTIME
---------------- ------------------ ---- ---------- ---------- --------------- ---- ---- ------
AW@23ai_246268 PURCHASE_ORDER_1 AN 85926 0 DDL_LCK S 1129
PURCHASE_ORDER_1 AN 85926 1076219119 _QV_LCK S 1129
PURCHASE_ORDER_1 AN 85926 2224531680 _QV_LCK S 1129
PURCHASE_ORDER_1 AN 85926 3368851146 _QV_LCK SX 1129
AW@23ai_247409 PURCHASE_ORDER_1 AN 85926 0 DDL_LCK S 3
PURCHASE_ORDER_1 AN 85926 1076219119 _QV_LCK SX 3Our second session’s transaction is blocked while requesting the qv lock for table ORDER_EVENT_HISTORY in SX mode, since it’s already held in S mode by the first transaction. When the first transaction is committed, our second session will proceed and detect an assertion violation for ORDER# 10.
You may be asking why we’re using the S and SX modes, and not the X mode to get these qv locks. This is because we can refine the need to enforce a serial execution order on potentially violating DML statements even further: not only when they involve the same quantified value, but also only when they operate on different tables, is there a need to serialize them. Two statements operating on the same table affecting the same ORDER# need not be serialized, they can safely run concurrently (the validation query really only requires the other two tables to remain stable for order 10). By using the S and SX modes we enable concurrent potentially violating DML against the same table for the same qv.
Here’s the enqueues taken by two sessions, each having inserted an open order line which is not yet committed:
SESSION SA TYPE ID1 ID2 P2_TXT LMOD REQU CTIME
---------------- ------------------ ---- ---------- ---------- --------------- ---- ---- ------
AW@23ai_246268 PURCHASE_ORDER_1 AN 85926 0 DDL_LCK S 2592
PURCHASE_ORDER_1 AN 85926 1076219119 _QV_LCK S 2592
PURCHASE_ORDER_1 AN 85926 2224531680 _QV_LCK S 2592
PURCHASE_ORDER_1 AN 85926 3368851146 _QV_LCK SX 2592
AW@23ai_247409 PURCHASE_ORDER_1 AN 85926 0 DDL_LCK S 23
PURCHASE_ORDER_1 AN 85926 1076219119 _QV_LCK S 23
PURCHASE_ORDER_1 AN 85926 2224531680 _QV_LCK S 23
PURCHASE_ORDER_1 AN 85926 3368851146 _QV_LCK SX 23As you can see from the ID2 values, both sessions hold the exact same three qv enqueues.
This first example has demonstrated that using AN enqueues enable us to very specifically target when DML statements are serialized to ensure correct validation of assertions while transactions execute concurrently. We really only enforce a serial execution order when it’s absolutely necessary: many other DML statements on these three tables can still proceed while all this is going on.
Enqueue overview script:
select *
from (select substr(s.module,1,18) "SESSION"
,substr(o.object_name,1,30) SA
,l.type ,l.ID1, l.ID2
,decode(l.ID2, 0, 'DDL_LCK',
NVL((select 'NOD_LCK '||LOCK_PARAM2_TXT
from sys.assertlock$
where ASSERTOBJ#=l.ID1
and LOCK_PARAM2=l.ID2
and rownum <2
union all
select 'ESC_LCK '||LOCK_PARAM2_TXT
from sys.assertlock$
where ASSERTOBJ#=l.ID1
and to_number(rawtohex(utl_raw.bit_xor(hextoraw
(to_char(LOCK_PARAM2,'FMXXXXXXXX'))
,hextoraw('F005BA11'))), 'XXXXXXXX') = l.ID2
and rownum <2), '_QV_LCK')) p2_txt
,CASE lmode WHEN 0 THEN ' '
WHEN 1 THEN 'NULL'
WHEN 2 THEN 'SS '
WHEN 3 THEN 'SX '
WHEN 4 THEN 'S '
WHEN 5 THEN 'SSX '
WHEN 6 THEN 'X '
ELSE 'UNK ' END as lmode
,CASE request WHEN 0 THEN ' '
WHEN 1 THEN 'NULL'
WHEN 2 THEN 'SS '
WHEN 3 THEN 'SX '
WHEN 4 THEN 'S '
WHEN 5 THEN 'SSX '
WHEN 6 THEN 'X '
ELSE 'UNK ' END AS request
,CTIME
from v$enqueue_lock l , v$session s, dba_objects o
where l.TYPE='AN'
and l.sid = s.sid (+)
and l.id1 = o.object_id (+)
order by 1, 2, 5)
where p2_txt not like 'ESC_LCK%';