Posted by Andy Hassall on 07/11/07 23:24

On Wed, 11 Jul 2007 18:11:35 -0400, Jerry Stuckle <jstucklex@attglobal.net>
wrote:

>What you're probably running into is locking in PostGres. When you're
>inserting or updating rows, PostGres can lock rows or tables so that
>queries don't get partially updated data or data which hasn't been
>committed.
>
>The result is SELECT statements will wait until you COMMIT or ROLLBACK
>the INSERT or UPDATE transaction (or close the connection).
>
>This is standard for virtually all databases.

Virtually all databases now implement (or have the option to implement)
multiversioning. For example, Oracle, MySQL with InnoDB table handlers, and I
believe recent versions of SQL Server, and from a quick search, PostgreSQL has
done for ages.

Multiversioning means that readers don't necessarily get blocked by
uncommitted writers.

The resources used for maintaining the "old" view of the data can be exhausted
of course, but in Oracle that produces an error for the reader ("snapshot too
old") rather than a lock - other databases may react differently.

Concurrent writers for the same data will still obviously contend with each
other.

--
Andy Hassall :: andy@andyh.co.uk :: http://www.andyh.co.uk
http://www.andyhsoftware.co.uk/space :: disk and FTP usage analysis tool

[Back to original message]