4

I have a work queue in SQL, managed by some services that read out entries to process with READPAST queries.

I recently added a UI to check on queue status that uses READ UNCOMMITTED NHibernate queries in C#. It's not at all critical that they give me the right numbers back, just an indication of how far along the processing is.

/* called from a foreach (I know... I could get them all at once with SQL) */
IQuery query = Persistence.CreateQuery(
    "select count(qi) from QueueItem qi where qi.Job = :job");
query.SetEntity("job", thisJob);
using(Persistence.ReadUncommitted())
{
    return (long)query.UniqueResult();
}

The problem is that these status queries have started causing timeouts. Sometimes they fail themselves; sometimes they cause queue operations to fail.

The main MSDN documentation says there can still be locking if I change the schema, which I am not.

On the other hand, Marcel van der Holst who apparently works for Microsoft's SQL Server team, says this on the subject (the Michael asking the question is not me):

READ UNCOMMITTED transactions will not take any database locks, but will still have to read databases pages to read the actual data. If other transactions are writing these pages at the same time, their might be some blocking between the two. Internally in the engine, we do not allow any transactions to read a page while a write is being in progress (we use latches to guarantee this). If a lot of transactions are writing while your big queries are going on, the big read might still become blocked.

Am I doing something wildly wrong? What should I change to stop blocking?

Schema

create table QueueItem(
    ID int identity(1,1) not null,
    JobID int not null,
    PersonID int not null,
    DateProcessed datetime null,
    Error varchar(max) null,
    constraint [PK_QueueItem] primary key nonclustered (ID)
)

alter table QueueItem
add constraint [FK_QueueItemsToJobs] foreign key (JobID)
references Job (ID)

Nonclustered indices:

JobID, DateProcessed, PersonID, ID (Non-Unique, Non-Clustered)
DateProcessed, JobID, PersonID (Non-Unique, Non-Clustered)
JobID, ID (Unique, Non-Clustered)
JobID, PersonID, ID (Unique, Non-Clustered)
PersonID, JobID, ID, DateProcessed (Unique, Non-Clustered)
ID (Unique, Non-Clustered)

In Practice

I have reduced the number of reads because it's not going to make things slower, but I'm still curious why there might be blocking with READ UNCOMMITTED.

Michael
  • 8,362
  • 6
  • 61
  • 88
  • From a purely TSQL/query writing standpoint, I'd add the "(nolock)" modifier to each table that is in the query, but I'm not sure how to accomplish that via NHibernate. – ganders Aug 01 '13 at 17:32
  • Can you include the table definition? – brian Aug 01 '13 at 19:11
  • @brian Here's the table setup. Is the foreign key what is killing me? – Michael Aug 01 '13 at 20:10
  • Are the read-uncommitted queries putting high load on the system? Maybe full IO load? Or your code is failing to actually use read uncommitted isolation level. Confirm the level that is being used by running this: http://stackoverflow.com/questions/1038113/how-to-find-current-transaction-level Or use Profiler to confirm that the right isolation level is being set. – usr Aug 01 '13 at 21:48
  • @Michael. Maybe, but I don't know enough about the table. Can you include the definition for any nonclustered index(s) on QueueItem? Also, what's the query you are trying to run? – brian Aug 02 '13 at 04:38
  • are you using row versioning? with row versioning set this should not occur - without row versioning on the other hand I've had similar experiences... – Linky Aug 07 '13 at 21:20
  • @Linky After looking at [these](http://technet.microsoft.com/en-us/library/ms187101(v=sql.105).aspx) [links](http://msdn.microsoft.com/en-us/library/ms189050(v=sql.105).aspx), I am fairly certain I am not using row versioning. I know I can fix my setup; I mostly want to know _why_ it doesn't work as-is. – Michael Aug 07 '13 at 21:31

1 Answers1

0

I'm not an expert on NHibernate, but you should try to add NOLOCK to your status queries; use SetLockMode in NHibernate.

  1. Reduce the number of indexes, looks like every column is indexed.
  2. You are not using a clustered index, add a unique clustered index on the columns JobID, ID.
Joe Perkins
  • 160
  • 2
  • 6