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:
   
   begin TRANSACTION
   UPDATE        tblCustomers
   WITH (TABLOCK)
   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:
   
   ROLLBACK TRANSACTION
   
   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.)