Search code examples
databasemariadblast-insert-idcolumnstore

MariaDB Columnstore LAST_INSERT_ID() alternative

Environment:

OS: CentOS 7.2

DB server: 10.1.23-MariaDB Columnstore 1.0.9-1

2 test databases, one InnoDB and one Columnstore:

CREATE TABLE `test_innodb` (
    `ctlid` bigint(20) NOT NULL AUTO_INCREMENT,
    `rfid` varchar(100) DEFAULT NULL,
    PRIMARY KEY (`ctlid`)
) ENGINE=InnoDB

CREATE TABLE `test_cs` (
    `ctlid` bigint(20) DEFAULT NULL COMMENT 'autoincrement=1',
    `rfid` varchar(100) DEFAULT NULL
) ENGINE=Columnstore

The problem:

I run several inserts into the InnoDB table:

insert into test_innodb (rfid) values ('a1');
...
insert into test_innodb (rfid) values ('aX');

When I want to get the last inserted id I run this:

select last_insert_id();

and the result properly shows the last ctlid value that has been inserted during the current session no matter whether there are other concurrent sessions that run inserts into that InnoDB table and trigger the creation of additional ctlid values. So far so good..

Now, I perform several inserts into the Columnstore table:

insert into test_cs (rfid) values ('a1');
...
insert into test_cs (rfid) values ('aX');

and I want to achieve the same behavior as the one above, but unfortunately this is ignored by Columnstore:

select last_insert_id();

I used the following alternatives:

-- this will return the next value
select nextvalue from calpontsys.syscolumn cs where cs.schema='my_test_database' and cs.tablename='test_cs' and cs.columnname='ctlid';

- this will return the last inserted id
select callastinsertid('test_cs');

But both display a major limitation: if other concurrent sessions run inserts, then the results of the two queries above are affected by the autoincrements values generated by those inserts. Basically I might not get the expected last inserted id, but a bigger one in case that other sessions created autoincrement values in parallel.

I also tried to:

  • lock the table

  • perform the insert

  • get the last insert id using select callastinsertid('test_cs')

  • unlock the table afterwards

But it looks like locking tables is not supported by Columnstore.

Is there any possibility to achieve a consistent last inserted id (per session) with Columnstore?

Our plan is to switch some of our features from MariaDB/MySQL to Columnstore but the limitation above is pretty blocking.

Solution

  • For high-speed insertion, insert into a separate table, then periodically copy the data from that table into the real table. By using this extra table, you have easier control over normalization, and other things that might need AUTO_INCREMENT values.

    And be sure to do the 'copy' in a single thread, not multiple threads.

    Here is a discussion of many of the details. Some adaptation is needed for ColumnStore, but I think it will work for you. http://mysql.rjweb.org/doc.php/staging_table

    Note the use of ping-ponging two tables. This allow for continuous ingestion while copying into the ColumnStore in parallel.