techblog 

We recently hit a problem caused by a database row lock. The actual update being made was not particularly important, as it was just a denormalized value pre-calculated for convenience. However, the consequences of the lock were rather nasty, with many processes waiting for the lock to be released. As a result I decided to change the code to allow the update to be deferred. So if the update encountered a lock, it would write its change to a queue table instead, to be applied later. I did this by opening a cursor with FOR UPDATE NOWAIT. This will throw an exception if it can’t get an immediate lock on the relevant rows.

So far so good, but how do you test something like this? The code in question has been around quite a while and is in PL/SQL (these days most new code we write is in Java). This means we use utPLSQL to test this functionality and so the tests themselves are also written in PL/SQL. The answer is the use of autonomous transactions. These allow you to mark a PL/SQL program unit with a special marker PRAGMA AUTONOMOUS_TRANSACTION that makes it run in its own transaction. So if the code we are testing is in the Make_The_Update procedure of the MyPackage package, the test code would look something like this:

.....

    --This Procedure does its thing in another
    --transaction to force an error to occur
    PROCEDURE Update_Externally IS
      PRAGMA AUTONOMOUS_TRANSACTION;
    BEGIN
       MyPackage.Make_The_Update(1000);
       COMMIT;
    END;

    PROCEDURE ut_check_locking IS
    BEGIN

      --Make an update in the first transaction, but don't commit
      MyPackage.Make_The_Update(10);
      --Make an update in the second transaction
      Update_Externally;
      --Check that it wrote to the queue table here
      ......

    END;

.....

For those unfamiliar with utPLSQL, test procedures are named ut_*. The other procedure is just there for convenience. I have left out much of the boilerplate, such as the setup and teardown code and so on.

So let’s look at what it does. The test procedure ut_check_locking makes an update, but doesn’t commit, then calls the other procedure to attempt an update in a different transaction. (Note that if our code wasn’t designed to deal with this situation, this would be a surefire way to generate a deadlock. Luckily Oracle detects these quite quickly, so if you make a mistake you’ll just get an exception after a few seconds). Finally, we check if the relevant rows have been written to our queue table. Doing this in utPLSQL is left as an exercise for the reader.

I initially had some problems with this, because I forgot that any test data needs to be visible from both transactions. Initially the main transaction just created it and then rolled it back at the end. This gave me some odd ORA-01410: Invalid ROWID errors as I was trying to update non-existent rows. Then I realised that I needed to explicitly commit this data in the setup.

I’ve used autonomous transactions in production code for doing things such as logging when you want the main transaction to roll back. But I’ve not seen them used as an aid to test issues around concurrent data access. But they are certainly helpful in that situation too.