Sql-server – JDBC Read uncommitted in SQL Server

What is HOBT lock?

A lock protecting a B-tree (index) or the heap data pages in a table that does not have a clustered index.

Why would I still get a S lock?

This happens on heaps. Example

SET NOCOUNT ON;

DECLARE @Query nvarchar(max) = 
   N'DECLARE @C INT; 
     SELECT @C = COUNT(*) FROM master.dbo.MSreplication_options';

/*Run once so compilation out of the way*/
EXEC(@Query);

DBCC TRACEON(-1,3604,1200) WITH NO_INFOMSGS;

PRINT 'READ UNCOMMITTED';
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
EXEC(@Query);

PRINT 'READ COMMITTED';
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
EXEC(@Query);

DBCC TRACEOFF(-1,3604,1200) WITH NO_INFOMSGS;

Output READ UNCOMMITTED

Process 56 acquiring Sch-S lock on OBJECT: 1:1163151189:0  (class bit0 ref1) result: OK

Process 56 acquiring S lock on HOBT: 1:72057594038910976 [BULK_OPERATION] (class bit0 ref1) result: OK

Process 56 releasing lock on OBJECT: 1:1163151189:0 

Output READ COMMITTED

Process 56 acquiring IS lock on OBJECT: 1:1163151189:0  (class bit0 ref1) result: OK

Process 56 acquiring IS lock on PAGE: 1:1:169 (class bit0 ref1) result: OK

Process 56 releasing lock on PAGE: 1:1:169

Process 56 releasing lock on OBJECT: 1:1163151189:0 

According to this article referencing Paul Randal the reason for taking this BULK_OPERATION shared HOBT lock is to prevent reading of unformatted pages.

The rows that qualify for an update are always stabilized during discovery by using (at least) U locks (Update locks, see Lock Compatibility). When is time to actually update them, the U lock is upgraded to X lock. Because of this stabilization the row cannot disappear nor can it be modified between the discovery and the update. Nor can a second UPDATE statement discover the same row: a concurrent UPDATE will have to wait for first to commit even if it only needs to 'check' the row and not actually update it. So Batch A is 100% bulletproof. For Batch B, most times the engine will understand what you mean and scan the join with update semantics (your IN clause will be rewritten as a JOIN during query normalization).

There are also differences in behavior depending on whether the plan requires Halloween Protection or not, but that only means the U locks is longer lived or not.

As a general comment the queries you show are the hallmark queries of using table row state where you should be using queues. The concurrency of SET InProgress = 1 WHERE Completed = 0 is basically 1, because it will always require a scan. An equivalent solution using queues for events to signal requests to transition state will scale basically X CPUs.