postgresql db table locking or row locking multi query execution

Question

I would like to understand how postgreSQL is doing multi query execution for example i have a DB that there are lots of insert queries running for example 20-40 per minute and lots of select queries like 200-300 per minute (simple query by primary key selection).

This type of queries are run on the same table and i'm curious on how postgreSQL is handling these. Is it like when insert query is run table is lock and we have to wait for select queries or it is row locking so that while insert query is in progress select queries can continue and ignore locked rows?

In mysql database there is MyISAM engine that does table lock and innoDB that does row locking i guess...

user330315 · Accepted Answer · 2018-09-21 08:34:42Z

1

Postgres implements multiv version concurrency control (MVCC) which means that readers never block writers and writers never block readers.

For normal DML statements Postgres never takes a table lock either so the SELECT queries are never blocked by any of the INSERT statements you are running concurrently.

The Postgres Wiki contains links to more detailed descriptions on how exactly MVCC is implemented and works in Postgres.

Essentially every modern DBMS uses some kind of MVCC these days. Oracle, Firebird and DB2 have "always" been using it. SQL Server introduced it with SQL Server 2005 (although it's still not the default behaviour) and in MySQL the InnoDB engine uses it.

answered Sep 21, 2018 at 8:34

user330315

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

postgresql db table locking or row locking multi query execution

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related