Kurt Welgehausen <[EMAIL PROTECTED]> wrote:
"Igor Tandetnik" <[EMAIL PROTECTED]> wrote:
RohitPatel9999
<rohitpatel9999-/[EMAIL PROTECTED]>
wrote:
Scenario 1
If action of some user needs to execute multiple SELECT statements
(read-only, no plan to write), it needs to start explicit
transaction to get consistent reads across read-only multiple
statements.
Q1. Which one is preferable -> BEGIN TRANSACTION or BEGIN
IMMEDIATE ?
BEGIN IMMEDIATE would block other readers. Don't use it unless you
plan
to write.
BEGIN IMMEDIATE blocks writers, not readers. I believe it's
what he wants.
You are correct, BEGIN IMMEDIATE does not block readers. Still, it
acquires a RESERVED lock which is overkill for a read-only transaction.
A SELECT statement acquires a SHARED lock which block writers anyway.
Normally, a writer acquires RESERVED lock, then writes modifications
into the memory cache. Only when a transaction is committed, or when the
memory cache becomes full and the data needs to be spilled to disk, does
the writer need to acquire EXCLUSIVE lock before actually writing to a
physical file.
By having a reader transaction grab RESERVED lock, you would prevent the
writer from even starting its work with the memory cache. This could
hurt performance, for no apparent reason.
Igor Tandetnik
