MySQL Forums
Forum List  »  InnoDB

primary key which does not create gap index bottleneck on insert
Posted by: Andrew Nuss
Date: March 03, 2016 07:52AM

I have a lot of tables that currently use an increasing bigint integral number as the primary surrogate key.

I also use read_committed primarily throughout my application.

My reading of the mysql locking for innodb tells me that I may have a problem.

Imagine a lot of processes efficiently getting a new surrogate upcount for use in inserting a row in a particular table with that primary surrogate bigint key. I now believe that since mostly the inserts will be going to the end of the primary key index, there will be a lot of blocking on the same gap indices.

I was thinking it would be better to md5hash(new bitint id) and append the id itself just to be safe on md5 collisions, and use this hash as the primary key. This way, with random string primary keys that are a function of the bigint surrogate id, it is very unlikely with a huge number of rows even, for 2 processes to lock the same "gap" on inserting a new row.

Am I understanding correctly?

Options: ReplyQuote

Written By
primary key which does not create gap index bottleneck on insert
March 03, 2016 07:52AM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.