processing abort of transaction mariadb

Question

Im using mariadb 10.0.20 created two tables with tokudb engine .

table1 structure :
  CREATE TABLE `table1` (
      `id` varchar(28) NOT NULL,
      `tin` varchar(50) DEFAULT NULL,
      `uid` varchar(12) NOT NULL ,
      `process_flag` char(2) DEFAULT NULL,
      `src_db_name` varchar(80),
      `src_tbl` varchar(200) ,
      `sc` varchar(2) DEFAULT NULL,
      PRIMARY KEY (`id`),
      KEY `TIN` (`tin`),  
    ) ENGINE=TokuDB DEFAULT CHARSET=latin1 ROW_FORMAT=FIXED `compression`='tokudb_lzma'

table2 structure:
CREATE TABLE `demo_v12` (  
 `uid` decimal(20,0) NOT NULL,
 `id` varchar(40) NOT NULL,
 PRIMARY KEY (`id`),
) ENGINE=TokuDB DEFAULT CHARSET=latin1 ROW_FORMAT=FIXED `compression`='tokudb_lzma'

I was trying to insert from table2 to table1 using JDBC

for(String table2:table2List){
      try (Connection conn = getConnection();
           Statement cst = conn.createStatement();) {
    String query = "insert ignore into table1 (id,uid) select (id,uid) from "+ table2;
    cst.executeUpdate(q);
    }
}

I m having hundreds of tables like table2 and million of records in each table, when I see the output of show process list at backend some times I'm seeing 'processing abort of transaction, 11264 out of 34752' i didn't understand whether my insertion was completed successfully or not and sometimes it is going into deadlock situation where it is restarting the transaction and failing.

Please let me know the reason for the above error message

Thanks inadvance

Renzo · Accepted Answer · 2015-07-07T07:33:53.820

1

The problem is that your code is problematic, since it does not follow the standard way of using JDBC, and in particular since:

You create a connection inside a loop. Never do this.
You create connections without closing them. Never do this.
You are not using correctly the try ... catch syntax.

Try the following:

try {
    Connection conn = getConnection();
    Statement cst = conn.createStatement();
    for(String table2:table2List){
        String query = "insert ignore into table1 (id,uid) select (id,uid) from "+ table2;
        cst.executeUpdate(q);
    }
    cst.close();
    conn.close();
    } catch(SQLException se) {
        //Handle errors for JDBC
}

Then you should consider if you want only one transaction for the entire program or one transaction for each operation. In the first case, if something fails then nothing is executed, in the second case, if there is a failure for a certain insertion, then only that insertion is not executed. To obtain the different behaviour, you should read the manual a propos of the autocommitproperty of a connection. If you leave your program as it is, autocommit is on and each insertion is a different transaction.

edited Jul 07 '15 at 07:33

answered Jul 07 '15 at 05:40

Renzo

26,848
5
49
61

thanks for your suggestion regarding the coding style but i would like to know what makes my transaction to abort , let it be different transaction for each insertion . – maverick Jul 07 '15 at 05:58
I think the transaction fails because you have too many connections opened, each one with a different transaction still in execution, if the program shown in the question is the real program. And if a single transaction fails, only that insertion is not completed. Did you try the program suggested? – Renzo Jul 07 '15 at 06:12
May be your right the above piece of code is in a runnable class , i'm running it by creating 10 threads each thread creates its own connection .If i see it in mysql> show processlist hardly there are 10-12 open connections this should not hurt mysql much , anyways i'll try to use one Connection object and getback to you with result – maverick Jul 07 '15 at 08:13
Now with single connection it is working fine but what are precautions i need to take to write a multi threaded insert from the same database. – maverick Jul 09 '15 at 09:06
In general the possibility of using the same connection among multiple threads depends on the driver used (see for instance [Is java.sql.Connection thread safe?](http://stackoverflow.com/questions/1531073/is-java-sql-connection-thread-safe)). However you should *not* share statements among different threads. Consider however the possibility of staying on the safe side and use different connections for different threads, but also check if multithreading actually increase the throughput of your application. – Renzo Jul 09 '15 at 17:35

processing abort of transaction mariadb

1 Answers1