PostgreSQL – Maximum Row Level Locks on Same Row

lockingpostgresql

I need some insights on row-level locking in Postgres.

How many shared locks (FOR SHARE) can be concurrently on a particular tuple in a relation .
Will have many shared locks on same tuple cause any performance/or any issue to database.
If such limitations exist, how to configure them and advisable limits.

Best Answer

PostgreSQL doesn't remember any information about modified rows in memory, so there is no limit on the number of rows locked at one time. However, locking a row might cause a disk write, e.g., SELECT FOR UPDATE modifies selected rows to mark them locked, and so will result in disk writes

^{(Emphasis mine)}

So the only performance issue that you might get, is the number of disk writes heavy locking might cause.

While there is an "unlimited" number of row locks in the system, there is a limit on the number of objects (e.g. tables) that can participate in locking for a single transaction, which is controlled through max_locks_per_transaction

That property controls the number of objects one transaction can hold locks for, not the number of locks. A transaction can lock a million rows in a single table. However the default value of 64 for max_locks_per_transaction means that a transaction can hold locks for at most 64 tables (but e.g. a million rows in each table). So if your transactions never touch more than 64 tables, there is nothing to worry about.

A common reason to increase this value is if you are dropping a lot of tables in a single transaction e.g. through a drop owned by current_user statement to clean up a database.

Related Solutions

Shared Lock on IsolationLevel.ReadUncommitted 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.

SQL Server – Risk of Disabling Page Locking

In the referenced link How to force the use of row locks? Paul White demonstrates that index reorganization cannot happen if that index has page locks disabled.

You can, of course, reset Page locking back on before doing a reorganization and then turn it off again afterward:

-- Allow page locks on Index
ALTER INDEX indexname ON tablename SET (ALLOW_PAGE_LOCKS = ON);

For what it is worth, in response to this question https://stackoverflow.com/questions/3114826/is-it-possible-to-force-row-level-locking-in-sql-server Remus Rusanu gives the statement on disabling page locking on an index. But he also comments: "I would never turn this OFF on my database. The solution is always to properly design the schema and the queries so that scans (which are the culprits for escalation) don't occur to start with."

This does not mean that it is wrong to disable page locking, but it points out that there are other ways to work through the problem.

You also might be interested in reading about Lock Escalation (SQL Server 2008 R2 is the most recent document I can find) at: http://technet.microsoft.com/en-us/library/ms184286(v=sql.105).aspx

Best Answer

Related Solutions

Shared Lock on IsolationLevel.ReadUncommitted in SQL Server

SQL Server – Risk of Disabling Page Locking

Related Question