Dealing with Database Concurrency Conflicts in the Real World

lot of articles have been written about database concurrency conflict detection and the various ways of handling them.

Unfortunately most of these articles, and accompanying solutions, have one major flaw in that they focus on the technical issues and database implementation instead of real-world data and how people use the data. In this article, I will try to show the difference between focusing on the database implementation and on the real-world data. I will show some possible approaches on how to solve these concurrency issues.

What Is a Database Concurrency Conflict?
Let's start with a quick recap of what database concurrency conflicts are and why you need to solve them in the first place.

Most database applications in this world are multi-user applications. This means that, at any given point in time, you can expect multiple persons and/or processes reading from and writing to a database. Given that multiple persons/processes are updating data in a database, it is only a matter of time before two separate persons/processes will try to update the same piece of data simultaneously. A typical update cycle consists of a sequence of actions:

• Read data into memory
• Update data in memory
• Write data back to the database

Therefore, there will be occasions where two users will both read the same data into memory. User 1 will update the data and write those changes back to the database before user 2 does the same thing. Now you have a concurrency control conflict, because user 1 read the data before user 2 wrote it back to the database. Why? Because if user 2 writes his data back into the database he will overwrite the changes made by user 1, causing them to be lost. Basically, whoever saves their changes last wins, overwriting the changes made by whoever saves first.

This kind of database concurrency issue can occur both with humans or automated processes or a combination of the two. A concurrency issue is more likely to occur with human users because the read/update/write cycle is likely to take much longer. However, that said, the same concurrency issue can occur between automated processes—and then it's harder to solve because in the case of an update by a human you can ask what the user wants (do they want to overwrite changes made by another user?) and respond to that, while a process needs to have all actions fully automated.