collision with auto_increment & triggers across threads

[Chris Cheshire]

DB : MySQL 5.5.23, InnoDB (Amazon RDS installation)

I Have a DB with separate tables for payments, credits and charges, with a master transaction table to keep them all in correct order between them all. This was recently moved from Oracle where I used one sequence to generate the transaction ids across all the payments, credits, charges tables.

For MySQL, I have had to change this around since auto_increments work slightly differently.

I have an auto_increment field on my transaction master table, and a trigger to generate the transaction ID based upon that on a before insert. These transaction ids are then the PK in the charge, credit & payment tables.

+----------------+---------------------+------+-----+---------+----------------+
| Field          | Type                | Null | Key | Default | Extra          |
+----------------+---------------------+------+-----+---------+----------------+
| iid            | bigint(20)          | NO   | PRI | NULL    | auto_increment |
| member_id      | varchar(20)         | NO   | MUL | NULL    |                |
| transaction_id | varchar(20)         | NO   |     | NULL    |                |
| timestamp      | datetime            | NO   | PRI | NULL    |                |
| currency       | tinyint(3) unsigned | NO   |     | NULL    |                |
+----------------+---------------------+------+-----+---------+----------------+

(the timestamp field is part of the primary key as it is partitioned by date)

create trigger trg_i_usa_transactions
before insert on usa_transactions
for each row
begin
  if length(new.transaction_id) = 4 then
    set new.transaction_id = (
        select concat(new.transaction_id, lpad(cast(auto_increment as char), 12, '0'))
        from information_schema.tables
        where table_schema = database() and table_name = 'usa_transactions'
    );
  end if;
end$$

Now, I have stored procedures for creating the payments, charges and credits that, after some sanity and business rule checks, generates an id via :

        insert into usa_transactions (
            member_id, transaction_id, timestamp, currency
        )
        values (
            memberid, 'USAG', timestamp, 1
        );

        select t.transaction_id 
        into chargeid
        from usa_transactions t
        where t.iid = last_insert_id();

Each transaction type has their own prefix (legacy system, that's how it was designed, not important etc).

So, theoretically, for every row of the usa_transactions table, the generated transaction id should have the auto_increment value as part of it.

Unfortunately, that is not happening if 2 different threads try to do an insert into the usa_transactions table at the same time.

+----------+---------------+------------------+---------------------+----------+
| iid      | member_id     | transaction_id   | timestamp           | currency |
+----------+---------------+------------------+---------------------+----------+
| 97988555 | USAC000054506 | USAG000097988555 | 2012-08-24 00:53:00 |        1 |
| 97988556 | USAC000054506 | USAD000097988556 | 2012-08-24 00:53:00 |        0 |
| 97988557 | USAC000057181 | USAG000097988557 | 2012-08-24 00:53:03 |        1 |
| 97988558 | USAC000012043 | USAD000097988557 | 2012-08-24 00:53:05 |        1 |
| 97988559 | USAC000012043 | USAG000097988559 | 2012-08-24 00:53:05 |        1 |
| 97988560 | USAC000057181 | USAD000097988559 | 2012-08-24 00:53:03 |        0 |
+----------+---------------+------------------+---------------------+----------+

Rows 1, 2, 3, 5 are correct, Row 4 has the transaction_id based off the previously inserted row. Row 6 has similar behaviour.

The transactions were being created by two different processes on the same machine, neither sharing connections. One was a single-threaded standalone java app, the second was running under tomcat. Both have autocommit turned off.

I have tested this quite a bit. When only one thread from anywhere is creating the transactions, they line up from iid --> transaction_id. When another thread using a completely new connection starts to create its own transactions at the same time, that's when the problems occur. I was even able to duplicate it by doing the same inserts via a mysql client and bypassing the stored procedures.

Is there something wrong with the trigger code or the way I am getting the generated record in the stored procedure? Is it a bug in this version of the DB engine?

Help

Thanks

Chris