Re: Avoiding any locks in SQL Servers - read and understand....its magic.

Guido Stepken wrote:
> PostgreSQL documentation you find everywhere, search google.

I know where to find the docu, i'm just not able to find what you're claiming, that's why i'm asking.

Some quotes...

Guido Stepken:
"Oracle hasn't really made it perfect, because a write transaction can
block another writing transaction.
PostgreSQL has MVCC, multi versioning concurrency control. No writes, updates, reads block each other."

PostgreSQL Docu 9.1. Introduction:
"...in MVCC locks acquired for querying

(reading) data don’t conflict with locks acquired for writing data, and so reading never blocks
writing and writing never blocks reading."

Guido Stepken:
"Sorry, if forgot to say, that the client is immediately informed, that
the data is written, although the server has them still in work within transaction, which may have been delayed, till other transactions are finished."

PostgreSQL Docu 9.2.1. Read Committed Isolation Level
"However, such a target row may have already been updated (or deleted or
marked for update) by
another concurrent transaction by the time it is found. In this case, the would-be updater will wait for
the first updating transaction to commit or roll back (if it is still in progress). If the first updater rolls
back, then its effects are negated and the second updater can proceed with updating the originally
found row. If the first updater commits, the second updater will ignore the row if the first updater
deleted it, otherwise it will attempt to apply its operation to the updated version of the row."

PostgreSQL Docu 9.2.1. Read Committed Isolation Level
"This behavior makes Read

Committed mode unsuitable for queries that involve complex search conditions."

Guido Stepken:
"I is like magic, programmers never ever have to care about concurrent
write events any longer, because they are time - shifted and rearranged in a manner, as if a lock had been set."

PostgreSQL Docu 9.2.2. Serializable Isolation Level:
"If the first updater

rolls back, then its effects are negated and the serializable transaction can proceed with updating the originally found row. But if the first updater commits (and actually updated or deleted the row, not
just selected it for update) then the serializable transaction will be rolled back with the message
ERROR: Can’t serialize access due to concurrent update because a serializable transaction cannot modify rows changed by other transactions after the serializable
transaction began."

Guido Stepken:
"Throw away all code in your application, which has to do with
locking....;-)) You will not loose any update, no fear."

PostgreSQL Docu 9.2.2. Serializable Isolation Level:
"The Serializable mode provides a rigorous guarantee that each
transaction sees a wholly consistent
view of the database. However, the application has to be prepared to retry transactions when concurrent
updates make it impossible to sustain the illusion of serial execution."

Dieter Received on Tue Aug 19 2003 - 11:55:00 CDT