A number of different mechanisms exist to keep the level of concurrency high and still prevent problems. In this section, we discuss the mechanism that has been implemented in most SQL products: locking.
The basic principle of locking is simple. If a user accesses a certain piece of data, such as a row from the PLAYERS table, the row will be locked and other users will not be able to access that row. Only the user who has locked the row can access it. Locks are released when the transaction ends. In other words, the life of a lock is never longer than that of the transaction in which the lock is created.
Let us see what will happen with two of the problems discussed in the previous section. For the problem of the lost update (see Figure), user U1 accesses penalty number 4 first. SQL automatically places a lock on that row. Then user U2 tries to do the same. This user, however, gets a message indicating that the row is not available. He must wait until U1 has finished. This means that the final penalty amount will be $105 (work it out for yourself). In this case, the transactions of U1 and U2 are processed not in parallel, but serially. Other users who do not work with penalty number 4, but with another number, are processed concurrently.
For the problem of the nonrepeatable read (see Figure), we now have a comparable situation. Only after U1 has printed the labels can user U2 change the address, which will no longer cause problems.
A locking mechanism works correctly if it meets the serializability criterion. This means that a mechanism works correctly if the contents of the database after (concurrently) processing a set of transactions are the same as the contents of the database after processing the same set of transactions serially (order is irrelevant). The state of the database after problem 1 is such that the penalty amount of penalty number 4 is $80. You will never manage to get the same amount by processing the two transactions of U1 and U2 serially. Whether you execute U1's transaction first and then U2's, or vice versa, the result will be $105, not $80.
Where does the database keep track of all those locks? This lock administration is kept in internal memory of the computer. Usually, a large part of the internal memory is reserved for this. This space is called the buffer. Therefore, locks are not stored in the database. We also mention, probably unnecessarily, that users do not see locks.
We stated that the transactions of users U1 and U2 are processed serially after locks have been placed. This is not ideal, of course. To increase the level of concurrency, most products support two types of locks: share and exclusive. (Sometimes, these locks are called read and write, respectively.) If a user has a share lock on a row, other users can read that row but cannot change it. The advantage is that users who only execute SELECT statements in their transactions do not hold each other up. If a user has an exclusive lock, other users cannot reach the row at all, even to read it. In the previous sections, we have assumed that each lock was an exclusive lock.
No separate SQL statement exists to indicate that you want to work with share locks, for example. SQL determines this itself. The type of lock is derived from the SQL statement. For example, if a SELECT statement is executed, a share lock is implemented. On the other hand, when you use an UPDATE statement, an exclusive lock is set.