User's Guide
   PART 2. Relational Database Concepts
     CHAPTER 15. Using Transactions and Locks       

Understanding and choosing isolation levels

The choice of isolation level depends on the kind of task an application is carrying out. This section gives some guidelines for choosing isolation levels.

When you choose an appropriate isolation level you must balance the need for consistency and accuracy in the information your transaction is using, with the need for concurrent transactions to proceed unimpeded. If a transaction involves only one or two specific values in one table, it is unlikely to interfere as much with other processes as one which searches many large tables and may need to lock many rows or entire tables and may take a very long time to complete.

If your transactions is transferring money between bank accounts or even checking account balances, you will likely want to do your utmost to ensure that the information you return is correct. On the other hand, if just want a rough estimate of the proportion of inactive accounts, then you may not care whether your transaction waits for others or not and indeed may be willing to sacrifice some accuracy to avoid interfering with other users of the database.

Furthermore, a transfer may affect only the two rows which contain the two account balances, whereas all the accounts must be read in order to calculate the estimate. For this reason, the transfer is less likely to delay other transactions.

Adaptive Server Anywhere provides four levels of isolation: levels 0, 1, 2, and 3. The third level provides complete isolation and ensures that transactions are interleaved in such a manner that the schedule is serializable.

     For a discussion of serializable schedules and correctness please refer to section Correctness.

  Tutorial 2 - The non-repeatable read

The example in section Introduction to concurrency demonstrated the first type of inconsistency, namely the dirty read. In that example, an accountant made a calculation while the Sales Manager was in the process of updating a price. The accountant's calculation used erroneous information which the Sales Manager had entered and was in the process of fixing.

The following example demonstrates one type of inconsistency, namely non-repeatable reads. In this example, you will play the role of the same two people, both using the demonstration database concurrently. The Sales Manager wishes to offer a new sales price on plastic visors. The Accountant wishes to verify the prices of some items that appear on a recent order.

This example begins with both connections at isolation level 1, rather than at isolation level 0, which is the default for the demonstration database supplied with Adaptive Server Anywhere. By setting the isolation level to 1, you eliminate the type of inconsistency which the previous tutorial demonstrated, namely the dirty read.

1. Start Interactive SQL.

2. Connect to the asademo database: Select Connect from the Command menu. Enter the user ID DBA and the password SQL. In the Advanced tab, name this connection Sales Manager.

   User ID:	DBA
   Password:	SQL
   Connection Name:	Sales Manager
   Database File:	asademo

3. Start a second copy of Interactive SQL. Again, enter DBA and SQL as the user ID and password, but this time name the connection Accountant. Click the OK button.

   User ID:	DBA
   Password:	SQL
   Connection Name:	Accountant

4. Set the isolation level to 1 for the Accountant's connection by executing the following command.

   SET TEMPORARY OPTION ISOLATION_LEVEL = 1;

5. Set the isolation level to 1 in the Sales Manager's window by executing the following command:

   SET TEMPORARY OPTION ISOLATION_LEVEL = 1;

6. The Accountant decides to list the prices of the visors. In the Accountant's window, execute the following command:

   SELECT id, name, unit_price FROM product

   id	name	unit_price
   300	Tee Shirt	9.00
   301	Tee Shirt	14.00
   302	Tee Shirt	14.00
   400	Baseball Cap	9.00
   401	Baseball Cap	10.00
   500	Visor	7.00
   501	Visor	7.00

7. The Sales Manager decides to introduce a new sale price for the plastic visor. In the Sales Manager's window, execute the following command:

   SELECT id, name, unit_price FROM product
   WHERE name = 'Visor';
   UPDATE product
   SET unit_price = 5.95 WHERE id = 501;
   COMMIT;

   id	name	unit_price
   500	Visor	7.00
   501	Visor	5.95

