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.
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.