Frequently Asked Questions

1. What is Optimistic Concurrency Control?
2. How can I use it for my CMP 1.x Beans?
3. How can I use it for my CMP 2.x Beans?
4. What are the different options for this property?
5. How can I achieve Pessimistic Concurrency?
6. What are the advantages and disadvantages of using Pessimistic Concurrency?
7. Does Pessimistic Concurrency work in Exclusive Mode (Option A)?
8. Does Pessimistic Concurrency work with all Databases?
9. How can I achieve Optimistic Concurrency?
10. What are the advantages and disadvantages of using Optimistic Concurrency?
11. Does Optimistic Concurrency work with all Databases?
12. Does using Verify*Fields result in lot of transaction rollbacks?
13. How can I see all SQL statements that are sent to Databases from Container?
14. Is there a way to just log the SQL Statements to a separate file?

1. What is Optimistic and Pessimistic Concurrency Control?

The Container uses optimistic or pessimistic concurrency to control the behavior of multiple transaction accessing the same data.

Back

2. How can I use it for my CMP 1.x Beans?

You can specify this property at entity bean level:

<property>
  <prop-name>ejb.cmp.optimisticConcurrencyBehavior</prop-name>
  <prop-type>Enumerated</prop-type>
  <prop-value>VerifyModifiedFields</prop-value>
</property>

Back

3. How can I use it for my CMP 2.x Beans?

You can specify this property at the table level. NOTE: this is a table-level property:

<property>
  <prop-name>optimisticConcurrencyBehavior</prop-name>
  <prop-type>Enumerated</prop-type>
  <prop-value>VerifyModifiedFields</prop-value>
</property>

Back

4. What are the different options for this property?

• SelectForUpdate
• SelectForUpdateNoWait
• VerifyModifiedFields
• VerifyAllFields
• UpdateModifiedFields   (default value)
• UpdateAllFields

Back

5. How can I achieve Pessimistic Concurrency?

In this mode Container will allow only one transaction at a time to access the data the Entity Bean is holding. Other transactions that want access to the same data will be blocked (wait) until the first transaction has completed. This is achieved by issuing a tuned SQL with "FOR UPDATE" when Entity Bean is loaded.

• SelectForUpdate
  The tuned SQL generated looks like this
  SELECT ID, NAME FROM EMP_TABLE WHERE ID=? FOR UPDATE
  Database locks the row until the current transaction is committed or rolled back. Other SELECTs on the row are blocked until then.
  
  
• SelectForUpdateNoWait
  The tuned SQL ganereated looks like this
  SELECT ID, NAME FROM EMP_TABLE WHERE ID=? FOR UPDATE NOWAIT
  Database locks the row until the current transaction is committed or rolled back. Other SELECTs on the row will fail

Back

6. What are the advantages and disadvantages of using Pessimistic Concurrency?

The advantage is that the data integrity is maintained as only one transaction is allowed to write at a time.
On the other hand, the performance of the application would suffers significantly because each transaction is blocked for prior one to complete when accessing the same data, which is a severe disadvantage in term of the performance.

Back

7. Does Pessimistic Concurrency work in Exclusive Mode (i.e. Option A)?

No. It is not recommended to use Option A caching when using Pessimistic Concurrency. In Option A mode (i.e. Exclusive Mode), Entity Bean's data is loaded into memory only ONCE. Since Pessimistic Concurrency depends on ejbLoad(), this combination simply won't work.

Back

8. Does Pessimistic Concurrency work with all Databases?

Pessimistic Concurrency depends on underlying database to support "SELECT ... FOR UPDATE ...". This is defined in SQL Standard and all major Database Vendors support this. Nevertheless, one should check with her/his own Database Documentation.

Back

9. How can I achieve Optimistic Concurrency?

In Optimistic Concurrency mode, Container allows multiple transactions operate on same data at the same time. While this mode gives good performance, data integrity might be compromised (therefore, Be Careful!)