8. Compare the price of the visor in the Sales Manager's window with the price for the same visor in the Accountant's window. The Accountant's window still shows the old price, even though the Sales Manager has entered the new price and committed the change.

   This inconsistency is called a non-repeatable read, because if the accountant did the same select a second time in the same transaction, he wouldn't get the same results. Try it for yourself. In the Accountant's window execute the select command again. Observe that the Sales Manager's sale price now displays.

   SELECT id, name, price 
   FROM products;

   id	name	unit_price
   300	Tee Shirt	9.00
   301	Tee Shirt	14.00
   302	Tee Shirt	14.00
   400	Baseball Cap	9.00
   401	Baseball Cap	10.00
   500	Visor	7.00
   501	Visor	5.95

   Of course if the accountant had finished his transaction, for example by issuing a COMMIT or ROLLBACK command before using the SELECT again, it would be a different matter. The database is available for simultaneous use by multiple users and it is completely permissible for someone to change values either before or after the accountant's transaction. The change in results is only inconsistent because it happens in the middle of his transaction. Such an event makes the schedule unserializable.

9. The accountant notices this behavior and decides that from now on he doesn't want the prices changing while he looks at them. Repeatable reads are eliminated at isolation level 2. Play the role of the accountant:

   SET TEMPORARY OPTION ISOLATION_LEVEL = 2;
   SELECT id, name, unit_price
   FROM product;

10. The Sales Manager decides that it would be better to delay the sale on the plastic visor until next week so that she won't have to give the lower price on a big order that she's expecting will arrive tomorrow. In his window, try to execute the following statements. The command will start to execute, then his window will appear to freeze.

    UPDATE product
    SET unit_price = 7.00 WHERE id = 501;

    The database server must guarantee repeatable reads at isolation level 2. To do so, it places a read lock on each row of the product table that the accountant reads. When the Sales Manager tries to change the price back, his transaction must acquire a write lock on the plastic visor row of the product table. Since write locks are exclusive, his transaction must wait until the Accountant's transaction releases its read lock.

11. The Accountant is finished looking at the prices. He doesn't want to risk accidentally changing the database, so he completes his transaction with a ROLLBACK statement.

    ROLLBACK;

    Observe that as soon as the database server executes this statement, the Sales Manager's transaction completes.

    id	name	unit_price
    500	Visor	7.00
    501	Visor	7.00

12. The Sales Manager can finish now. She wishes to commit his change to restore the original price.

    COMMIT;

Types of Locks and different isolation levels 

When you upgraded the accountant's isolation from level 1 to level 2, the database server used read locks where none were previously acquired. In general, each isolation level is characterized by the types of locks needed and by how locks held by other transactions are treated.

At isolation level 0, the database server needs only write locks. It makes use of these locks to ensure that no two transactions make modifications which conflict. For example, a level 0 transaction acquires a write lock on a row before it updates or deletes it, and inserts any new rows with a write lock already in place.

Level 0 transactions perform no checks on the rows they are reading. For example, when a level 0 transaction reads a row, it doesn't bother to check what locks may or may not have been acquired on that row by other transactions. Since no checks are needed, level 0 transactions are particularly fast. This speed comes at the expense of consistency. Whenever they read a row which is write locked by another transaction, they risk returning dirty data.

At level 1, transactions check for a write locks before they read a row. Although one more operation is required, these transactions are assured that all the data they read is committed. Try repeating the first tutorial with the isolation level set to 1 instead of 0. You will find that the Accountant's computation cannot proceed while the Sales Manager's transaction, which updates the price of the tee shirts, remains incomplete.

When the Accountant raised his isolation to level 2, the database server began using read locks. From then on, it acquired a read lock for his transaction on each row that matched his selection.

  Changing the isolation level within a transaction

You may not have thought about it, but in doing the above tutorial, you demonstrated another important feature of Adaptive Server Anywhere, namely that the isolation level may be changed in the middle of a transaction. The accountant performed one SELECT, upgraded his isolation to level 2, and then performed a second SELECT.

When you change the ISOLATION_LEVEL option in the middle of a transaction, the new setting affects only the following:

• Any cursors opened after the change

• Any statements executed after the change

You may wish to change the isolation level during a transaction, as doing so affords you control over the number of locks your transaction places. You may find a transaction needs to read a large table, but perform detailed work with only a few of the rows. If an inconsistency would not seriously affect your transaction, set the isolation to a low level while you scan the large table to avoid delaying the work of others.

