During a recent technical meeting, one of my colleagues raised a concern about two concurrent inserts for the same object from different processes hitting the system at once. After explaining this to him, I realized that even among experienced engineers, the inner workings of the database are often treated as a black box. There is a persistent uncertainty about what the database guarantees and what it leaves up to developers. ...
Problem description In developing database systems, generating IDs is a crucial task. IDs ensure the uniqueness of data, facilitate queries, and establish relationship constraints in databases. Most modern database management systems (DBMS) can generate auto-increment IDs. We can delegate this task to the DBMS entirely and not worry about the uniqueness. However, there are several reasons why we shouldn’t use auto-increment IDs, especially for distributed systems. The most important reason is that in distributed systems with independent servers, using per-server auto-increment IDs does not guarantee uniqueness and can lead to duplication problems. ...