Container Managed Persistance FAQ

Container Managed Persistance

Frequently Asked Questions

What is Optimistic Concurrency Control?
How can I use it for my CMP 1.x Beans?
How can I use it for my CMP 2.x Beans?
What are the different options for this property?
How can I achieve Pessimistic Concurrency?
What are the advantages and disadvantages of using Pessimistic Concurrency?
Does Pessimistic Concurrency work in Exclusive Mode (Option A)?
Does Pessimistic Concurrency work with all the databases?
How can I achieve Optimistic Concurrency?
What are the advantages and disadvantages of using Optimistic Concurrency?
Does Optimistic Concurrency work with all the databases?
Does using Verify*Fields result in lot of transaction rollbacks?
How can I see all the SQL statements that are sent to the Database from the Container?
Is there a way to just log the SQL statements to a saperate file?

What is Optimistic Concurrency Control?

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

Back

How can I use it in my CMP 1.x beans?

You can specify this property at the entity bean level.

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

Back

How can I use it in my CMP 2.x beans?

You can specify this property at the table level. Note that this is a table level property unlike in cmp1.x beans.

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

Back

What are the different options for this property?

SelectForUpdate
SelectForUpdateNoWait
VerifyModifiedFields
VerifyAllFields
UpdateModifiedFields
UpdateAllFields

UpdateModifiedFields is the default option.

Back

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 block (wait) until the first transaction has completed. This is acheived 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 commited or rolledback. Other selects on the row are blacked till then.

- SelectForUpdateNoWAIT

The tuned SQL generated looks like this

SELECT ID, NAME FROM EMP_TABLE WHERE ID=? FOR UPDATE NOWAIT

Database locks the row until the current transaction is commited or rolledback. Other selects on the row will fail.

Back

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.

The disadvantage is the performance of the application suffers significantly because each transaction is blocked for the prior one to complete when accessing the same data.

Back

Does Pessimistic Concurrency work in Exclusive Mode (Option A)?

No. It is not recommended to use Option A caching when using Pessimistic Concurrency. In Option A (Exclusive mode), entity bean's data is loaded into the memory only once. Since Pessimistic concurrency depends on the ejbLoad() this combination will not work.

Back

Does Pessimistic Concurrency work with all the databases?

Pessimistic Concurrency depends on the underlying database to support "SELECT ...FOR UPDATE". This is a SQL standard and all major database vendors support this. Please check with your database documentation.

Back

How can i achieve Optimistic Concurrency?

In this mode container allows multiple transactions operate on same data at the same time. While this mode gives good performace, data integrity would be compromised.

- UpdateAllFields

With this option container issues an update on all fields, regardless of whether they were modified or not.Given a CMP bean with three 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 suppresses the update altogether if the bean was not modified. With the above bean, if only "value1" was modified,the following tuned update is issued:

UPDATE MyTable SET (value1 = <value1>) WHERE key = <key>

- VerifyModifiedFields
In this mode, the CMP engine issues a tuned update while verifying that the fields it is updating are consistent with the values that were previously read in. So, for the previous example,where only "value1" was modified, the followingupdate is issued:

UPDATE MyTabale SET (value1 = <value1>) WHERE key = <key> AND value1 = <old-value1>

- VerifyAllFields
This mode is similar to VerifyModifiedFields, except that all fields areverified. So the update would be:

UPDATE MyTabale SET (value1 = <value1>) WHERE key = <key> AND value1 = <old-value1>
AND value2 = <old-value2>

Back

What are the advantages and disadvantages of using Optimistic Concurrency?

The disadvantage is that the data integrity could be compramised if not used with caution.

The advantages are numerous, first one being allowing mulitiple transactions to access same data concurrently.

When UpdateModifiedFields option is used:
This can give a significant performance boost for following reasons:

1) Very often your data access is read-only. In such cases, not sending an update to the database is obviously a big win. We have seen order-of-magnitute performance boosts from this single optimization.
2) 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 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 DBMSs.
3) There is obviously less JDBC-based network traffic going to the database and less work going on in the JDBC driver. The network issue is, generally, not significant, but the JDBC driver issue is significant.Our performance measurements indicate that upwards of 70% of the CPUs time is spent in the JDBC driver in large-scale EJB applications. Often, this is due to the fact that many commercial JDBC drivers have not been sufficiently performance tuned. Even for well-tuned drivers, the less work they have to do, the better.
When VerifyModifiedFields and VerifyAllFields are used:

These two verify settings can be used to replicate the SERIALIZABLE isolation level in the Container. Often your application requires serializable isolation semantics. However, asking the database to implement this for you can have a significant performance impact. Our tests show using SERIALIZABLE with Oracle, instead of a 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

Does Optimistic Concurrency work with all the databases?

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

Back

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 the rollbacks by doing retries or some other actions that appropriate in your application domain.

Back

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 the 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 statements to the log file.

2. set -DEJBDebug=true to the partition VM. You can set this the same way as above. This option is very verbose and will generate lot of trace including method calls of the beans.
3. Use a third party utility to proxy the JDBC driver and trap all the SQL statements to a file. One such third party utility is described below.

Is there a way to just log the SQL statements to a saperate file?

Yes. As mentioned in the option 3 above, you can use a third party utility to write all the SQL statements sent to the database to a saperate file. One such utility is P6SPY. You can download this from http://www.p6spy.com. 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-INSTALL/classes/patches directory (You can create the directory in case it does not exist)
4. Edit the dar file (or jndi-definitions.xml file) 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-type>
            <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-type>
            <prop-value>none</prop-value>
        </property>
        <property>
            <prop-name>url</prop-name>
            <prop-type>String</prop-type>
            <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>
.......

5. Edit spy.properties file you copied to change the realdriver. 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. Example log file

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', 8)
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)