You may also wish to change the isolation level in mid transaction if, for example, just one table or group of tables requires serialized access.

  Transaction blocking

In step 10 of the above tutorial, the Sales Manager's screen froze during the execution of his UPDATE command. The database server began to execute his command, then found that the Accountant's transaction had acquired a read lock on the row that the Sales Manager needed to change. At this point, the database server simply paused the execution of the UPDATE. Once the Accountant finished his transaction with the ROLLBACK, the database server automatically released his locks. Finding no further obstructions, it then proceeded to complete execution of the Sales Manager's UPDATE.

In general, a locking conflict occurs when one transaction attempts to acquire an exclusive lock on a row on which another transaction holds a lock, or attempts to acquire a shared lock on a row on which another transaction holds an exclusive lock. One transaction must wait for another transaction to complete. The transaction which must wait is said to be blocked by another transaction.

When the database server identifies a locking conflict which prohibits a transaction from proceeding immediately, it can either pause execution of the transaction, or it can terminate the transaction, roll back any changes, and return an error. You control the route by setting the BLOCKING option. When BLOCKING is set to ON, then the second transaction waits as in the above tutorial

     For further information regarding the blocking option, see The BLOCKING option.

  Tutorial 3 - A phantom row

The following continues the same scenario. In this case, the Accountant views the department table while the Sales Manager creates a new department. You will observe the appearance of a phantom row.

If you have not done so, do steps 1 through 4 of the previous tutorial. These steps describe how to open two copies of Interactive SQL.

1. Start two copies of Interactive SQL as in steps 1 through 3 of the previous tutorial. Name one connection Sales Manager. Name the other connection Accountant.

   User ID:	DBA
   Password:	SQL
   Connection Name:	Sales Manager/ Accountant
   Database File:	asademo

2. Set the isolation level to 2 in the Sales Manager's window by executing the following command.

   SET TEMPORARY OPTION ISOLATION_LEVEL = 2;

3. Set the isolation level to 2 for the Accountant's connection by executing the following command.

   SET TEMPORARY OPTION ISOLATION_LEVEL = 2;

4. In the Accountant's window, enter the following command to list all the department.

   SELECT * FROM department
   ORDER BY dept_id;

   dept_id	dept_name	dept_head_id
   100	R & D	501
   200	Sales	902
   300	Finance	1293
   400	Marketing	1576
   500	Shipping	703

5. The Sales Manager decides to set up a new department to focus on the foreign market. Philip Chin, who has number 129, will head the new department.

   INSERT INTO department
      (dept_id, dept_name, dept_head_id)
      VALUES(600, 'Foreign Sales', 129);

   The final command creates the new entry for the new department. It appears as a new row at the bottom of the table in the Sales Manager's window.

6. The Accountant, however, is not aware of the new the new department. At isolation level 2, the database server places locks to ensure that no row changes, but places no locks that stop other transactions from inserting new rows

   The Accountant will only discover the new row if he should execute his select command again. In the Accountant's window, execute the SELECT statement again. You will see the new row appended to the table.

   SELECT * FROM department
   ORDER BY dept_id;

   dept_id	dept_name	dept_head_id
   100	R & D	501
   200	Sales	902
   300	Finance	1293
   400	Marketing	1576
   500	Shipping	703
   600	Foreign Sales	129

   The new row that appears is called a phantom row because, from the Accountant's point of view, it appears like an apparition, seemingly from nowhere. The Accountant is connected at isolation level 2. At that level, the database server acquires locks only on the rows that he is using. Other rows are left untouched and hence their is nothing to prevent the Sales Manager from inserting a new row.

7. The Accountant would prefer to avoid such surprises in future, so he raises the isolation level of his current transaction to level 3. Enter the following commands for the Accountant.

   SET TEMPORARY OPTION ISOLATION_LEVEL = 3;
   SELECT * FROM department
   ORDER BY dept_id;

