1

I'm fairly new to postgresql. I've checked out the documentation and I can't find anything about how to do page-level locking.

I understand that I can set a table into a locking mode like this: LOCK TABLE myTable IN LOCKMODE ROW EXCLUSIVE;

How would I implement page level locking for my session? Let me know if it seems that I have a misunderstanding of the fundamentals.

John Gardeniers
  • 27,458
  • 12
  • 55
  • 109
Lebowski156
  • 113
  • 5
  • 1
    PostgreSQL doesn't have page level locking. – Frank Heikens Dec 01 '12 at 21:39
  • Actually, Postgres *does* have page-level locking, related to shared buffer pool. But this is an internal matter that should be transparent to application developers. See section 13.3.3 of current doc page, [Explicit Locking](http://www.postgresql.org/docs/current/static/explicit-locking.html). – Basil Bourque May 30 '15 at 07:00

1 Answers1

3

Why would you want to do explicit page level locking? You don't control what is on each page, so you'd be locking some arbitrary collection of data. What purpose would that serve?

I think you're looking for row-level locking, which is accomplished using SELECT ... FOR UPDATE or SELECT ... FOR SHARE.

PostgreSQL upgrades row-level locks to page-level locks internally in a few places like serializable snapshot predicate tracking, buffer management, etc. It'll do this when there are too many row level locks and they're becoming a performance issue. You don't need to care about this, as this will have almost no effect on you at the SQL level. Normal row locks - as acquired by UPDATE, DELETE, SELECT ... FOR UPDATE, SELECT ... FOR SHARE etc - are AFAIK never upgraded to page-level locks, as doing so would increase the probability of deadlocks.

If you're really convinced you want page-level locking, please explain what problem you're trying to solve in more detail and why row-level or table-level locking don't do the job.

See:

Craig Ringer
  • 11,083
  • 9
  • 40
  • 61