Continuous Numbering in SQL – Why the Transaction Model Must Be Understood (and Why DBF Is Often Underestimated)
Incrementing Field Values
Post by xxx »
I need to assign consecutive invoice numbers. That means incrementing a table column by 1 each time. What is an elegant way to do this? And in such a way that nobody interferes in between and you don’t get an incorrect number back. With DBF, I lock the record and then release it. But how do you do this in SQL? It’s rather unlikely, but what if two people press the button at the same time… Or is it so fast that the numbers always end up sequential anyway?
The original question nicely illustrates an uncertainty that can be observed in many SQL applications in practice:
“If two people click at the same time – is it so fast that everything will end up nicely in sequence anyway?”
This question is absolutely legitimate – but it also shows that SQL is often still used with a sequential mindset, even though it is a parallel system.
Context
SQL is not a faster DBF, but a system with:
parallel transactions
isolation levels
implicit and explicit locks
concurrency as the normal case
Anyone using SQL must understand this model – otherwise exactly these kinds of questions arise.
A statement that can be made clearly
Anyone willing to engage deeply with SQL, transactions, and concurrency is very well served by SQL.
Anyone who does not want or need to do that is often safer with a well-structured DBF approach.
This is not a value judgment, but a question of complexity versus benefit.
How is this solved correctly in SQL?
3.1 AUTO_INCREMENT / IDENTITY (Best Practice)
The recommended solution is not to calculate the number yourself, but to leave it to the database.
CREATE TABLE rechnung (
id INT AUTO_INCREMENT PRIMARY KEY,
datum DATE,
kunde_id INT
);
On insert:
INSERT INTO rechnung (datum, kunde_id)
VALUES (CURRENT_DATE, 123);
What is important here – and often underestimated – is:
You cannot see in the code how the number is generated
You have to trust the database system that:
the operation is atomic
no duplicate assignment is possible
You can only verify the behavior indirectly (parallel tests, documentation, engine knowledge).
For many developers this feels abstract –
especially for those who like to “touch” their data directly and understand what is happening.
This is exactly where DBF comes back into play
An interesting comparison: DBF + microservice
If DBF is not accessed directly, but via a single-threaded microservice
(one process, no threads), then the following applies:
Requests are processed serially
there is no parallel access in the code
simultaneous clicks are technically not a problem
The flow is logical and visible:
Request A → read → +1 → save → done
Request B → read → +1 → save → done
Completely without explicit locks, because:
no second code path runs at the same time
the microservice itself represents the queue
How do you achieve “single-threaded” technically?
A simple, practical approach:
a single microservice process
event-loop based
no worker threads
one request after the other
On Windows, this is easy to implement, for example via:
WebSockets (asynchronous, event-driven)
the WebSocket stack is part of the operating system
One process = one state = one truth
This automatically gives you:
deterministic behavior
clear ordering
reproducible processes
no need to think about concurrency in application code
Result of this approach
In this scenario, DBF is:
very simple
easy to understand
deterministic
easy to debug
completely sufficient for many SMB applications
And often more robust than SQL code that runs in parallel
but is written without a real understanding of transactions.