We are experiencing a non-deterministic (ND) problem. It is happening
regularly, but not consistently. We run a suite of tests for performance
evaluation. Some of the tests insert data into empty tables, and later these
tables are read. Each time we are able to reproduce the hang, it is because
one of the connections is shown as holding an X lock as described below on
one of the tables. The process holding the lock will always be shown as
sleeping by the SQL Server Activity Monitor. At the point that the hang
occurs, we will only be using a single connection to the database. We use a
connection pool, so there will be some connections shown as coming from the
JDBC driver (us) and sleeping. This is because of our connection pool.
However, there should be no outstanding active work for any of those
connections because we always end the transaction, either by commit or
rollback, before getting a new connection or at the end of an individual test
case.
My guess is that the X lock is being acquired by the autostats feature when
SQL Server is updating the statistics on a table during a SQL statement
because 1 of the thresholds (inserts, updates) has been exceeded and that the
lock is not being released as it should be. I am confident that we are not
explicitly updating the statistics on any table through our application until
well after this X lock becomes visible in the SQL Server Activity Monitor.
We are running of SQL Server 2005 SP1, and we experience the problem with
both the 1.0 and 1.1 CTP versions of the Microsoft JDBC driver. The database
has the default settings of AUTO_UPDATE_STATISTICS ON, and
AUTO_CREATE_STATISTICS ON, but AUTO_UPDATE_STATISTICS_ASYNC OFF.
Blocking process is holding an X lock (Type=Object, Subtype=UPDSTATS, Object
ID = 1157579163 – cc_typekeydatadist, Description = NULL, Owner Type =
TRANSACTION, Object = (internal))
Successfully processed 0 ops in 468.0069999694824 secs (running avg 0
ops/secs; last interval avg 0 ops/secs) Detailed Status:
Number of threads: 1, total number of tables: 634, number of tables
processed: 421, total number of commands: 846, number of commands processed:
492, number of command errors: 0
Index name synch started at: Mon Jul 31 17:00:58 PDT 2006, ended at: Mon Jul
31 17:00:58 PDT 2006, duration: 0 seconds
Thread #0: Active: true, number of tables processed: 421, number of commands
processed: 492, number of command errors: 0,
start time: Mon Jul 31 17:00:58 PDT 2006,
start command time: Mon Jul 31 17:01:09 PDT 2006, current command: UPDATE
STATISTICS cc_typekeydatadist WITH FULLSCAN
Successfully processed 0 ops in 478.021999835968 secs (running avg 0
ops/secs; last interval avg 0 ops/secs) Detailed Status:
Number of threads: 1, total number of tables: 634, number of tables
processed: 421, total number of commands: 846, number of commands processed:
492, number of command errors: 0
Index name synch started at: Mon Jul 31 17:00:58 PDT 2006, ended at: Mon Jul
31 17:00:58 PDT 2006, duration: 0 seconds
Thread #0: Active: true, number of tables processed: 421, number of commands
processed: 492, number of command errors: 0,
start time: Mon Jul 31 17:00:58 PDT 2006,
start command time: Mon Jul 31 17:01:09 PDT 2006, current command: UPDATE
STATISTICS cc_typekeydatadist WITH FULLSCAN
We are going to try running with all auto-stats options off, and with the
aync option on, to see if that works around the problem.
We do not issue a commit after a read-only operation, because we do not
expect to be holding any locks, but that we do return the connection to the
connection pool. Does the UPDATE STATISTICS operation occur in the same
transaction, and thus could be holding a lock unless we are running in
autocommit mode or do an explicit commit? Or does it occur on a separate,
server-initiated thread?
