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

Answer 1

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

1. You create a connection inside a loop. Never do this.
2. You create connections without closing them. Never do this.
3. 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.