Upserts with version awareness in a relational database - Part Three: TiDB

Insert or Update, like MariaDB - but different

One of the earlier posts in this series covered how to implement an upsert with MariaDB or MySQL. TiDB is described as being "highly compatible" with the MySQL protocol, but my simple table definition surfaced up one of the differences.

The table definition that worked for MariaDB was:

CREATE TABLE event (id UUID PRIMARY KEY, 
name varchar(255) NOT NULL,
version bigint NOT NULL DEFAULT 0) 

TiDB does not support UUID as a built in data type, so we have to adapt the setup to involve a less direct representation. Initially I went with a varchar text representation, but then found a suggestion to apply a binary type instead, leaving a table creation statement as follows.

CREATE TABLE event (id BINARY(16) PRIMARY KEY,
name varchar(255) NOT NULL,
version bigint NOT NULL DEFAULT 0)

The corresponding statement for inserting data also involved some adjustment, to enable the value passed in to be converted to the correct type.

INSERT INTO event (id, name, version) VALUES (UUID_TO_BIN(?), ?, ?)
ON DUPLICATE KEY UPDATE
    name = IF(version < VALUES(version), VALUES(name), name),
    version = IF(version < VALUES(version), VALUES(version), version)

Likewise, when retrieving data back out from the database we can also apply a conversion to simplify the handling as a String.

SELECT BIN_TO_UUID(id) AS uuidId, name, version from event

Different isolation levels

Another difference between TiDB and the MariaDB / MySQL implementations is that TIDB does not support the "read uncommitted" isolation level. If you try to specify it you will trigger an error.

https://docs.pingcap.com/tidb/stable/transaction-isolation-levels/

Show me some code...

The code involved for this exploration can be found in one of my public repositories on GitHub. 

https://github.com/Sounie/tidb-with-jdbc