![]() |
Controlling Cloudscape Application Behavior
|
Reference Manual |
Locking, Concurrency, and IsolationThis section discusses topics pertinent to multi-user systems, in which concurrency is important. Cloudscape is configured by default to work well for multi-user systems. For single-user systems, you may want to tune your system so that it uses fewer resources; see Lock Granularity.
Isolation Levels and ConcurrencyCloudscape provides three transaction isolation levels. Setting the transaction isolation level for a connection allows a user to specify how severely the user's transaction should be isolated from other transactions. For example, it allows you to specify whether transaction A is allowed to make changes to data that have been viewed by transaction B before transaction B has committed. A connection determines its own isolation level, so JDBC provides an application with a way to specify a level of transaction isolation. It specifies five levels of transaction isolation. The higher the transaction isolation, the more care is taken to avoid conflicts; avoiding conflicts sometimes means locking out transactions. Lower isolation levels thus allow greater concurrency. Inserts, updates, and deletes always behave the same no matter what the isolation level is. Only the behavior of select statements varies. JDBC defines the following isolation levels:
NEW: Support for TRANSACTION_REPEATABLE_READ is new in Version 3.6. These levels allow you to avoid particular kinds of transaction anomalies, which are described in Table 6-2 The transaction isolation level is a way of specifying whether these transaction anomalies are allowed. The transaction isolation level thus affects the quantity of data locked by a particular transaction. In addition, a DBMS's locking schema may also affect whether these anomalies are allowed. A DBMS may lock either the entire table or only specific rows in order to prevent transaction anomalies. Table 6-3 shows which anomalies are possible under the various locking schemas and isolation levels. In Cloudscape Version 3.6, the following java.sql.Connection isolation levels are available:
NEW: Support for TRANSACTION_REPEATABLE_READ is new in Version 3.6. Attempting to set isolation to another level results in a "Feature not implemented" (SQLState XJZZZ) SQLException. Cloudscape will offer more isolation level options in future releases. Configuring Isolation LevelsIf a connection does not specify its isolation level, it inherits the default isolation level for the Cloudscape system, which you set with the cloudscape.language.defaultIsolationLevel property. The default value of that property is READ_COMMITTED. When set to READ_COMMITTED, the connection inherits the TRANSACTION_READ_COMMITTED isolation level. When set to SERIALIZABLE, the connection inherits the TRANSACTION_SERIALIZABLE isolation level. To override the inherited default, use the methods of java.sql.Connection. In addition, a connection can change the isolation level of the transaction within an SQL-J statement. For more information, see SET TRANSACTION ISOLATION LEVEL statement of the Cloudscape Reference Manual. The AT ISOLATION clause changes the isolation level for the current statement only. See AT ISOLATION clause of the Cloudscape Reference Manual. With the exception of using the AT ISOLATION clause, changing the isolation level commits the current transaction. NOTE: For information about how to choose a particular isolation level, see Tuning Cloudscape. Lock GranularityCloudscape can be configured for table-level locking. With table-level locking, when a transaction locks data in order to prevent any transaction anomalies, it always locks the entire table, not just those rows being accessed. By default, Cloudscape is configured for row-level locking. Row-level locking uses more memory but allows greater concurrency, which works better in multi-user systems. Table-level locking works best with single-user applications or read-only applications. You typically set lock granularity for the entire Cloudscape system, not for a particular application. However, at compilation time or runtime, Cloudscape may escalate the lock granularity for a particular transaction from row-level locking to table-level locking for performance reasons. You have some control over the threshold at which this occurs. For information on turning off row-level locking, see cloudscape.storage.rowLocking in Tuning Cloudscape. For more information about automatic lock escalation, see About the Optimizer's Selection of Lock Granularity and Transaction-Based Lock Escalation in Tuning Cloudscape. For more information on tuning your Cloudscape system, see Chapter 3, "Tuning Databases and Applications". You can override row-level locking for individual tables. Because row-level locking uses more resources, it is useful to be able to use table-level locking where possible. For example, you may have a table that you know will be read-only after you populate it, or one that will always be accessed by only one user at a time, or you want to ensure that only one user accesses it at a time. You override row-level locking with the SET LOCKING clause when you create or alter a table. For more information, see CREATE TABLE statement in the Cloudscape Reference Manual. Types and Scope of Locks in Cloudscape SystemsExclusive LocksWhen a statement modifies data, its transaction holds an exclusive lock on data that prevents other transactions from accessing the data. This lock remains in place until the transaction holding the lock issues a commit or rollback. Table-level locking lowers concurrency in a multi-user system. Shared LocksWhen a statement reads data without making any modifications, its transaction obtains a shared lock on the data. Another transaction that tries to read the same data is permitted to read, but a transaction that tries to update the data will be prevented from doing so until the shared lock is released. How long this shared lock is held depends on the isolation level of the transaction holding the lock. Transactions using the READ COMMITTED isolation level release the lock when the transaction steps through to the next row. Transactions using the SERIALIZABLE or REPEATABLE_READ isolation level hold the lock until the transaction is committed, so even a SELECT can prevent updates if a commit is never issued. Update LocksWhen a user-defined update cursor (created with the FOR UPDATE clause) reads data, its transaction obtains an update lock on the data. If the user-defined update cursor updates the data, the update lock is converted to an exclusive lock. If the cursor does not update the row, when the transaction steps through to the next row, transactions using the READ COMMITTED isolation level release the lock, and transactions using the SERIALIZABLE or REPEATABLE_READ isolation level downgrade it to a shared lock until the transaction is committed. Update locks help minimize deadlocks. Lock Compatibility
The following table lists compatibility between lock types. + means compatible,
Scope of LocksThe amount of data locked by a statement can vary.
Notes on LockingIn addition to the locks already described, foreign key lookups require briefly held shared locks on the referenced table (row or table, depending on the configuration). The table and examples in this section do not take performance-based lock escalation into account. Remember that the system may choose table-level locking for performance reasons. Bulk Fetch and Row LocksFor SELECTs in a read committed environment, Cloudscape in theory locks a single row at a time--the row being looked at currently by the application. This isn't actually true when bulk fetch is turned on. Bulk fetch is a performance enhancement that means that Cloudscape fetches more than one row at a time from the underlying conglomerate (index or table). By default, Cloudscape fetches 16 rows at a time. When bulk fetch is used, Cloudscape actually locks a different row from the one being looked at. Some applications may need to rely on the current row being locked because they may go on to update the row currently being looked at. The most sensible thing in such a situation is for an application to use an updatable cursor, since bulk fetch is automatically turned off in that situation. Otherwise, applications could do one of the following:
Locking Examples
No examples are provided for table-level locking, because the entire table is always locked. Row-Level Locking, TRANSACTION_READ_COMMITTED
-- Cloudscape locks and releases the rows as
-- (no index on miles)
-- Cloudscape locks but does not release the rows as
-- Cloudscape locks the entire range right away
-- Cloudscape locks the row being inserted until commit
-- Cloudscape locks the range of rows Row-Level Locking, TRANSACTION_SERIALIZABLE and REPEATABLE_READ
-- Cloudscape locks the entire range (most likely
-- (no index on miles)
-- (with an index on miles)
-- Cloudscape locks the entire
-- Cloudscape locks the row being inserted until commit
-- Cloudscape locks the range of rows being deleted DeadlocksIn a database, a deadlock is a situation in which two or more transactions are waiting for one another to give up locks. For example, Transaction A may hold a lock on some rows in the Accounts table and needs to update some rows in the Orders table to finish. Transaction B holds locks on those very rows in the Orders table but needs to update the rows in the Accounts table held by Transaction A. Transaction A cannot complete its transaction because of the lock on Orders. Transaction B cannot complete its transaction because of the lock on Accounts. All activity comes to a halt and remains at a standstill forever unless the DBMS detects the deadlock and aborts one of the transactions.
Avoiding DeadlocksUsing both row-level locking and the TRANSACTION_READ_COMMITTED isolation level makes it likely that you will avoid deadlocks (both settings are Cloudscape defaults). However, deadlocks are still possible. Cloudscape application developers can avoid deadlocks by using consistent application logic; for example, transactions that access Accounts and Orders should always access the tables in the same order. That way, in the scenario described above, Transaction B simply waits for transaction A to release the lock on Orders before it begins. When transaction A releases the lock on Orders, Transaction B can proceed freely. Another tool available to you is the LOCK TABLE statement. A transaction can attempt to lock a table in exclusive mode when it starts to prevent other transactions from getting shared locks on a table. For more information, see LOCK TABLE statement in the Cloudscape Reference Manual. Deadlock DetectionWhen a transaction waits more than a specific amount of time to obtain a lock (called the deadlock timeout), Cloudscape can detect whether the transaction is involved in a deadlock. When Cloudscape analyzes such a situation for deadlocks it tries to determine how many transactions are involved in the deadlock (two or more). Usually aborting one transaction breaks the deadlock. Cloudscape must pick one transaction as the victim and abort that transaction; it picks the transaction that holds the fewest number of locks as the victim, on the assumption that that transaction has performed the least amount of work. (This may not be the case, however; the transaction may have recently been escalated from row-level locking to table locking and thus hold a small number of locks even though it has done the most work.) When Cloudscape aborts the victim transaction, it receives a deadlock error (an SQLException with an SQLState of 40001). The error message gives you the transaction IDs, the statements, and the status of locks involved in a deadlock situation. ERROR 40001: A lock could not be obtained due to a deadlock, For information on configuring when deadlock checking occurs, see Configuring Deadlock Detection and Lock Wait Timeouts. NOTE: Deadlocks are detected only within a single database. Deadlocks across multiple databases are not detected. Non-database deadlocks caused by Java synchronization primitives are not detected by Cloudscape. Lock Wait TimeoutsEven if a transaction is not involved in a deadlock, it may have to wait a considerable amount of time to obtain a lock because of a long-running transaction or transactions holding locks on the tables it needs. In such a situation, you may not want a transaction to wait indefinitely. Instead, you may want the waiting transaction to abort, or time out, after a reasonable amount of time, called a lock wait timeout. (For information about configuring the lock wait timeout, see Configuring Deadlock Detection and Lock Wait Timeouts.) Configuring Deadlock Detection and Lock Wait TimeoutsYou configure the amount of time a transaction waits before Cloudscape does any deadlock checking with the cloudscape.locks.deadlockTimeout property. You configure the amount of time a transaction waits before timing out with the cloudscape.locks.waitTimeout property. When configuring your database or system, you should consider these properties together. For example, in order for any deadlock checking to occur, the cloudscape.locks.deadlockTimeout property must be set to a value lower than the cloudscape.locks.waitTimeout property. If it is set to a value equal to or higher than the cloudscape.locks.waitTimeout, the transaction times out before Cloudscape does any deadlock checking. By default, cloudscape.locks.waitTimeout is set to 180 seconds. -1 is the equivalent of no wait timeout. This means that transactions never time out, although Cloudscape may choose a transaction as a deadlock victim. Figure 6-2, Figure 6-3, and Figure 6-4 show some example configurations. Figure 6-2 One possible configuration: deadlock checking occurs when a transaction has waited 30 seconds; no lock wait timeouts occur. Figure 6-3 Another typical configuration: deadlock checking occurs after a transaction has waited 60 seconds for a lock; after 90 seconds, the transaction times out and is rolled back. Figure 6-4 A configuration in which no deadlock checking occurs: transactions time out after they have waited 50 seconds. No deadlock checking occurs. Debugging DeadlocksIf deadlocks are happening frequently in your multi-user system with a particular application, you may need to do some debugging. Cloudscape provides a VTI class to help you in this situation, COM.cloudscape.vti.LockTable. For information, see the Cloudscape Server and Administration Guide. Programming Applications to Handle DeadlocksWhen you configure your system for deadlock and lockwait timeouts and an application could be chosen as a victim when the transaction times out, you should program your application to handle this. To do this, test for SQLExceptions with SQLStates of 40001 (deadlock timeout) or 40XL1 or 4)XL2 (lockwait timeout). In the case of a deadlock you may want to re-try the transaction that was chosen as a victim. In the case of a lock wait timeout, you probably do not want to do this right away. The following code is one example of how to handle a deadlock timeout. /// if this code may encounter a deadlock, put the whole // thing in a try/catch block // then try again if the deadlock victim exception was thrown try { s.executeUpdate( "UPDATE FlightAvailability " + "SET economy_seats_taken = economy_seats_taken " + "+ 1 WHERE flight_id = 'AA1116'"); s.executeUpdate( "UPDATE HotelAvailability " + "SET rooms_taken = 0 WHERE hotel_id = 9"); } // note: do not catch such exceptions in database-side methods; // catch such exceptions only at the outermost level of // application code. // See Database-Side JDBC Methods and SQLExceptions. catch (SQLException se) { if (se.getSQLState().equals("40001")) { // it was chosen as a victim of a deadlock. // try again at least once at this point. System.out.println( "Will try the transaction again."); s.executeUpdate( "UPDATE FlightAvailability "+ "SET economy_seats_taken = economy_seats_taken " + "+ 1 WHERE flight_id = 'AA1116'"); s.executeUpdate( "UPDATE HotelAvailability "+ "SET rooms_taken = 0 WHERE hotel_id = 9"); } else throw se; } |
||||||||||||||||
|
![]() Cloudscape Version 3.6 For information about Cloudscape technical support, go to: www.cloudscape.com/support/.Copyright © 1998, 1999, 2000 Informix Software, Inc. All rights reserved. |