• UpdateAllFields
  With this option, Container issues an update on all fields, regardless of whether they were modified or not. Given a CMP bean with 3 fields: "key", "value1" and "value2", stored in a table called "MyTable", the following update will be issued at the end of every transaction, regardless of whether the bean was modified or not:
  UPDATE MYTABLE SET (VALUE1=<value1>, VALUE2=<value2>) WHERE KEY=<KEY>
  
  
• UpdateModifiedFields
  This is the default setting. Issues an update only on the fields that were modified, or suppress the update altogether if the bean was not modified. With the above bean, if only "value1" was modified, the following tuned SQL statement will be generated:
  UPDATE MYTABLE SET (VALUE1=<value1>) WHERE KEY=<KEY>
  
  
• VerifyModifiedFields
  In this mode, CMP engine issues a tuned update while verifying that the fields being updated are consistent with the values that were previously fetched from Database. Following previous sample scenario, the following SQL statement is issued:
  UPDATE MYTABLE SET (VALUE1=<value1> WHERE KEY=<KEY> AND VALUE1 = <old value>
  
  
• VerifyAllFields
  This mode is similar to VerifyModifiedFields, except that all fields are verified. i.e.
  UPDATE MYTABLE set (VALUE1=<value1> WHERE KEY=<KEY> AND VALUE1=<old value> AND VALUE2=<old value>

Back

10. What are the advantages and disadvantages of using Optimistic Concurrency?

The main disadvantage is that the data integrity could be compromised if NOT used with caution.

On the other hand, if used appropriately,  the advantages are numerous -- it allows multiple transaction to access same data concurrently. 

• When UpdateModifiedFields option is used: this can give a significant performance boost for the following reasons (or rather scenarios):
  
  • Very often, the data access is read-only. In such cases, not sending an update to Database is obviously a big win (it has been observed of order-of-magnitude performance boosting from this single optimization!)
  • Many Databases write logs depending on which columns were modified. For example, SQL Server will log the UPDATE if a TEXT or IMAGE field is updated, regardless of whether the column's value actually changed. Note that the database often does not (or cannot) distinguish between updating a column to hold the same same value it used to hold (which is what occurs with "UpdateAllFields"), and actually modifying the column's value. Suppressing the update for the case where the value did not actually change can have a very significant performance impact when using such DBMS
  • There is obviously less JDBC-based network traffic going to the database and less work going on in JDBC driver. The network issue is, generally, not significant, but JDBC driver issue is significant! Our performance measurements indicate that upwards of 70% of CPU time is spent in JDBC driver in large-scale EJB applications. Often, this is due to the fact that many commercial JDBC drivers have not been sufficiently tuned. Even for well-tuned drivers, the less work they have to do, the better.
    
    
• When VerifyModifiedFields or VerifiAllFields are used:
  These two verify settings can be used to replicate the SERIALIZABLE isolation level in Container. Often your application requires serialized isolation semantics. However, asking the database to implement this for you can have a significant performance impact. Our tests show that using SERIZLIZABLE with Oracle, instead of less restricted isolation level, can slow down an application by over 50%. The main reason for this slowdown is that Oracle provides optimistic concurrency using a row-level locking model. With the above two settings, you are basically asking the CMP engine to implement optimistic concurrency using field-level locking. And with any concurrent system, the smaller the granularity of the locking, the better the concurrency.

Back

11. Does Optimistic Concurrency work with all the Databases?

Yes. Optimistic Concurrency only relies on the "WHERE" clause, which is supported by all the RDBMS.

Back

12. Does using Verify*Fields result in lot of transaction rollbacks?

Yes, transaction rollbacks do happen because same data is accessed by multiple transactions. You need to take this into account while designing your application and handle rollbacks by doing retries or some other actions that appropriate in your application domain.

Back

13. How can I see all the SQL statements that are sent to the Database from the Container?

There are three ways to achieve this:

1. set -DDataSourceDebug=true to Partition VM. You can do this by editing partition_server.config file and adding this line to the end of the file:
   vmparam -DDataSourceDebug=true
   This will print all the SQL Statement to the log file
2. Set -DEJBDebug=true to Partition VM. You can set this the same way as above. This options is very verbose and will generate lot of trace including method calls of the beans.
3. Using a 3rd party utility to proxy the JDBC driver and trap all the SQL Statement to a file. One such 3rd party utility is described in next Q&A Answer.

Back

14. Is there a way to just log the SQL statements to a separate file?

Yes. As mentioned in Option 3 above, you can use 3rd party utilities to write all SQL statement sent to the Database to a separate file. One such utility is P6SPY. You can download this from GitHub.

Follow these steps to install and configure P6SPY:

1. Extract the p6spy-install.jar file. The p6spy-install.jar file contains p6spy.jar and spy.properties
2. Deploy p6spy.jar into the partition as a library (this requires a restart of the Partition)
3. Copy spy.properties to ${BES}/classes/patches directory (You can create the directory in case it does not exist)
4. Edit the default dar file (or jndi-definitions.xml) to change the driver settings to use P6SPY driver. For example
   <jndi-definitions>
     <visitransact-datasource>
       <jndi-name>serial://datasources/JDSLocal</jndi-name>
       <driver-datasource-jndiname>serial://datasources/JdsLocalDriver</driver-datasource-jndiname>
       <property>
         <prop-name>connectionType</prop-name>
         <prop-type>Enumerated</prop-name>
         <prop-value>Direct</prop-value>
       </property>
       <property>
         <prop-name>dialect</prop-name>
         <prop-type>Enumerated</prop-type>
         <prop-value>jdatastore</prop-value>
       </property>
     </visitransact-datasource>
     <driver-datasource>
       <jndi-name>serial://datasources/JdsLocalDriver</jndi-name>
       <datasource-class-name>com.inprise.visitransact.jdbc1w2.InpriseConnectionPoolDataSource</datasource-class-name>
       <property>
         <prop-name>user</prop-name>
         <prop-type>String</prop-type>
         <prop-value>none</prop-value>
       </property>
       <property>
         <prop-name>password</prop-name>
         <prop-type>String</prop-name>
         <prop-value>none</prop-name>
       </property>
       <property>
         <prop-name>url</prop-name>
         <prop-type>String</prop-name>
         <prop-value>jdbc:borland:dslocal:ejbcontainer.jds</prop-value>
       </property>
       <property>
         <prop-name>driverClassName</prop-name>
         <prop-type>String</prop-type>
         <prop-value>com.p6spy.engine.spy.P6SpyDriver</prop-value>
       </property>
     </driver-datasource>
     .... ....
   </jndi-definitions>
5. Edit spy.properties file you copied to change the real driver. In the case of JDatastore:
   realdriver=com.borland.datastore.jdbc.DataStoreDriver
6. Restart the partition
7. You can specify the file name where you want to log the SQL statements in the spy.properties. Otherwise, it will be stored in partition working directory by default. 
   A sample output will be like this:
   
   
   1083785556103|-1||debug||com.p6spy.engine.common.P6SpyOptions reloading properties
   1083785556150|-1||info||Using properties file: C:\BDP\classes\patches\spy.properties
   1083785556150|-1||info||No value in environment for: getStackTrace, using: false
   1083785556150|-1||info||No value in environment for: getAppender, using: com.p6spy.engine.logging.appender.FileLogger
   1083785556150|-1||info||No value in environment for: getFilter, using: false
   1083785556150|-1||info||No value in environment for: getAppend, using: true
   1083785556150|-1||info||No value in environment for: getDeregisterDrivers, using: true
   1083785556150|-1||info||No value in environment for: getUsePrefix, using: false
   1083785556150|-1||info||No value in environment for: getExecutionThreshold, using: 0
   1083785556150|-1||info||No value in environment for: getAutoflush, using: true
   1083785556150|-1||info||No value in environment for: getExclude, using: 
   1083785556150|-1||info||No value in environment for: getExcludecategories, using: info,debug,result,batch
   1083785556150|-1||info||No value in environment for: getInclude, using: 
   1083785556150|-1||info||No value in environment for: getIncludecategories, using: 
   1083785556150|-1||info||No value in environment for: getLogfile, using: spy.log
   1083785556150|-1||info||No value in environment for: getRealdriver, using: com.borland.datastore.jdbc.DataStoreDriver
   1083785556150|-1||info||No value in environment for: getRealdriver2, using: 
   1083785556150|-1||info||No value in environment for: getRealdriver3, using: 
   1083785556150|-1||info||No value in environment for: getSpydriver, using: com.p6spy.engine.spy.P6SpyDriver
   1083785556150|-1||info||No value in environment for: getDateformat, using: 
   1083785556150|-1||info||No value in environment for: getDateformatter, using: null
   1083785556150|-1||info||No value in environment for: getStringmatcher, using: com.p6spy.engine.common.SubstringMatcher
   1083785556150|-1||info||No value in environment for: getStringMatcherEngine, using: com.p6spy.engine.common.SubstringMatcher@73cc11
   1083785556150|-1||info||No value in environment for: getStackTraceClass, using: 
   1083785556150|-1||info||No value in environment for: getSQLExpression, using: null
   1083785556150|-1||info||No value in environment for: getReloadProperties, using: false
   1083785556150|-1||info||No value in environment for: getReloadPropertiesInterval, using: 60
   1083785556150|-1||info||No value in environment for: getJNDIContextFactory, using: null
   1083785556150|-1||info||No value in environment for: getJNDIContextProviderURL, using: null
   1083785556150|-1||info||No value in environment for: getJNDIContextCustom, using: null
   1083785556150|-1||info||No value in environment for: getRealDataSource, using: null
   1083785556150|-1||info||No value in environment for: getRealDataSourceClass, using: null
   1083785556165|-1||info||No value in environment for: getRealDataSourceProperties, using: null
   1083785556603|47|0|statement|CREATE TABLE ORDER_SHARED (ADDRESS VARCHAR, FIRST_NAME VARCHAR, LAST_NAME VARCHAR, ORDER_NUMBER INTEGER NOT NULL, PRIMARY KEY (ORDER_NUMBER))|CREATE TABLE ORDER_SHARED (ADDRESS VARCHAR, FIRST_NAME VARCHAR, LAST_NAME VARCHAR, ORDER_NUMBER INTEGER NOT NULL, PRIMARY KEY (ORDER_NUMBER))
   1083785556634|16|0|statement|SELECT * FROM ORDER_SHARED WHERE 1 = 0|SELECT * FROM ORDER_SHARED WHERE 1 = 0
   1083785556665|31|0|commit||
   1083785556681|16|0|statement|CREATE TABLE LINEITEM_SHARED (ITEM VARCHAR, LINE INTEGER NOT NULL, ORDER_NUMBER INTEGER, QUANTITY SMALLINT, PRIMARY KEY (LINE))|CREATE TABLE LINEITEM_SHARED (ITEM VARCHAR, LINE INTEGER NOT NULL, ORDER_NUMBER INTEGER, QUANTITY SMALLINT, PRIMARY KEY (LINE))
   1083785556681|0|0|statement|SELECT * FROM LINEITEM_SHARED WHERE 1 = 0|SELECT * FROM LINEITEM_SHARED WHERE 1 = 0
   1083785556712|31|0|commit||
   1083785556775|16|0|statement|INSERT INTO ORDER_SHARED (ADDRESS, FIRST_NAME, LAST_NAME, ORDER_NUMBER) VALUES (?, ?, ?, ?)|INSERT INTO ORDER_SHARED (ADDRESS, FIRST_NAME, LAST_NAME, ORDER_NUMBER) VALUES ('Address0', 'Customer Last0', 'Customer First0', 0)
   1083785556775|0|0|statement|INSERT INTO ORDER_SHARED (ADDRESS, FIRST_NAME, LAST_NAME, ORDER_NUMBER) VALUES (?, ?, ?, ?)|INSERT INTO ORDER_SHARED (ADDRESS, FIRST_NAME, LAST_NAME, ORDER_NUMBER) VALUES ('Address1', 'Customer Last1', 'Customer First1', 1)
   1083785556775|0|0|statement|INSERT INTO ORDER_SHARED (ADDRESS, FIRST_NAME, LAST_NAME, ORDER_NUMBER) VALUES (?, ?, ?, ?)|INSERT INTO ORDER_SHARED (ADDRESS, FIRST_NAME, LAST_NAME, ORDER_NUMBER) VALUES ('Address2', 'Customer Last2', 'Customer First2', 2)
   1083785556775|0|0|statement|INSERT INTO ORDER_SHARED (ADDRESS, FIRST_NAME, LAST_NAME, ORDER_NUMBER) VALUES (?, ?, ?, ?)|INSERT INTO ORDER_SHARED (ADDRESS, FIRST_NAME, LAST_NAME, ORDER_NUMBER) VALUES ('Address3', 'Customer Last3', 'Customer First3', 3)
   1083785556775|0|0|statement|INSERT INTO ORDER_SHARED (ADDRESS, FIRST_NAME, LAST_NAME, ORDER_NUMBER) VALUES (?, ?, ?, ?)|INSERT INTO ORDER_SHARED (ADDRESS, FIRST_NAME, LAST_NAME, ORDER_NUMBER) VALUES ('Address4', 'Customer Last4', 'Customer First4', 4)
   1083785556775|0|0|statement|INSERT INTO ORDER_SHARED (ADDRESS, FIRST_NAME, LAST_NAME, ORDER_NUMBER) VALUES (?, ?, ?, ?)|INSERT INTO ORDER_SHARED (ADDRESS, FIRST_NAME, LAST_NAME, ORDER_NUMBER) VALUES ('Address5', 'Customer Last5', 'Customer First5', 5)
   1083785556775|0|0|statement|INSERT INTO ORDER_SHARED (ADDRESS, FIRST_NAME, LAST_NAME, ORDER_NUMBER) VALUES (?, ?, ?, ?)|INSERT INTO ORDER_SHARED (ADDRESS, FIRST_NAME, LAST_NAME, ORDER_NUMBER) VALUES ('Address6', 'Customer Last6', 'Customer First6', 6)
   1083785556775|0|0|statement|INSERT INTO ORDER_SHARED (ADDRESS, FIRST_NAME, LAST_NAME, ORDER_NUMBER) VALUES (?, ?, ?, ?)|INSERT INTO ORDER_SHARED (ADDRESS, FIRST_NAME, LAST_NAME, ORDER_NUMBER) VALUES ('Address7', 'Customer Last7', 'Customer First7', 7)
   1083785556790|0|0|statement|INSERT INTO ORDER_SHARED (ADDRESS, FIRST_NAME, LAST_NAME, ORDER_NUMBER) VALUES (?, ?, ?, ?)|INSERT INTO ORDER_SHARED (ADDRESS, FIRST_NAME, LAST_NAME, ORDER_NUMBER) VALUES ('Address8', 'Customer Last8', 'Customer First8',
   1083785556790|0|0|statement|INSERT INTO ORDER_SHARED (ADDRESS, FIRST_NAME, LAST_NAME, ORDER_NUMBER) VALUES (?, ?, ?, ?)|INSERT INTO ORDER_SHARED (ADDRESS, FIRST_NAME, LAST_NAME, ORDER_NUMBER) VALUES ('Address9', 'Customer Last9', 'Customer First9', 9)
   1083785556790|0|0|statement|INSERT INTO ORDER_SHARED (ADDRESS, FIRST_NAME, LAST_NAME, ORDER_NUMBER) VALUES (?, ?, ?, ?)|INSERT INTO ORDER_SHARED (ADDRESS, FIRST_NAME, LAST_NAME, ORDER_NUMBER) VALUES ('Address10', 'Customer Last10', 'Customer First10', 10)
   1083785556790|0|0|statement|INSERT INTO ORDER_SHARED (ADDRESS, FIRST_NAME, LAST_NAME, ORDER_NUMBER) VALUES (?, ?, ?, ?)|INSERT INTO ORDER_SHARED (ADDRESS, FIRST_NAME, LAST_NAME, ORDER_NUMBER) VALUES ('Address11', 'Customer Last11', 'Customer First11', 11)
   1083785556790|0|0|statement|INSERT INTO ORDER_SHARED (ADDRESS, FIRST_NAME, LAST_NAME, ORDER_NUMBER) VALUES (?, ?, ?, ?)|INSERT INTO ORDER_SHARED (ADDRESS, FIRST_NAME, LAST_NAME, ORDER_NUMBER) VALUES ('Address12', 'Customer Last12', 'Customer First12', 12)
   1083785556790|0|0|statement|INSERT INTO ORDER_SHARED (ADDRESS, FIRST_NAME, LAST_NAME, ORDER_NUMBER) VALUES (?, ?, ?, ?)|INSERT INTO ORDER_SHARED (ADDRESS, FIRST_NAME, LAST_NAME, ORDER_NUMBER) VALUES ('Address13', 'Customer Last13', 'Customer First13', 13)
   1083785556790|0|0|statement|INSERT INTO ORDER_SHARED (ADDRESS, FIRST_NAME, LAST_NAME, ORDER_NUMBER) VALUES (?, ?, ?, ?)|INSERT INTO ORDER_SHARED (ADDRESS, FIRST_NAME, LAST_NAME, ORDER_NUMBER) VALUES ('Address14', 'Customer Last14', 'Customer First14', 14)
   1083785556806|0|0|statement|INSERT INTO ORDER_SHARED (ADDRESS, FIRST_NAME, LAST_NAME, ORDER_NUMBER) VALUES (?, ?, ?, ?)|INSERT INTO ORDER_SHARED (ADDRESS, FIRST_NAME, LAST_NAME, ORDER_NUMBER) VALUES ('Address15', 'Customer Last15', 'Customer First15', 15)
   1083785556806|0|0|statement|INSERT INTO ORDER_SHARED (ADDRESS, FIRST_NAME, LAST_NAME, ORDER_NUMBER) VALUES (?, ?, ?, ?)|INSERT INTO ORDER_SHARED (ADDRESS, FIRST_NAME, LAST_NAME, ORDER_NUMBER) VALUES ('Address16', 'Customer Last16', 'Customer First16', 16)
   1083785556806|0|0|statement|INSERT INTO ORDER_SHARED (ADDRESS, FIRST_NAME, LAST_NAME, ORDER_NUMBER) VALUES (?, ?, ?, ?)|INSERT INTO ORDER_SHARED (ADDRESS, FIRST_NAME, LAST_NAME, ORDER_NUMBER) VALUES ('Address17', 'Customer Last17', 'Customer First17', 17)
   1083785556806|0|0|statement|INSERT INTO ORDER_SHARED (ADDRESS, FIRST_NAME, LAST_NAME, ORDER_NUMBER) VALUES (?, ?, ?, ?)|INSERT INTO ORDER_SHARED (ADDRESS, FIRST_NAME, LAST_NAME, ORDER_NUMBER) VALUES ('Address18', 'Customer Last18', 'Customer First18', 18)
   1083785556821|0|0|statement|INSERT INTO ORDER_SHARED (ADDRESS, FIRST_NAME, LAST_NAME, ORDER_NUMBER) VALUES (?, ?, ?, ?)|INSERT INTO ORDER_SHARED (ADDRESS, FIRST_NAME, LAST_NAME, ORDER_NUMBER) VALUES ('Address19', 'Customer Last19', 'Customer First19', 19)
   1083785556821|0|0|statement|INSERT INTO ORDER_SHARED (ADDRESS, FIRST_NAME, LAST_NAME, ORDER_NUMBER) VALUES (?, ?, ?, ?)|INSERT INTO ORDER_SHARED (ADDRESS, FIRST_NAME, LAST_NAME, ORDER_NUMBER) VALUES ('Address20', 'Customer Last20', 'Customer First20', 20)
   1083785556821|0|0|statement|INSERT INTO ORDER_SHARED (ADDRESS, FIRST_NAME, LAST_NAME, ORDER_NUMBER) VALUES (?, ?, ?, ?)|INSERT INTO ORDER_SHARED (ADDRESS, FIRST_NAME, LAST_NAME, ORDER_NUMBER) VALUES ('Address21', 'Customer Last21', 'Customer First21', 21)

Back