reliably reproducing db contention

Question

we experience with some regularity contention on a database table, and would like to evaluate a number of different options for resolving this issue.

in order to do so, i need to reproduce in a test case, contention on a table (any table) with repeatable reliability.

the approach i'm considering would be to reverse the semantics of a lock (e.g. java.util.concurrent.locks.ReentrantLock) and to release the lock when writing on the table begins, allowing all reads to occur at the time when the writing begins.

So therefore one writer thread holds the lock until shortly before doing an insert to a table, and then releasing the lock, multiple reader threads would attempt to run select statements against the same table.

Was wondering if there were any thoughts on such an approach, or if there is a simpler approach that could, with 100% reliability, reproduce contention on a db table.

thanks

Answer 1

You could use a CountDownLatch with a count of 1.

final CountDownLatch barrier = new CountDownLatch(1);

You launch all of the reader threads, whose first action is

barrier.await();

then the writer thread can

barrier.countDown();

at which point all of the readers will merrily fire away.

Answer 2

It depends a lot on your database as to how easy it is to produce contention in it. For example, if you are using Oracle, then doing a select will never produce any contention.

The easiest way to produce contention in the database is to do a select for read on the row that you know you are about to need to update.

Edit: After re-reading the question, I see that you seem to care more about "reader" contention on the database than update contention. The above idea can be used to force update contention, but not reader contention.

In the case, where you want to launch a mass number of readers to flood the database with selects, which should cause no actual contention, only starvation, then you can use the CountDownLatch as mentioned in another answer, or do it the old fashioned way with Object.wait/Object.notifyAll() if you are forced to run in a pre-1.5 JVM.

Edit 2: After reading the comment, probably the easiest way to emulate the contention that you are seeing is to use the Sybase lock table command. Just lock the table, fire up the selects, and then unlock the table. The selects should then all fire off... this also has the advantage of most accurately emulating the situation that you are trying to model.