To prevent the problems identified in the previous section, the DBMS isolates each transaction. That is to say that it ensures that the interleaving of low level steps cannot happen and that one transaction must be complete before another one can make use of the same data. This is achieved by locking the data that is in used and releasing the locks again once the transaction has been committed or rolled back.
Locks can operate at different levels. For example, when a user starts a transaction, the entire database could be locked until the transaction is complete. However, in a busy environment this would cause a significant decrease in performance while transactions are held in a queue. A less drastic lock would be one which locks only the affected table in the database leaving the others available for other transactions. In real systems such as out e-commerce example, this would still cause performance issues because the transactions would be concentrated on a small number of tables. The effect would therefore be similar to locking the entire database.
A much more realistic approach is to lock individual rows within tables. Row-level locking allows multiple users to perform transactions on the same tables concurrently, and only a small number would actually conflict with each other. The small proportion of conflicts would have a negligible effect on performance.
Of course, the logical extension of row-level locking would be field-level locking where different users can update different fields in the same record concurrently. The benefits of this would be very small, however, and the management overhead would be very large. Field-level locking is therefore rarely used.
A special DBMS process called the scheduler manages the locking process thereby keeping transactions isolated from each other. We can see how locking resolves the problems illustrated earlier by revisiting the same examples.
|4||1||Calculate: new value = 100 - 10||100|
|6||1||Store new value||90|
|7||1||COMMIT (release locks)||90|
|10||2||Calculate: new value = 90 - 50||90|
|11||2||Store new value||40|
|12||2||COMMIT (release locks)||40|
Because the row in the PRODUCT table is locked by transaction 1, transaction 2 is forced to wait until transaction 1 is complete. This ensures that the correct value is stored in the database at the end of both transactions.
|3||1||Calculate: new value = 100 - 10||90|
|4||1||Store new value||90|
|6||2||ROLLBACK (release locks)||100|
|9||2||Calculate: new value = 100 - 50||100|
|10||2||Store new value||50|
|11||2||COMMIT (release locks)||50|
Again, because transaction 2 is obliged to wait, the final value stored in the database is the correct one.