Locking

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.

Lost updates

Step	Transaction	Operation	Stored value
1	1	Obtain locks	100
2	1	Read quantity	100
3	2	Wait	100
4	1	Calculate: new value = 100 - 10	100
5	2	Wait	100
6	1	Store new value	90
7	1	COMMIT (release locks)	90
8	2	Obtain locks
9	2	Read quantity	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.

Uncommitted data

Step	Transaction	Operation	Stored value
1	1	Obtain locks	100
2	1	Read quantity	100
3	1	Calculate: new value = 100 - 10	90
4	1	Store new value	90
5	1	Wait	100
6	2	ROLLBACK (release locks)	100
7	2	Obtain locks	100
8	2	Read quantity	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.