User's Guide
PART 2. Relational Database Concepts
CHAPTER 15. Using Transactions and Locks
Often, the general information about locking provided in the earlier sections will suffice to meet your needs. There are times, however, when you may benefit from more knowledge of what goes on inside Adaptive Server Anywhere when you perform basic types of operations. This knowledge will provide you with a better basis from which to understand and predict potential problems that users of your database may encounter.
The details of locking are best broken into two sections: what happens during a INSERT, UPDATE, DELETE or SELECT and how the various isolation levels affect the placement of read, phantom, and anti-phantom locks.
Although you can control the amount of locking that takes place within the database server by setting the isolation level, there is a good deal of locking that occurs at all levels, even at level 0. These locking operations are fundamental. For example, once one transaction updates a row, no other transaction must can modify the same row before the first transaction completes. Without this precaution, you could not role back the first transaction.
The locking operations which the engine performs at isolation level 0 are the best to learn first exactly because they represent the foundation. The other levels add locking features, but do not remove any present in the lower levels. Thus, moving to higher isolation level adds operations not present in lower levels and never removes any.
Locking during insertsINSERT operations create new rows. Adaptive Server Anywhere employs the following procedure to ensure data integrity.
Make a location in memory to store the new row. The location is initially hidden from the rest of the database, so there is as yet no concern that another transaction could access it.
Fill the new row with any supplied values.
Write lock the new row.
Place an anti-phantom lock in the table to which the row is being added. Recall that anti-phantom locks are exclusive, so once the-anti-phantom lock is acquired, no other transaction can block the insertion by acquiring a phantom lock
Insert the row into the table. Other transactions can now, for the first time, see that the new row exists. They can't modify or delete it, though, because of the write lock acquired earlier.
Update all affected indexes and verify both referential integrity and uniqueness, where appropriate. Verifying referential integrity means ensuring that no foreign key points to a primary key which does not exist. Primary key values must be unique. Other columns may also be defined to contain only unique values, and if any such columns exist, uniqueness is verified.
The transaction can be committed provided referential integrity will not be violated by doing so: record the operation in the transaction log file and release all locks.
Insert other rows as required, if you have selected the cascade option, and fire triggers.
You can ensure that all values in a particular column, or combination of columns, are unique. database server always performs this task by building an index for the unique column, even if you don't ask for an index explicitly.
In particular, all primary key values must be unique. database server automatically builds an index for the primary key of every table. Thus, you don't need to, and indeed shouldn't, explicitly ask database server to index a primary key as you risk asking it to create a redundant index.
A foreign key is a reference to a primary key, usually in another table. When that primary key doesn't exist, the offending foreign key is called an orphan. Adaptive Server Anywhere automatically ensures that your database contains no orphans. This process is referred to as verifying referential integrity. The database server verifies referential integrity by counting orphans.
You can ask the database server to delay verifying referential integrity to the end of your transaction. In this mode, you can insert one row which contains a foreign key, then insert a second row which contains the missing primary key. You must perform both operations in the same transaction. Otherwise, the database server will not allow your operations.
To request that the database server delay referential integrity checks until commit time, set the value of the option WAIT_FOR_COMMIT to ON. By default, this option is OFF. To turn it on, issue the following command:
SET OPTION WAIT_FOR_COMMIT = ON;
Before committing a transaction, the database server verifies that referential integrity is maintained by checking the number of orphans your transaction has created. At the end of every transaction, that number must be zero.
Even if the necessary primary key exists at the time you insert the row, the database server must ensure that it still exists when you commit your results. It does so by placing a read lock on the target row. With the read lock in place, any other transaction is still free to read that row, but none can delete or alter it.
Locking during updatesThe database server modifies the information contained in a particular record using the following procedure.
Write lock the affected row.
If any entries changed are included in an index, delete each index entry corresponding to the old values. Make a record of any orphans created by doing so.
Update each of the affected values.
If indexed values were changed, add new index entries. Verify uniqueness where appropriate and verify referential integrity if a primary of foreign key was changed.
The transaction can be committed provided referential integrity will not be violated by doing so: record the operation in the transaction log file, including the previous values of all entries in the row, and release all locks.
Cascade the insert or delete operations, if you have selected this option and primary or secondary keys are affected.
You may be surprised to see that the deceptively simple operation of changing a value in a table can necessitate a rather large number of operations. The amount of work that the database server needs to do is much less if the value you are changing is not part of a primary or foreign key. It is lower still if it is not contained in an index, either explicitly or implicitly because you have declared that attribute unique.
The operation of verifying referential integrity during an UPDATE operation is no less simple than when the verification is performed during an INSERT. In fact, when you change the value of a primary key, you may create orphans. When you insert the replacement value, the database server must check for orphans once more.
Locking during deletesThe DELETE operation follows almost the same steps as the INSERT operation, except in the opposite order.
Write lock the affected row.
Delete each index entry present for the any values in the row. Immediately prior to deleting each index entry, acquire one or more phantom locks as necessary to prevent another transaction inserting a similar entry before the delete is committed. In order to verify referential integrity, the database server also keeps track of any orphans created as a side effect of the deletion.
Remove the row from the table so that it is no longer visible to other transactions. The row cannot be destroyed until the transaction is committed because doing so would remove the option of rolling back the transaction.
The transaction can be committed provided referential integrity will not be violated by doing so: record the operation in the transaction log file including the values of all entries in the row, release all locks, and destroy the row.
Cascade the delete operation, if you have selected this option and have modified a primary or foreign key.
The database server must ensure that the DELETE operation can be rolled back. It does so in part by acquiring phantom locks. These locks are not exclusive, but deny other transactions the right to insert entries which make it impossible to roll back the DELETE operation. For example, the row deleted may have contained a primary key value for the table, or another unique value. Were another transaction allowed to insert a row with the same value, the DELETE could not be undone without violating the unique property of the primary key or attribute.
Adaptive Server Anywhere enforces uniqueness constraints through indexes. In the case of a simple table with only a one-attribute primary key, a single phantom lock may suffice. Other arrangements can quickly escalate the number of locks required. For example, the table may have no primary key or other index associated with any of the attributes. Since the rows in a table have no fundamental ordering, the only way of preventing inserts may be to phantom lock the entire table.
Deleting a row can mean acquiring a great many locks. You can minimize the effect on concurrency in your database in a number of ways. As described earlier, indexes and primary keys reduce the number of locks required because they impose an ordering on the rows in the table. the database server takes advantage of these orderings. Instead of acquiring locks on every row in the table, it can simply lock the next row. Without the index, the rows have no order and thus the concept of a next row is meaningless.
The database server acquires phantom locks on the row following the row deleted. Should you delete the last row of a table, then it simply places the phantom lock on an invisible end row. In fact, if the table contains no index, the number of phantom locks required is one more than the number of rows in the table.
While one or more phantom lock excludes an anti-phantom lock, and one or more read lock excludes a write lock, no interaction exists between phantom/anti-phantom locks and read/write locks. For example, although a write lock is exclusive and so can not be acquired on a row which contains read locks, it can be acquired on a row which has only a phantom lock. More options are open to the database server because of this flexible arrangement, but it means that the engine must generally take the extra precaution of acquiring a read lock when acquiring a phantom lock. Otherwise, there another transaction could in effect remove the phantom lock by deleting the row on which it was acquired.
Selecting at isolation level 0No locking operations are required when executing a SELECT statement at isolation level 0. Each transaction is not protected from changes introduced by other transactions. It is the responsibility of the programmer or database user to interpret the result of these queries with this limitation in mind.
Selecting at isolation level 1You may be surprised to learn that Adaptive Server Anywhere uses almost no more locks when running a transaction at isolation level 1 than it does at isolation level 0. Indeed, the database server modifies its operation in only two ways.
The first difference in operation has nothing to do with acquiring locks, but rather with respecting them. At isolation level 0, a transaction is free to read any row, whether or not another transaction has acquired a write lock on it. By contrast, before reading each row an isolation level 1 transaction must check whether a write lock is in place. It cannot read past any write-locked rows because doing so might entail reading dirty data.
The second difference in operation creates cursor stability. Cursor stability is achieved by acquiring a read lock on the current row of a cursor. This read lock is released when the cursor is moved. More than one row may be affected if the contents of the cursor is the result of a join. In this case, the database server acquires read locks on all rows which have contributed information to the cursor's current row and removes all these locks as soon as another row of the cursor is selected as current. A read lock placed to ensure cursor stability is the only type of lock that does not persist until the end of a transaction.
Selecting at isolation level 2At isolation level 2, Adaptive Server Anywhere modifies its procedures to ensure that your reads are repeatable. If your SELECT command returns values from every row in a table, then the database server acquires a read lock on each row of a table as it reads it. If, instead, your SELECT contains a WHERE clause, or other condition which restricts the rows to selected, then the database server instead reads each row, tests the values in the row against your criterion, and then acquires a read lock on the row if it meets your criterion.
As at all isolation levels, the locks acquired at level 2 include all those set at levels 1 and 0. Thus, cursor stability is again insured and dirty reads are not permitted.
Selecting at isolation level 3When operating at isolation level 3, Adaptive Server Anywhere is obligated to ensure that all schedules are serializable. In particular, in addition to the requirements imposed at each of the lower levels, it must eliminate phantom rows.
To accommodate this requirement, the database server uses read locks and phantom locks. When you make a selection, the database server acquires a read lock on each row which contributes information to your result set. Doing so ensures that no other transactions can modify that material before you have finished using it.
This requirement is similar to the procedures that the database server engine uses at isolation level 2, but differs in that a lock must be acquired for each row read, whether or not it meets any attached criterion. For example, if you select the names of all employees in the sales department, then the engine must lock all the rows which contain information about a sales person, whether the transaction is executing at isolation level 2 or 3. At isolation level 3, however, it must also acquire read locks on each of the rows of employees which are not in the sales department. Otherwise, someone else accessing the database could potentially transfer another employee to the sales department while you were still using your results.
The fact that a read lock must be acquired on each row whether or not it meets your criterion has two important implications.
The database server may need to place many more locks than would be necessary at isolation level 2.
The database server can operate a little more efficiently: It can immediately acquire a read lock on each row at as it reads it, since the locks must be placed whether or not the information in the row is accepted.
The number of phantom locks the engine places can very greatly and depends upon your criterion and on the indexes available in the table. Suppose you select information about the employee with Employee ID 123. If the employee id is the primary key of the employee table, then the database server can economize its operations. It can use the index, which is automatically built for a primary key, to locate the row efficiently. In addition, there is no danger that another transaction could change another Employee's ID to 123 because primary key values must be unique. The engine can guarantee that no second employee is assigned that ID number simply by acquiring a read lock on only the one row containing information about the employee with that number.
By contrast, the database server would likely have to acquire many more locks were you to, instead, select all the employees in the sales department. Since any number of employees could be added to the department, the engine will likely have to read every row in the employee table and test whether each person is in sales. If this is the case, both read and phantom locks must be acquired for each row.
Special optimizationsThe previous sections describe the locks acquired when all transactions are operating at a given isolation level. For example, when all transactions are running at isolation level 2, locking is performed as described in the appropriate section, above.
In practice, your database is likely to need to process multiple transactions which are at different levels. A few transactions, such as the transfer of money between accounts, must be serializable and so run at isolation level 3. For other operations, such as updating an address or calculating average daily sales, a lower isolation level will often suffice.
While the database server is not processing any transactions at level 3, it optimizes some operations so as to improve performance. In particular, many extra phantom and anti-phantom locks are often necessary to support a level 3 transaction. Under some circumstances, the database server can avoid either placing or checking for some types of locks when no level 3 transactions are present.
For example, the engine uses phantom locks to guard against two distinct types of circumstances:
Ensure that deletes in tables with unique attributes can be rolled back.
Eliminate phantom rows in level 3 transactions.
If no level 3 transactions are using a particular table, then the database server need not place phantom locks in the index of a table which contains no unique attributes. If, however, even one level 3 transaction is present, all transactions, even those at level 0, must place phantom locks so that the level 3 transactions can identify their operations.
Naturally, the database server always attaches notes to a table when it attempts the types of optimizations described above. Should a level 3 transaction suddenly start, you can be confident that the necessary locks will be put in place for it.
You may have little control over the mix of isolation levels in use at one time as so much will depend on the particular operations that the various users of your database wish to perform. Where possible, however, you may wish to select the time that level 3 operations execute because they have the potential to cause significant slowing of database operations. The impact is magnified because the database server is forced to perform extra operations for lower-level operations.