8. The Sales Manager would like to add a second department to handle sales initiative aimed at large corporate partners. Execute the following command in the Sales Manager's window.

   INSERT INTO department
      (dept_id, dept_name, dept_head_id)
   VALUES(700, 'Major Account Sales', 902);

   The Sales Manager's window will pause during execution. The command is blocked by the Accountant's locks. Click the Stop button to interrupt this entry.

9. To avoid changing the demonstration database that comes with Adaptive Server Anywhere, you should roll back the insertion of the new departments. Execute the following command in the Sales Manager's window:

   ROLLBACK;

When the Accountant raised his isolation to level 3 and again selected all rows in the department table, the database server placed phantom locks on each row in the table, and one extra phantom lock to avoid insertion at the end of the table. When the Sales Manager attempted to insert a new row at the end of the table, it was this final lock that blocked his command.

Notice that the Sales Manager's command was blocked even though the Sales Manager is still connected at isolation level 2. the database server places phantom locks, like read locks, as demanded by the isolation level and statements of each transactions. Once placed, these locks must be respected by all other concurrent transactions.

     Further information on the details of the locking methods employed by Adaptive Server Anywhere is located in How Adaptive Server Anywhere implements locking.

  Tutorial 4 - Practical locking implications

The following continues the same scenario. In this tutorial, the Accountant and the Sales Manager both have tasks that involve the sales order and sales order items tables. The Accountant needs to verify the amounts of the commission checks paid to the sales employees for the sales they made during the month of April 1994. The Sales Manager, notices that a few orders have not been added to the database and wants to add them.

Their work demonstrates phantom locking. When a transaction at isolation level 3 selects rows which match a given criterion, the database server places phantom locks to stop other transactions from inserting rows which would also match. The number of locks placed on your behalf depends both on the search criterion and on the design of your database.

If you have not done so, do steps 1 through 3 of the previous tutorial which describe how to start two copies of Interactive SQL.

1. Start two copies of Interactive SQL as in steps 1 through 3 of the previous tutorial. Name one connection Sales Manager. Name the other connection Accountant.

   User ID:	DBA
   Password:	SQL
   Connection Name:	Sales Manager/ Accountant
   Database File:	asademo

2. Set the isolation level to 2 in the Sales Manager's window by executing the following command.

   SET TEMPORARY OPTION ISOLATION_LEVEL = 2

3. Set the isolation level to 2 for the Accountant's connection by executing the following command.

   SET TEMPORARY OPTION ISOLATION_LEVEL = 2

4. Each month, the sales representatives are paid a commission which is calculated as a percentage of their sales for that month. The Accountant is preparing the commission checks for the month of April 1994. His first task is to calculate the total sales of each representative during this month.

   Enter the following command in the Accountant's window. Prices, sales order information, and employee data are stored in separate tables. Join these tables using the foreign key relationships which link them to combine these necessary pieces of information.

   SELECT emp_id, emp_fname, emp_lname,
      SUM(sales_order_items.quantity * unit_price)
         AS "April sales"
   FROM employee
      KEY JOIN sales_order
      KEY JOIN sales_order_items
      KEY JOIN product
   WHERE '1994-04-01' <= order_date
      AND order_date < '1994-05-01'
   GROUP BY  emp_id, emp_fname, emp_lname

   emp_id	emp_fname	emp_lname	April sales
   129	Philip	Chin	2160.00
   195	Marc	Dill	2568.00
   299	Rollin	Overbey	5760.00
   467	James	Klobucher	3228.00
   667	Mary	Garcia	2712.00
   690	Kathleen	Poitras	2124.00
   856	Samuel	Singer	5076.00
   902	Moira	Kelly	5460.00
   949	Pamela	Savarino	2592.00
   1142	Alison	Clark	2184.00
   1596	Catherine	Pickett	1788.00

