Issue:"Unsuccessful execution ... unavailable resource" and isolation levels (WebSphere)
Symptom
This issue applies to sites using WebSphere as the application server, and either DB2 or Microsoft SQL Server as their database.
A site running Process Commander Version 5.x saw the number of locks required by their DB2 database increase exponentially.
In DB2, the maxlocks parameter limits the number of locks the database can use. The site’s system hit this limit and displayed a database exception:
com.pega.pegarules.pub.database.DatabaseException: There was a problem getting a list: code: -904 SQLState: 57011 Message: UNSUCCESSFUL EXECUTION CAUSED BY AN UNAVAILABLE RESOURCE. REASON 00C90096, TYPE OF RESOURCE 00000304, AND RESOURCE NAME PRP1DB .SDACCTM1.X'00F626' '.X'08'
Another symptom of this issue is frequent database deadlocks. Example error:
SQLState 57033, Error code -913: com.ibm.db2.jcc.c.SqlException: UNSUCCESSFUL EXECUTION CAUSED BY DEADLOCK OR TIMEOUT. REASON CODE 00C90088, TYPE OF RESOURCE 00000200, AND RESOURCE NAME PRP1DB .PCASSW1
In addition, your DBA might report a very high number of locks in the database (although not a database exception), or many contending queries (though not yet a deadlock).
Solution
EXPLANATION
Process Commander Version 5.x behaves differently than Version 4.x due to requirements of conforming to the Java EE (formerly J2EE) specification.
The Java EE CMT (Container-Managed Transaction) specifications do not allow applications to directly modify connection settings such as the isolation level. Therefore, Process Commander leverages the application server containers for connection management: all connection definition information is in the application server, including the transaction isolation level. (For further information on transaction isolation levels, see the Additional Details section below.)
Although the DB2 database defaults to the correct transaction isolation level — READ_COMMITTED
— WebSphere automatically defaults its isolation level to REPEATABLE_READ
, which overrides the correct level in the database. This level causes a great many more locks to be used, which can lead to the errors described above. (READ_COMMITTED
is the JDBC term for this correct level; in DB2, this level is called Cursor Stability.)
WORKAROUND
When the problems described above occur, it is easy to overlook the transaction isolation level, because the required custom property is not included in a WebSphere configuration by default.
To resolve this error, it is necessary to add a new custom property to the datasource defined for Process Commander in your WebSphere application server setup: webSphereDefaultIsolationLevel.
IMPORTANT UPDATE
IBM has published PK54755: ‘WebSphereDefaultIsolationLevel’ custom property is not picked up when using the WebSphere Embedded ConnectJDBC Provider
http://www-1.ibm.com/support/docview.wss?rs=180&uid=swg1PK54755
This article explains:
When the 'webSphereDefaultIsolationLevel' property is configured for the WebSphere embedded ConnectJDBC Provider, the connection manager does not pick up and use the property. All SQL statements are executed using the default isolation level.
The code was fixed so the default value that is set in the helper class for isolation level will be used.The fix for this APAR is currently targeted for inclusion in fix packs 6.0.2.27 and 6.1.0.15.
If you are using the ConnectJDBC Provider, and you wish to apply one of these fixpacks, they will not work with PRPC version 5.4; you must be running Version 5.4 SP1. There were other issues with these fixpacks – see CPRBootstrap Error initializing PRAppLoader message when starting Process Commander for details.
To add the webSphereDefaultIsolationLevel custom property:
1. Open your datasource. (In the left navigation area of the WebSphere Admin Console, expand Resources, then JDBC. Choose your datasource.)
2. On the right side of the window, choose Custom Properties.
3. The Custom Properties screen appears. At the top left of this window, click New.
4. A blank Configuration form appears.
5. Not all of the fields on this form require changes. Leave the defaults in the Scope and Type fields.
For the rest of the fields, enter the following information:
Field Name | Description | Value To Enter |
---|---|---|
Name | The name of the custom property. NOTE: This must be the exact text shown in the Value column, in order for the system to use this property. | webSphereDefaultIsolationLevel |
Value | A numerical value for this property. | 2 |
Description | A description of the custom property. NOTE: This is just a text field; the description does not have to be exactly as shown. | “Set transaction isolation level to READ_COMMITTED.” |
6. Click OK.
ADDITIONAL DETAILS
The transaction isolation level for a database connection specifies the degree of database locking that is used when transactions are committed. JDBC specifies four levels of transaction isolation:
JDBC Transaction Isolation Level | Description |
---|---|
SERIALIZABLE | This isolation level is the most restrictive, prohibiting:
|
REPEATABLE_READ | This isolation level is the next most restrictive. Phantom reads are allowed, but the following are prohibited:
|
READ_COMMITTED | This isolation level prohibits only dirty reads. Non-repeatable reads and phantom reads are allowed. |
READ_UNCOMMITTED | This level does not prohibit any reads, and is the least restrictive. |
NOTE: Various software products may call these levels by different names. For example, in order of descending strictness, DB2’s isolation levels are:
- Repeatable Read (maps to
SERIALIZABLE
) - Read Stability (maps to
REPEATABLE_READ
) - Cursor Stability
- Uncommitted Read
The higher the “transaction isolation,” the more care is required to avoid conflicts, because the database is enforcing stricter policies. Different types of conflicts can occur, including:
- “dirty” reads
- non-repeatable reads
- phantom reads
Dirty reads occur when a transaction reads data that is being modified by another transaction which has not yet committed. For example, Transaction A may be updating an existing customer record into a database table, where the customer’s age is over 35. Transaction B is creating a report of all customers over 35. If Transaction B read the data from Transaction A’s new entry before it was committed, that is considered a “dirty” read. If the data from Transaction A is not committed for some reason, the report created by Transaction B would be invalid.
A non-repeatable read occurs when a database query returns data that could be different if that query were repeated (within the same transaction). As an example, Transaction A is creating a report of all customers over 35, and Transaction B is updating one of the customer records to mark that customer’s age as over 35. If the Transaction A report were run at the very beginning of Transaction B, then that customer would not be included in the report. If the Transaction A report were run at the end of transaction B, then that customer record would be included; the reports would be different within the same transaction.
A phantom read happens when new entries are added into the database table which could change a transaction’s results. Transaction A is reporting on customer ages over 35, and Transaction B is adding in a new customer; if Transaction A misses the row added by Transaction B (since it has already gathered all the information from that part of the database), that would be considered a phantom read, since running the same query a second time would result in additional data being returned (the new row entered by Transaction B).
Depending upon the application design pattern, you can prohibit one or all of the above situations using the above transaction isolation levels. However, the more restricted a setup is, the less concurrency – the fewer users who can use the database at the same time. If the most restrictive isolation level is chosen, then when one user is running a transaction, there are very few other transactions that can run until that one has completed.
Process Commander is designed to use the READ_COMMITTED
(“Cursor Stability”) transaction isolation level, to ensure both concurrency and data integrity. The REPEATABLE_READ
(“Read Stability”) isolation level also maintains data integrity, but with a much higher overhead. PRPC is designed to use the less restrictive and less expensive READ_COMMITTED
level while still maintaining complete integrity of the data.
You can see an example of the effect of the transaction isolation levels in Process Commander when the system uses a workbasket. If a user opens a workbasket to retrieve one of the assignments, they generally see a list of all the items available in that workbasket.
- If the isolation level is set to
READ_COMMITTED
, then the only item that would be locked during that viewing process would be the item that the user chooses and opens. (If no item is chosen, and the user just views the workbasket list and then closes it, then no items in that workbasket would be locked.)
- If the isolation level is set to
REPEATABLE_READ
, then when the user looks at the workbasket, all items in the list are locked, whether the user eventually opens them or not. No other user is allowed to work on any of those items while the first user reviews the workbasket list.
For the most efficient balance between database restriction and concurrency for a Process Commander application, set the transaction isolation level to READ_COMMITTED
.
If your application has a specific need to avoid phantom reads and non-repeatable reads, you may set the transaction isolation level to REPEATABLE_READ.
Realize, however, that this has effects on the database functionality.
To avoid too many locks in a REPEATABLE_READ
database setup, write the application so that workbaskets may only contain a few items (to limit the number of locks created). Also, avoid creating reports that count a large number of items.
(For example, if you have a report that calculates the number of work objects that were created the prior day, and there are several thousand work objects that get created each day, a database with the isolation level set to REPEATABLE_READ
must open and acquire a lock on each of these items as it is being counted for the report.)
Row-level locking
A REPEATABLE_READ
database should also be set to use row-level locking. There are several levels of database locking, including:
- row-level locking
- page-level locking
Row-level locking locks just the row of data that the system is currently accessing.
Page-level lockingl locks an entire block of data in the database table (one “page” of data). If this type of locking is enabled, users may be blocked or encounter unexpected database deadlocks. A deadlock occurs when User 1 is working on Work Object A, and tries to get a lock on Work Object B, at the same time that User 2 is working on Work Object B, and tries to get a lock on Work Object A.
Row-level locking does not guarantee that you won’t get deadlocks, but if they occur, the application design is at fault and can be changed. (In the above example, why is User 2 trying to get a lock on Work Object A? If the two objects are linked, then the business process should always start with Work Object A and link to B, rather than the other way around. This may mean that User 2 must wait until User 1 finishes working with Work Object A, but it will avoid a deadlock.)
Important notes
- Do not set the transaction isolation level to
SERIALIZABLE
, because the locking caused by this setting will prevent adequate performance from the database for Process Commander.
- Do not set the transaction isolation level to
READ_UNCOMMITTED
, because of the possibility of data integrity issues.
Related resources
http//www-1.ibm.com/support/docview.wss?rs=180&uid=swg21224492 - Changing the default isolation level using a new custom property: webSphereDefaultIsolationLevel
http://www-1.ibm.com/support/docview.wss?uid=swg21190874 - Determining and Changing the Isolation Level
http://publib.boulder.ibm.com/infocenter/cscv/v10r1/index.jsp?
topic=/com.ibm.cloudscape.doc/cdevconcepts15366.html - Locking, Concurrency, and Isolation Levels
http://www.redbooks.ibm.com/abstracts/sg246319.html - IBM Redbook: DB2 for z/OS and WebSphere: The Perfect Couple
http://www-1.ibm.com/support/docview.wss?rs=180&uid=swg1PK54755 - ‘WebSphereDefaultIsolationLevel’ custom property is not picked up when using the WebSphere Embedded ConnectJDBC Provider