MySQL Proxy: COMMIT obfuscator

This is an idea I came across at the MySQL UC last week: How many applications handle failing COMMITs correctly ? And how can we test it ?

COMMITs can fail. The most simple case are deadlocks. The sad side of deadlocks is that they only happen under real load when you application creates concurrency against the same rows. That's usually hard to create in test-setups.

With MySQL Proxy you can create deadlocks easily. Well, more or less.

You can at least fake them nicely and let the application and server think that we have a deadlock. The trick is:

COMMIT obfuscator

To make it a bit more interesting you don't have to let all the COMMITs fail. Just let 50% of them fail.

--[[

Let a random number of transaction rollback

As part of the QA we have to verify that applications handle
rollbacks nicely. To simulate a deadlock we turn client-side COMMITs
into server-side ROLLBACKs and return a ERROR packet to the client
telling it a deadlock happened.

--]]

function read_query(packet)
    if packet:byte() ~= proxy.COM_QUERY then return end

    -- only care about commits
    if packet:sub(2):upper() ~= "COMMIT" then return end

    -- let 50% fail
    if math.random(10) <= 5 then return end

    proxy.queries:append(1, string.char(proxy.COM_QUERY) .. "ROLLBACK")

    return proxy.PROXY_SEND_QUERY
end

function read_query_result(inj)
    if inj.id ~= 1 then return end

    proxy.response = {
            type = proxy.MYSQLD_PACKET_ERR,
            errmsg = "Lock wait timeout exceeded; try restarting transaction",
            errno = 1205,
            sqlstate = "HY000"
    }

    return proxy.PROXY_SEND_RESULT
end

As proof I used a simple InnoDB table, ran two simple transactions against it and let the script do its job:

root@127.0.0.1:4040 [test]> begin;
Query OK, 0 rows affected (0.01 sec)

root@127.0.0.1:4040 [test]> select * from commit where id = 1;
+------+
| id   |
+------+
|    1 |
+------+
1 row in set (0.81 sec)

root@127.0.0.1:4040 [test]> commit;
ERROR 1105 (HY000): Lock wait timeout exceeded; try restarting transaction

2nd round:

root@127.0.0.1:4040 [test]> begin;
Query OK, 0 rows affected (0.00 sec)

root@127.0.0.1:4040 [test]> select * from commit where id = 1;
+------+
| id   |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

root@127.0.0.1:4040 [test]> commit;
Query OK, 0 rows affected (0.00 sec)

The server only saw these statements.

root@127.0.0.1:4040 [test]> SHOW SESSION STATUS LIKE 'com_%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| Com_begin                | 2     |
| Com_commit               | 1     |
| Com_rollback             | 1     |
| Com_select               | 2     |
+--------------------------+-------+

Comments