5. The Sales Manager notices a big order sold by Philip Chin was not entered into the database. Philip likes to be paid his commission promptly, so the Sales manager enters the missing order, which was placed on April 25.

   In the Sales Manager's window, enter the following commands. The Sales order and the items are entered in separate tables because an one order can contain many items. You should create the entry for the sales order before you add items to it. To maintain referential integrity, the database server won't allow a transaction which adds items to an order which does not exist.

   INSERT into sales_order
   VALUES ( 2653, 174, '1994-04-22', 'r1',
         'Central', 129);
   INSERT into sales_order_items
   VALUES ( 2653, 1,  601, 100, '1994-04-25' );
   COMMIT;

6. The Accountant has no way of knowing that the Sales Manager has just added a new order. Had the new order been entered earlier, it would have been included in the calculation of Philip Chin's April sales.

   In the Accountant's window, calculate the April sales totals again. Use the same command, but observe that Philip Chin's April sales changes to $4560.00.

   emp_id	emp_fname	emp_lname	April sales
   129	Philip	Chin	4560.00
   195	Marc	Dill	2568.00
   299	Rollin	Overbey	5760.00
   467	James	Klobucher	3228.00
   667	Mary	Garcia	2712.00
   690	Kathleen	Poitras	2124.00
   856	Samuel	Singer	5076.00
   902	Moira	Kelly	5460.00
   949	Pamela	Savarino	2592.00
   1142	Alison	Clark	2184.00
   1596	Catherine	Pickett	1788.00

   Imagine that the Accountant now marks all orders placed in April to indicate that commission has been paid. The order that the Sales Manager just entered might be found in the second search and marked as paid, even though it was not included in Philip's total April sales!

7. At isolation level 3, the database server places phantom locks to ensure that no other transactions can add a row which matches the criterion of a search or select.

   First, roll back the insertion of Philip's missing order: Execute the following statement in the Sales Manager's window.

8. In the Accountant's window, execute the following two statements.

   ROLLBACK;
   SET TEMPORARY OPTION ISOLATION_LEVEL = 3;

9. In the Sales Manager's window, execute the following statements to remove the new order.

   DELETE FROM sales_order_items
   WHERE id = 2653;
   DELETE FROM sales_order
   WHERE id = 2653;
   COMMIT;

10. In the Accountant's window, execute same query as before.

    SELECT emp_id, emp_fname, emp_lname,
       SUM(sales_order_items.quantity * unit_price)
          AS "April sales"
    FROM employee
       KEY JOIN sales_order
       KEY JOIN sales_order_items
       KEY JOIN product
    WHERE '1994-04-01' <= order_date
       AND order_date < '1994-05-01'
    GROUP BY  emp_id, emp_fname, emp_lname;

    Because you set the isolation to level 3, the database server will automatically place phantom locks to ensure that the Sales Manager can't insert April order items until the Accountant finishes his transaction.

11. Return to the Sales Manager's window. Again attempt to enter Philip Chin's missing order.

    INSERT INTO sales_order
    VALUES ( 2653, 174, '1994-04-22', 'r1',
          'Central', 129);

    The Sales Manager's window will hang; the operation will not complete. Click Stop to interrupt this entry.

12. The Sales Manager can't enter the order in April, but you would think that she could still enter it in May.

    Change the date of the command to April 05 and try again.

    INSERT INTO sales_order
    VALUES ( 2653, 174, '1994-05-05', 'r1',
          'Central', 129);

    The Sales Manager's window will hang again. Click Stop to interrupt this entry. Although the database server places no more locks than necessary to prevent insertions, these locks have the potential to interfere with a large number of other transactions.

    The database server places locks in table indices. For example, it places a phantom lock in an index so a new row cannot be inserted immediately before it. However, when no suitable index is present, it must lock every row in the table.

    In some situations, phantom locks may block some insertions into a table, yet allow others.

13. The Sales Manager wishes to add a second item to order 2651. Use the following command.

    INSERT INTO sales_order_items
    VALUES ( 2651, 2,  302, 4, '1994-05-22' );

    All goes well, so the Sales Manager decides to add the following item to order 2652 as well.

    INSERT INTO sales_order_items
    VALUES ( 2652, 2,  600, 12, '1994-05-25' );

    The Sales Manager's window will hang. Click Stop to interrupt this entry.

