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 difference lies between a query and a transaction. A transaction can contain any number of queries. To illustrate the difference, I set up a small example:
CREATE TABLE table_to_be_updated (
id serial PRIMARY KEY,
other_column text,
column_changing text
);
INSERT INTO table_to_be_updated (other_column, column_changing)
VALUES
('value', 'old_value'),
('value', 'other_value'),
('nonvalue', 'doesnt matter');
Then run two transactions concurrently (issuing the commands one by one, the middle line wants to depict the timeline):
| <-- BEGIN;
|
|
| UPDATE table_to_be_updated
BEGIN; -----------------------> | SET column_changing = 'new_value'
| WHERE
| other_column = 'value' AND
| column_changing = 'old_value';
|
|
SELECT column_changing -------> | -- update not yet committed
FROM table_to_be_updated |
WHERE other_column = 'value'; | <-- COMMIT;
|
|
SELECT column_changing -------> |
FROM table_to_be_updated |
WHERE other_column = 'value'; |
|
|
COMMIT; ----------------------> |
Running these in READ COMMITTED
isolation level, the first query returns a row with 'old_value', while the second one shows a row with 'new_value'. On an other run, I change the left-hand-side transaction isolation level:
SET transaction ISOLATION LEVEL REPEATABLE READ;
(The command must be the first statement in a transaction.)
Now both SELECTs return the same rowset, while a third one after committing both transactions will show the new row.
Best Answer
max_locks_per_transaction
does not apply to rows, but to objects. The fact that rows do not count is made explicit in the documentation:From https://www.postgresql.org/docs/current/static/runtime-config-locks.html
The information necessary to row locking is written in the row itself.