As with one of my customers they do a lot of J2EE on IBM Websphere AS with SQL Server I observed an interesting phenomenon while troubleshooting excessive blocking.
First lets remind everyone on SQL Server Isolation Levels. SQL Server Supports six isolation levels. These are: READ COMMITTED (default), READ UNCOMMITTED, REPEATABLE READ, SNAPSHOT, SERIALIZABLE and READ COMMITTED SNAPSHOT. As you may understand using correct isolation level has a big impact on performance and concurrency, because:
1. It controls whether locks are taken, what kind of locks and how long these are held.
2. Whether transaction allows for “dirty reads” , uncommitted modifications by another transaction , reads only committed changes or even places exclusive lock and blocks until row is freed.
More on this is in SQL BOL –
The default isolation level in SQL Server is READ COMMITTED, which means that query retrieves the committed version of the row that existed at the time the statement or transaction started.
As customer reported database blocking problems I fired up old trusty blocking DMV output and noted that for some odd reason despite default ISOLATION LEVEL in database being READ COMMITTED my head blocker was running under REPEATABLE READ.
As per SQL Server BOL for READ COMMITTED– “Specifies that statements cannot read data that has been modified but not committed by other transactions. This prevents dirty reads. Data can be changed by other transactions between individual statements within the current transaction, resulting in nonrepeatable reads or phantom data.” Vs. REPEATABLE READ – “Specifies that statements cannot read data that has been modified but not yet committed by other transactions and that no other transactions can modify data that has been read by the current transaction until the current transaction completes.Shared locks are placed on all data read by each statement in the transaction and are held until the transaction completes. This prevents other transactions from modifying any rows that have been read by the current transaction. Other transactions can insert new rows that match the search conditions of statements issued by the current transaction. If the current transaction then retries the statement it will retrieve the new rows, which results in phantom reads. Because shared locks are held to the end of a transaction instead of being released at the end of each statement, concurrency is lower than the default READ COMMITTED isolation level. Use this option only when necessary.”
Obviously REPEATABLE READ is more strict isolation level, therefore prone to more blocking.
So why? To do get to the bottom of this I had to do some research on Websphere front, mainly into something called Websphere access intent policies. As per IBM – “WebSphere Application Server access intent policies provide a consistent way of defining the isolation level for CMP bean data across the different relational databases in your environment. Within a deployed application, the combination of an access intent policy concurrency definition and access type signifies the isolation level value that Application Server sets on a database connection”
And looks also that default with SQL Server is Repeatable Read as referenced in IBM docs.
As soon as customer changed policy to wsOptimisticRead which translated to READ COMMITTED in SQL Server world vs. wsPessimisticUpdateWeakestLockAtLoad WebSphere default blocking in the system subsided dramatically. So note to all SQL DBAs troubleshooting blocking and concurrency performance issues from J2EE\IBM WebSphere AS – check isolation level, don’t just assume that if database is set to READ COMMITTED by default that’s what your client connections are running under. If you see unusually restrictive isolation level (REPEATABLE READ, SERIALIZABLE) that you don’t expect, check with your development team on Access Intent Policy in WebSphere.