MySQL Forums
Forum List  »  Triggers

collision with auto_increment & triggers across threads
Posted by: Chris Cheshire
Date: August 23, 2012 08:27PM

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

Options: ReplyQuote


Subject
Views
Written By
Posted
collision with auto_increment & triggers across threads
4566
August 23, 2012 08:27PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.