For solving concurrency problems in multiuser transaction processing in SQL, there is a process known as versioning is used. In this process, two or more copies of rows of a database are created. One copy has the old data which is before the update and the other copy has the new data after the update, that is different versions of data are created. Which version should be displayed to a particular user is decided by the database management system.
To understand versioning, refer to the below given diagram. When Transaction 1 reads ITEMS table a copy is created and Transaction 1 reads that there are 300 items available. Now, Transaction 2 updates the ITEMS table and places 100 items for the order and decreases the new ITEMS table copy from 300 to 200. From here, on Transaction 2 will see a new copy of the ITEMS table. After that, Transaction 3 reads the ITEMS table to see that there are 300 items available. For Transaction 3 old copy will be displayed as the Transaction 2 has not committed the transaction. Now, Transaction 2 commits the update and items are reduced to 200. After that, Transaction 4 reads the ITEMS table to see the new copy of 200 available items.
Advantages of Versioning:
1) Many transactions can be run in parallel by using versioning.
2) For an enterprise database versioning is the perfect process for multiuser transaction processing.
Disadvantages of versioning:
1) It increases the processing overhead hwne it creates multiple copies.
2) Memory requirements are more in versioning.