~jk

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:

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     |
+--------------------------+-------+