Simulate table locking problems

Question

I have inherited a system built on a MySQL database using InnoDB tables. The system has a bug that shows up under heavy load. I have created some jmeter tests to load up the system hoping to see the bug in a controlled environment. However, I am not stressing the system correctly and the bug is never showing. The current theory is that heavily locked tables are causing a rollback, leaving the user's data in an odd state. This likely means that the transactions are not structured correctly and I want to find and fix that, but I need to find the problem first.

I surmise that if I can create various controlled "table locking loads" in the database I can then run my simulated users on the whole system and force the bug to occur or prove the theory wrong, but I'm not sure how to create such a thing. Does anyone have an idea for how best to do this? At this point I'm not even sure what a poor first version would look like, so any ideas to get me started would help. Thanks!

Answer 1

If you are specifically looking for a locked state for a row in a table (i assume this is what you mean unless you are performing alterations to the table itself on the fly). You can implement this by having a second script initiate a transaction on a set of rows and then pause for some period of time before rolling back or committing the transaction.

Lets say you have this table structure for example:

CREATE TABLE `allkindsofvalues` (
  `value1` int(11) NOT NULL,
  `value2` int(11) NOT NULL,
  `irrelevant_value3` int(11) NOT NULL,
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Open up a MySQL prompt and initiate a transaction:

BEGIN;

UPDATE allkindsofvalues SET irrelevant_value3 = '32143234232';

Now fire up your application. At this point any attempts to use these rows will await for the initiated transaction to complete.

The moment you COMMIT or ROLLBACK the transaction in the MySQL prompt, things should continue normally, but until that point the rows will be in a locked state, unavailable for access. Which it sounds like is the condition you are attempting to replicate.