14. Conclude this tutorial by undoing any changes to avoid changing the demonstration database. Enter the following command in the Sales Manager's window.

    ROLLBACK;

    Enter the same command in the Accountant's window.

    ROLLBACK;

    You may now close both windows.

  Reducing the impact of locking

You should avoid running transactions at isolation level 3 whenever practical. They tend to place large number of locks and hence impact the efficient execution of other concurrent transactions.

When the nature of an operation demands that it run at isolation level 3, you can lower its impact on concurrency by designing the query to read as few rows and index entries as possible. These steps will help the level 3 transaction run more quickly and, of possibly greater importance, will reduce the number of locks it places.

In particular, you may find that adding an index may greatly help speed up transactions, particularly when at least one of them must execute at isolation level 3. An index can have two benefits:

• Orders can be located in an efficient manner

• Fewer locks may be needed for searches which make use of the index.

You should design your database with the operations that you wish it to perform in mind. For example, if selections such as that used by the Accountant are to be run at level 3 frequently, you might find it worthwhile to index the order dates.

To execute each of your commands, the database server must decide which information in the database to access and an order in which to retrieve it. These plans are based both on general strategic principles as well as past experience with your database. the database server provides you with information about its plans. With this knowledge, you can more readily anticipate which rows and indexes the database server will need to read and hence which rows a particular statement is likely to lock.

     Further information on the details of the locking methods employed by Adaptive Server Anywhere is located in How Adaptive Server Anywhere implements locking.

     For information on performance and how Adaptive Server Anywhere plans its access of information to execute your commands, refer to Monitoring and Improving Performance.

  Transactions for which no updates are lost

Depending upon what level of locking you demand from the database server, you can encounter other inconsistencies in addition to the three types of inconsistencies introduced above. This section introduces one additional type of inconsistency. It is of particular relevance to you if you make use of cursors within your SQL programs.

Some applications require that no updates be lost. The following example typifies the lost update problem.

Example 

Consider a sequence of instructions which could occur when two people put money into the same account at about the same time.

The initial account balance is $1000, and two people (Alex and Ben, say) are about to deposit money into it. Alex will deposit $2000, while Ben will deposit $100.

1. Alex reads the account balance and finds it to be $1000.

2. Ben reads the account balance and finds it to be $1000.

3. Alex adds $2000 to the present balance of $1000 to calculate the new account balance. He then updates the account balance to reflect his deposit. He writes a new balance of $3000 into the database and completes his transaction.

4. Ben adds $100 to the present account balance, which he has read as $1000, to calculate the new account balance. He then updates the account balance to reflect his deposit. He writes a balance of $1100 into the database and completes his transaction.

5. The final balance recorded after the two deposits is $1100. The $2000 deposit entered by Alex has been lost.

While both transactions are perfectly correct in themselves, the interaction between the two creates an invalid result in the database, and Alex's update is lost.

Adaptive Server Anywhere provides you with several means of eliminating lost updates. You can execute such transactions at either isolation level 2 or 3, which guarantee repeatable reads. The next section describes another option.

  Cursor stability

A cursor can hold the result of a SELECT 

The database server allows you to return the results of a select in the form of a data type called a cursor. A cursor is similar to a table, but has the additional property that one row is identified as the present, or current row. Various commands allow you to navigate through the rows of a cursor. For example the FETCH command retrieves a row from the cursor and identifies it as the current row. You can step through all the rows in a cursor by repeatedly calling this command.

Cursors are of most use when you program procedures, or when you write applications which access a database using embedded SQL. They are not as useful when using Interactive SQL interactively.

The rows in a cursor, like those in a table, have no order associated with them. The FETCH command steps through the rows, but the order may appear random and can even be inconsistent. For this reason, you will frequently wish to add an index and impose an order by appending a ORDERED BY phrase to your SELECT statement.

     For further information on programming SQL procedures and cursors, see Using Procedures, Triggers, and Batches.

The Adaptive Server Anywhere locking mechanism can achieve cursor stability. Cursor stability ensures that no other transactions can modify information which is contained in the present row of your cursor. The information in a row of a cursor may be the copy of information contained in a particular table or may be a combination of data from different rows of multiple tables. More than one table will likely be involved whenever you use a join or sub-selection within a SELECT statement.

