This is part 2 of a multi-part blog series on locking aspects of assertions. In the first part we’ve discussed why there’s a need to enforce a serial execution order on transactions that involve the same assertion. In this second part we’ll explain that locking is only required in very specific cases, i.e. when very specific DML statements are executed in your transaction. For that we’ll need to introduce the concept of a potentially violating DML statement with respect to an assertion.
Let’s continue with the same assertion that we introduced in part 1, which mandates that a purchase order must not have both an open order line and a cancellation event.
CREATE ASSERTION purchase_order_1 check
(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')
);Now ask yourself: what type of DML statements can potentially violate the assertion, and require that we validate the assertion after execution of such statement? For now, focus on INSERT and DELETE only.
Clearly DELETEs require no effort at all: if the database state currently produces no rows for the query under the NOT EXISTS, then removing rows – from any of the three tables involved – can never make the query suddenly produce rows, which would introduce a violation of the assertion. In this case, only INSERTs can potentially violate the assertion, as they could make the query start producing rows. And to do so, these INSERTs need to be very specific too:
- INSERT of a cancellation event
- INSERT of an open order line
- INSERT of a purchase order
All other INSERTs cannot violate the assertion. We call the above three INSERTs “potentially violating” with respect to our assertion. Why potentially? Because it depends on the contents of all three tables involved whether the INSERT does or does not violate the assertion. There is a need to validate the change that was made by the INSERT.
In this database design ORDERS is the parent table, so you may say that the 3rd one (inserting a purchase order) isn’t potentially violating, as the foreign keys from the other two child-tables to the ORDERS table would prevent joinable child-rows to already be there. And you would have a point, given the existence of (not deferred) foreign keys, inserting a purchase order would not have to be classified as potentially violating. However it would require us to track assertion-constraint dependencies, which we currently do not, and so the 3rd one is also considered potentially violating.
Next we’ll focus on UPDATEs. Potentially violating UPDATEs can be deduced straight from the INSERTs.
- UPDATE of a non-cancellation event to a cancellation event
- UPDATE of a non-open order line to an open order line
- UPDATE of a non-purchase order to a purchase order
And there are a few more by considering changes to ORDER#, the join-column in these tables:
- UPDATE of ORDER# of an open order line (the new order could be a canceled purchase order)
- UPDATE of ORDER# of a cancellation event (the new order could be a purchase order with an open order line)
- UPDATE of ORDER# of a purchase order (the new order could have cancellation event and an open order line)
Only the above six types of UPDATEs are potentially violating, all other UPDATEs are not.
At the end of DML statement execution we first determine whether your INSERT or UPDATE was potentially violating with respect to the assertion. This is done by inspecting the rows that were affected by the statement. Assertion locking is only required when a potentially violating DML statement has just been executed. Because only in those cases do we need to query other data to revalidate the assertion and the result set of this query must remain stable till the end of our transaction.
Typically in your transactional load, many DML statements can (will?) be not potentially violating with respect to the assertion and thus will not require assertion locks to be acquired.
Now what type of locks should be acquired to enforce a serial execution order on potentially violating DML statements? Row-level locks don’t help: since how can we prevent another transaction to execute a potentially violating insert? Those rows aren’t there yet. Should we then use table-level locks and lock tables in share mode, row-share mode, or exclusive mode? Ideally we should not as these types of table locks would prohibit many other DML-statements that do not affect the assertion from being able to proceed.
Assertion locks should only block other potentially violating DML statements with respect to the same assertion and allow all other DML statements to proceed. How exactly this is done will be discussed in the next few installments of this blog series.