Can AUTO UPDATE STATISTICS hold an X-lock?

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?
I've posted the same question on the Connect Feedback area."Wes Clark" <WesClark@.discussions.microsoft.com> wrote in message
news:85E8BB89-22E7-427A-B263-4155783F125A@.microsoft.com...
> We are experiencing a non-deterministic (ND) problem. It is happening
> regularly, but not consistently. We run a suite of tests for performance
. . .
> 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?
>
In a simple test I did, I did not see any locks enlisted in a transaction
from automatically created statistics, but I suppose it's possible.
In any case, leaving transactions open when you return connections to the
pool is a dangerous and unusual thing to to. As you have discovered, you
have to be 100% sure you have not created any locks, and if one slips in,
you've got a big problem. Why not just commit? If you haven't written any
log records it doesn't really cost anything.
David|||Your suggestion sounds reasonable, and we shall consider it.
Do you know the answer to my questions: 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?
"David Browne" wrote:
> "Wes Clark" <WesClark@.discussions.microsoft.com> wrote in message
> news:85E8BB89-22E7-427A-B263-4155783F125A@.microsoft.com...
> > We are experiencing a non-deterministic (ND) problem. It is happening
> > regularly, but not consistently. We run a suite of tests for performance
> .. . .
> > 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?
> >
>
> In a simple test I did, I did not see any locks enlisted in a transaction
> from automatically created statistics, but I suppose it's possible.
> In any case, leaving transactions open when you return connections to the
> pool is a dangerous and unusual thing to to. As you have discovered, you
> have to be 100% sure you have not created any locks, and if one slips in,
> you've got a big problem. Why not just commit? If you haven't written any
> log records it doesn't really cost anything.
> David
>
>