Postgres Pro
Downloads
Home   >   mailing lists

Thread: Locking

Locking

From
Simon Attwell
Date:
How does postgresql deal with locking when one has a large select query running
on a > 700,000 row table, when there are inserts pending for that table.

I have an application that does a _lot_ of inserts, and a frontend that
makes large long laborious select queries on the same tables.

MySQL has the INSERT DELAYED which allows batch processing of inserts and
allows clients to receive and instant "OK" when doing inserts.

    - Simon

--
Simon Attwell
Systems Engineer
Berbee
5520 Research Park Drive
Madison, WI 53711
attwell@berbee.com

Berbee... putting the E in business.

Re: Locking

From
Peter Eisentraut
Date:
Simon Attwell writes:

> How does postgresql deal with locking when one has a large select query running
> on a > 700,000 row table, when there are inserts pending for that table.

The insert and select can happen in parallel.  See
http://www.postgresql.org/users-lounge/docs/7.0/postgres/mvcc.htm for the
gory details.

> I have an application that does a _lot_ of inserts, and a frontend that
> makes large long laborious select queries on the same tables.
>
> MySQL has the INSERT DELAYED which allows batch processing of inserts and
> allows clients to receive and instant "OK" when doing inserts.

No, PostgreSQL doesn't cheat.

--
Peter Eisentraut      peter_e@gmx.net       http://yi.org/peter-e/

Re: Locking

From
Tom Lane
Date:
Simon Attwell <attwell@binc.net> writes:
> How does postgresql deal with locking when one has a large select
> query running on a > 700,000 row table, when there are inserts pending
> for that table.

> I have an application that does a _lot_ of inserts, and a frontend that
> makes large long laborious select queries on the same tables.

In Postgres, you don't lock, you just do the operations.  The SELECT
query won't see the results of (nor be blocked by) updating transactions
that start after it does.  If you need consistency across multiple
SELECTs then wrap them all in a BEGIN/END block.  See
http://www.postgresql.org/devel-corner/docs/postgres/mvcc.html
for more detail.

> MySQL has the INSERT DELAYED which allows batch processing of inserts and
> allows clients to receive and instant "OK" when doing inserts.

We think our scheme is a great deal better ;-)

            regards, tom lane