Data Locking.

The three possible problems just described force the DBMS to implement some kind of mechanism that prevents the updates of multiple users from interfering with each other and from corrupting the data in the database. The DBMS must make sure that the data in the database is consistent throughout each transaction and that it is unaffected by transient changes made by other concurrently running transactions. The DBMS does this by not allowing concurrent transactions to access the same rows of data at the same time. Once a transaction accesses a row in the database, the DBMS doesn't allow any other transaction to modify that row (they can only read it). This is done through a technique called locking and is applied automatically by the DBMS. It is totally transparent to the SQL user.

TRANSACTION 3 (Order Processing) ---

READ STOCK VALUE SET STOCK = STOCK-10 WRITE STOCK VALUE READ STOCK VALUE READ QTY VALUE WRITE STOCK VALUE

lues. The DBMS might execute these transaction statements in the following order:

4 SET STOCK = STOCK-10 (row2) Order Proc. (3) 5 WRITE STOCK VALUE (row2) Order Proc. (3)

9 SUM=SUM+STOCK (row3) Total Stok. (4) 10 READ STOCK (row3) Order Proc. (3) 13 WRITE STOCK VALUE (row3) Order Proc. (3)

Figure 9.5

SET STOCK = STOCK+QTY

TRANSACTION 4 (Total Stock) ---

SUM(STOCK) FOR ALL ITEMS

Two programs run these transactions at about the same time. Transaction 3 is a part of the new order processing program and transaction 2 is a part of a report generation program that calculates the sum of the STOCK row va

TIME STATEMENT TRANSACTION No. ---- --- --- 0 SUM=0; Total Stok. (4) 1 READ STOCK (row1) Total Stok. (4) 2 SUM=SUM+STOCK (row1) Total Stok. (4) 3 READ STOCK (row2) Order Proc. (3) 6 READ STOCK (row2) Total Stok. (4) 7 SUM=SUM+STOCK (row2) Total Stok. (4) 8 READ STOCK (row3) Total Stok. (4) 11 READ QTY VALUE Order Proc. (3) 12 SET STOCK = STOCK+QTY (row3) Order Proc. (3)

Transaction 4 reads the value of STOCK (row2), at time=6, after transaction 3 has subtracted 10 from it. At time=8, transaction 4 reads STOCK (row3) before transaction 3 subtracts QTY. The sum of the STOCK field values will thus be off by the value of QTY.

There are two basic types of locks that are used by most SQL DBMS. The share lock and the exclusive lock. Share locks allow multiple transactions to access the data that the lock is applied to but do not allow transactions to modify it. Share locks can be applied by more than one transaction to the same data. The second type of basic lock is the exclusive lock. Exclusive locks can only be applied by one transaction at a time, and prevent all other users from locking the same data. Exclusive locks are applied when transactions want to update data in the database and share locks are applied when transactions want to read the data. The rules for applying share and exclusive locks is shown in Figure 9.6. Transaction 1 a s LOCK a c EXCLUSIVE yes no no o

Fi

NO LOCK SHARE LOCK EXCLUSIVE LOCK T NO LOCK yes yes yes

r

n SHARE yes yes no

t LOCK i n 2

gure 9.6

When you access rows of data through a transaction, the DBMS prevents other users from modifying those rows while your transaction is still running. So if you run a SELECT that accesses lots of rows from a table, no other user will be able to change the values of those rows while your transaction is processing. This is why you should keep your transactions as short as possible to maximize concurrent transaction activity in the database.

Although locking prevents the problems associated with concurrent transactions which we have described ie. lost update problem, temporary update problem etc, they introduce another potential problem called a deadlock. This is illustrated in Figure 9.7. Transaction 1 updates the STUDENTS table first then updates the EXAMS table. Transaction 2 does the same thing, but the other way round. If the transactions are executed by the DBMS as shown, transaction 1 updates a part of the STUDENTS table and locks the part of it that it accesses. Transaction 2 then updates the EXAMS table and locks part of that. Now each transaction is trying to update part of the table that has been locked by the other transaction. The transactions are deadlocked. Such deadlocks

can also occur between three or more transactions and without external intervention, each transaction will wait forever.

The DBMS handles deadlocks by periodically checking for them. If a deadlock is detected, one of the transactions is arbitrarily chosen as the deadlock loser and is rolled back thereby releasing the deadlock. This means that any transaction could be rolled back by the DBMS at any time because it resulted in a deadlock with another transaction. In interactive SQL, this is not much of a problem. All it means is that you will have to re-enter the whole transaction again.

TRANSACTION 1 (T1) ---

x-lock = Exclusive lock. s-lock = Share lock.

The DBMS runs these two transactions as:

2 UPDATE EXAMS ROW 7 T1 none, Transaction Waits. UPDATE STUDENTS ROW 3

UPDATE EXAMS ROW 7 TRANSACTION 2 (T2) ---

UPDATE EXAMS ROW 7 UPDATE STUDENTS ROW 3

TIME STATEMENT TRANSACTION LOCK APPLIED

---- --- --- --- 0 UPDATE STUDENTS ROW 3 T1 x-lock on row3 students. 1 UPDATE EXAMS ROW 7 T2 x-lock on row7 exams. 3 UPDATE STUDENTS ROW 3 T2 none, Transaction Waits.

Transaction 1 is waiting for the row locked by transaction 2 to be released and transaction 2 is waiting for the row locked by transaction 1 to be released. They will both wait forever, ie. they are in a deadlock.