Different isolation levels may be suitable for different parts of a single transaction. SQL Anywhere allows you to change the isolation level of your database in the middle of a transaction.
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 want to change the isolation level during a transaction to control 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 want to change the isolation level mid-transaction if, for example, just one table or group of tables requires serialized access.
For an example in which the isolation level is changed in the middle of a transaction, see Tutorial: Phantom rows.
You can also set the isolation level (levels 0-3 only) using table hints, but this is an advanced feature that you should use only when needed. For more information, see the WITH table-hint section in FROM clause.
Discuss this page in DocCommentXchange.
|Copyright © 2010, iAnywhere Solutions, Inc. - SQL Anywhere 12.0.0|