0

I was looking at my design for primary keys which is based on my own incrementing BIGINT key generator. It is designed to generate batches of 50 numbers for use on that process, but mostly it ends up generating surrogate keys in monotonic increasing order.

Therefore I am worried with inserts from lots of different users to the same table for that primary surrogate key being used, since the inserts will all be using numbers near each other at a given point in time, might there be enough gap lock contention to slow down the insert?

I have two workarounds in mind. One is to use an md5 hash of the BIGINT number as the actual primary key. This way, all concurrent inserts are likely to lock different gaps. The other solution is that even though for programming simplicity, it would be best to use the the new BIGINT given by my sequence generator as the surrogate id, instead use a primary key tuple (user_id, surrogate_int_id).

Also, if there secondary indices on the table do inserts take gap locks on all of these as well?

  • 3
    And why don't you use the provided AUTO_INCREMENT generator? And how many inserts (rows) per second are we talking about? – ypercubeᵀᴹ May 16 '16 at 17:05
  • One batch INSERT? Or 50 single-row inserts? – Rick James May 26 '16 at 2:51
  • Let's see the secondary keys. – Rick James May 26 '16 at 2:53
  • This is a general question about table id design. Based on the answer, I will continue to use integer ids that are clustered in value, and some tables will do multi-inserts and some do single inserts. However, the table that has a secondary unique constraint has both the integer "id" and a unique constraint that is "parentId, id". I am using READ_COMMITTED exclusively, so am wondering about the gap lock on insert with both the primary id, and the unique constraint, can there be deadlocks on the insert statements due to the unique constraint in combination with the primary key? – Andy May 30 '16 at 20:01
2

One INSERT per transaction, containing 50 rows, with "consecutive" (or "nearby") PRIMARY KEY values will be optimal, regardless of one or many connections.

Using MD5, UUID, etc, is a disaster for huge tables. When the table is too big to be fully cached in RAM, the inserts become I/O bound. In contrast, 50 'adjacent' records will cause almost no I/O.

A secondary index that is a MD5 or UUID is just as bad as the PRIMARY KEY being such. This is because a secondary key is a BTree, just like the PRIMARY KEY (and the data) is a BTree. (OK, the secondary key is not quite as bad, because it is probably smaller.)

If you have multiple connections doing inserts, plan on having occasional deadlocks. Deal with them by re-executing the batch INSERT.

As for "gap locks", first let's get the big picture figured out. That is, deal with the advice I have given you, and provide a SHOW CREATE TABLE.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.