Database – How to prevent two users from registering at the same instant with same username

There are two elements to this problem: #1, the technical change to the existing code base to support multi-threading, and #2 changing the culture of the company in order to be aware of thread safety in the code.

The first is relatively simple. First, document the functions in the class that are thread safe and are not thread safe. I agree with Jeffery Thomas that you should treat it as a major new feature. Often, I'll write an entirely new class with some (relation to the original class to keep it DRY) that is thread safe. While there is no explicit "thread safe" tag in c++, I find making the class const correct can do a lot to help out. You should also clearly document what is thread safe or not (follow a convention, be CONSISTENT, and make the assumed state not thread safe).

The larger problem is changing the culture of the company to make sure everyone is aware of thread safety. I find this is where things like a style guide and code reviews can be very helpful. A style guide can inform people exactly how to mark things as thread safe. We do a lot of C programming, so I'm partial to decorating names, but you should choose a style that works for you. Some companies lean towards making everything thread safe by default, but I find that tends to add a lot of unnecessary code complexity, and can add non-insignificant overhead on occasion.

Code reviews, when done correctly, are a way to educate everyone on the style and to pass around information about programming in general.

It happens quite often, though the history (sometimes known as audit records) is kept either in the same table, or in a separate one in the same database.

For example, I used to work with a system where any updates to a table would be implemented as an insert, the old 'current' record would have a flag set on it saying it was a historical record, and the timestamp when it was updated written to a column.

Today I work on a system where every change is written to a dedicated audit table and then the update occurs on the table.

The latter is more scalable, but not as easy to implement in a generic manner.

The easiest way to achieve your goal of making queries simple and not requiring adding the 'is current' flag is to only allow read queries via a view or stored procedure. Then you make a call to say "get all players" and the stored proc will return only current players (you can implement a 2nd procedure to return players with more control over which ones are returned). This works well for writing too. A stored procedure for updating a player can then write whatever history details are needed and update the player - without the client ever knowing what the history mechanism is. For this reason, stored procedures are better than a view which returns only current players, as it keeps the entire DB access mechanism the same for reading and writing - everything goes through a sproc.