I was involved in one of the development support for MySQL. Here is an interesting case
The application needs a Sequence generator as it was not present inbuilt with MySQL ( MariaDB will have it in 10.3 ) they have made sequence generator function with ‘select for update‘ statement. But it tends to cause more locking at high concurrency with their XtraDB Cluster deployment.
To get rid of this , the sequence generation were made as direct Update queries and sequence is fetched using Select @next.
UPDATE EO_PK_TABLE SET PK = (@next := PK …[Read more]