Skip to main content

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 



 


Labels:
Location: Christchurch, New Zealand

Comments

Post a Comment

Popular posts from this blog

Speeding up Software Builds for Continuous Integration

Downloading the Internet Can you remember the last time you started out on a clean development environment and ran the build of some software using Maven or Gradle for dependency management? It takes ages to download all of the necessary third party libraries from one or more remote repositories, leading to expression like, "Just waiting for Maven to download the Internet". Once your development environment has been used for building a few projects the range of dependencies that will need to be downloaded for other builds reduces down as the previously referenced onces will now be cached and found locally on your computer's hard drive. What happens on the Continuous Integration environment? Now consider what goes on when Jenkins or your other preferred Continuous Integration server comes to build your software. If it doesn't have a local copy of the libraries that have been referenced then it is going to pay the cost of that slow " download the Internet" p...
Post a Comment
Read more

2022 - A year in review

Just a look back over the last 12 months. January I moved back to Christchurch to live, after having spent a few months further south since moving back from London. Work was mainly around balancing other peoples' understanding and expectations around our use of Kafka. February I decided that it would be worthwhile to have a year's subscription for streaming Sky Sports, as some rugby matches that I would want to watch would be on at time when venues wouldn't be open. Having moved to Christchurch to be close to an office, now found myself working from home as Covid restrictions came back into effect across New Zealand. March Got back into some actual coding at work - as opposed to mainly reviewing pull requests for configuration changes for Kafka topics.  This became urgent, as the command line interface tool that our provisioning system was dependent on had been marked for deprecation. April   Had my first direct experience with Covid-19.  I only went for a test because ...
Post a Comment
Read more

Designing systems - The "ity"s That Limit or Enable Profitability

Introduction This started off as a little aide-mémoire to get my head into the right space for preparing for an interview. It's not an exhaustive list, and twists terminology that has been used to represent other things (see:  to Velocity), so don't treat it as a text book reference to work from. Most of the listed points can be associated back to so called "non-functional requirements" - NFRs. I don't like that particular terminology, so alternatively we might consider them as dimensions of the quality of the sytem. Usability "If you build it, they will come" should come with a provisor, "... but if it's awkward to use they'll soon go away, and might not come back." Security All of the aspects that combine to protect data from being seen or manipulated by anyone other than the intended recipient or sender, and also assuring users that the data has originated from the intended source. Velocity Here I'm cheating a bit by trying t...
Post a Comment
Read more