Every once in a while, progs amaze me by casually offering some off-the-cuff solution to my major testing headaches.

I was trying to recreate a complex user scenario involving precise timing.  I needed a way to make a service hang (blocking other services), sneak some specific test actions through in the meantime, then unhang the service.  After assuming this would be way too complicated for me, my prog offered, “just use a SQL hint to put an exclusive lock on the table”.

A SQL hint is an addition to the query that instructs the database engine to do something extra, overriding its normal decisions.  The tabblock hint, wrapped in a transaction, allows you to put an exclusive lock on a table, preventing other transactions from reading or modifying said table.  I’m using MS SQL but Oracle supports a similar technique.

Here is how it works in a generic test example:

  1. State A exists.
  2. Lock the table:

    UPDATE        tblCustomers
    SET           Name = 'Fred'
    WHERE        (ID = 10)

    Note: The transaction remains open.  The update statement is irrelevant because we are going to roll it back.
  3. Trigger the action you want to hang.  For example: maybe the current UI state is ready for female customers. You trigger a service that returns female customers from tblCustomers to display them on the UI.  Take your time, it won’t complete due to the tablock.
  4. Perform the action you are trying to sneak in.  For example: maybe you  change the UI to expect male customers.
  5. Now State B exists instead of State A.
  6. Unlock the table:


    Note: execute the above statement in the same query session as the query in step 1 was executed.  The action that was hanging in step 3 completes, and in this example, female customers attempt to load into a screen expecting male customers.

So the next time you have a test idea that is too complex to execute in real time, try doing it in bullet time (using a tablock hint to slow things down.)


  1. Adam said...

    That's genius! Thanks for sharing.

    Here's another neat little snipped for you - find all the currently executing queries on the SQL server. Found this while investigating a perf problem last week.


  2. Eric Jacobson said...

    Awesome script, Adam. I just used it. Thanks for passing it along.

Copyright 2006| Blogger Templates by GeckoandFly modified and converted to Blogger Beta by Blogcrowds.
No part of the content or the blog may be reproduced without prior written permission.