The Adaptive Server Anywhere locking scheme assures cursor stability at isolation levels 1 through 3. Cursor stability also eliminates lost updates.

Cursor stability at isolation levels 1, 2, and 3 

If you are writing SQL procedures or writing an application which makes use of embedded SQL, you may wish to take advantage of cursor stability. No row fetched through a cursor yields uncommitted data. No updates made through a cursor will be lost. Adaptive Server Anywhere automatically provides cursor stability at isolation levels 1, 2, and 3.

  Early release of read locks—an exception

A transaction acquires a read lock on each row of a table which is read when you have set the isolation to level 3. Ordinarily, a transaction never releases a lock, once it has acquired it, before the end of the transaction. Indeed, it is essential that a transaction does not release locks early if the schedule is to be serializable.

Adaptive Server Anywhere always retains write locks until a transaction completes. If it were to release a lock sooner, another transaction could modify that row making it impossible to roll back the first transaction.

Read locks are never released either, except under one, special circumstance. Under isolation level 1, transactions acquire a read lock on a row only when it becomes the current row of a cursor. This lock eliminates lost updates by assuring that no other transaction can modify that row. When, however, that row is no longer current, the lock is released. This behavior is acceptable because the database engine does not need to guarantee repeatable reads at isolation level 1.

  Typical transactions at various isolation levels

Various isolation levels lend themselves to particular types of tasks. Use the information below to help you decide which level is best suited to each particular operation.

Typical level 0 transactions 

Transactions that involve browsing or performing data entry may last several minutes, and read a large number of rows. If isolation level 2 or 3 is used, concurrency can suffer. Isolation level of 0 or 1 is typically used for this kind of transaction.

For example, a decision support application that reads large amounts of information from the database to produce statistical summaries may not be significantly affected if it reads a few rows that are later modified. If high isolation is required for such an application, it may acquire read locks on large amounts of data, not allowing other applications write access to it.

Typical level 1 transactions 

Isolation level 1 is particularly useful in conjunction with cursors, because this combination eliminates lost updates without greatly increasing locking requirements. Adaptive Server Anywhere achieves this benefit through the early release of read locks acquired for the present row of a cursor. These locks must persist until the end of the transaction at either levels two or three in order to guarantee repeatable reads.

For example, a transaction which updates inventory levels through a cursor is particularly suited to this level, because each of the adjustments to inventory levels as items are received and sold would not be lost, yet these frequent adjustments would have minimal impact on other transactions.

Typical level 2 transactions 

At isolation level 2, rows which match your criterion cannot be changed by other transactions. You can thus employ this level when you must read rows more than once and rely that rows contained in your first result set won't change.

Because of the relatively large number of read locks required, you should use this isolation level with care. As with level 3 transactions, careful design of your database and indices reduce the number of locks acquired and hence can improve the performance of your database significantly.

Typical level 3 transactions 

Isolation level 3 is appropriate for transactions which demand the most in security. The elimination of phantom rows lets you perform multi-step operations on a set of rows without fear that new rows will appear partway through your operations and corrupt the result.

However much integrity it provides, isolation level 3 demands respect when used on large systems which must support a large number of concurrent transactions. Adaptive Server Anywhere places more locks at this level than at any other, raising the likelihood that one transaction will impede the process of many others. You will thus likely wish to use lower isolation levels wherever possible.

  Improving concurrency at isolation levels 2 and 3

When you must make use of serializable transactions, design your database, in particular the indices, with the business rules of your project in mind. You may also improve performance by breaking large transactions into several smaller ones, thus shortening the length of time that rows are locked.

Although serializable transactions have the most potential to block other transactions, they are not necessarily less efficient. When processing these transactions, Adaptive Server Anywhere can perform certain optimizations which may improve performance, despite the increased number of locks. For example, since all rows read must be locked whether or not they match the a search criteria, the database server is free to combine the operation of reading rows and placing locks.