On Tue, 19 Jul 2022 at 18:50, Sebastien Flaesch <sebastien.flaesch@xxxxxxx> wrote: >> If that's the behavior you want, you can build it out of standard SQL facilities (e.g. update a one-row table). > Can you elaborate please? > Do you mean the code should use an UPDATE on a one-row table to acquire a lock? That's the usual SQL way. You can, using the appropiate isolation level, select the max() from the table using the IDs ( but this will probably lock it whole ), you can use a one-row table with a current int column as a sequence, and set current=current+1, but this will lock everyone inserting ( and need an extra table ). You can use a name-current unique sequences table and update that ( it saves tables but it can worsen locking, as many engines locks whole pages ). The consecutive gapless sequence problem surfaces a lot, but there is no good way to solve it without locking, as once you get the value everybody else needing it has to wait until you decide whether to commit on rollback. Sequences are for when you need autogenerated keys. They use consecutive integer blocks nearly everywhere because they are easy to code, play well with btree indexes, correlate well with insertion times, but they give up the "no hole" approach to improve performance. They even can return values unordered in different sessions ( sequences acquire, and burn, blocks in a locking way, when you need high concurrency you make them return big blocks, but them values from different sessions can be delivered out of order ). If you need correlative numbers, byte the bullet and code it, trying to do it with sequences will probably end with an approach which will fail in some corner cases and be slower than just coding it with a single row table ( just remember to check it, so you do not have the fun which ensues in oracle when someone puts two rows in dual in the old days ). All the queries you have posted are probably more complex than a couple procedures to update and read a table, even using eval to simulate named sequences if you want ( just remember to always pre-increment before reading, there is a reason everybody does it that way ). As a remark, in Spain bill numbers need to be gapless increasing. I have done it with a sequence ( with cache 1, so I doubt it is much more performant than a single row table, as it has a backing element which needs to be saved ), and just used another process which swept the values and filled the gap a posteriori ( there is a way to fill a gap, has it legal uses, you can use it for that as long as you do not have too many, basically insert cancelled bills in the gaps ). I probably would do it with the one-row table now for simplicity, I doubt I gained enough performance to justify the 20-something perl lines plus the crontab entry to use sequences. As beard turns grey I apreciate more the simpler ways to do things. Francisco Olarte.
