Thread: predicate locking

Hi

Tom Lane and the postgres help state, "AFAIK, no commercial database
does predicate locking either" (12.2.2.1. Serializable Isolation
versus True Serializability)

But MSSQL (SQL Server) does predicate locking.

From MSDN

"SERIALIZABLE

Places a range lock on the data set, preventing other users from
updating or inserting rows into the data set until the transaction is
complete. This is the most restrictive of the four isolation levels.
Because concurrency is lower, use this option only when necessary.
This option has the same effect as setting HOLDLOCK on all tables in
all SELECT statements in a transaction
"

So does MSSQL (SQL Server) implement predicate locking?

Regards,

Daniel Roth
MCSD.NET

Tom Lane wrote:
> Florian Weimer <fw@deneb.enyo.de> writes:
> > Is this a bug, or is SQLxx serializability defined in different terms?
>
> Strictly speaking, we do not guarantee serializability because we do not
> do predicate locking.  See for example
> http://archives.postgresql.org/pgsql-general/2003-01/msg01581.php
>
> AFAIK, no commercial database does predicate locking either, so we all
> fall short of true serializability.  The usual solution if you need the
> sort of behavior you're talking about is to take a non-sharable write
> lock on the table you want to modify, so that only one transaction can
> do the COUNT/INSERT at a time.
>
>             regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: the planner will ignore your desire to choose an index scan if your
>       joining column's datatypes do not match

Daniel Roth <dan.c.roth@gmail.com> writes:
> But MSSQL (SQL Server) does predicate locking.

> Places a range lock on the data set, preventing other users from
> updating or inserting rows into the data set until the transaction is
> complete. This is the most restrictive of the four isolation levels.

Range locks are a far cry from general predicate locks.
        regards, tom lane

PS: kindly don't email me privately while posting the same message to
the lists.  You think I have time to answer things twice?

Hi Tom

Apologies for my previous double post.

In "12.2.2.1. Serializable Isolation versus True Serializability"

It sates: "To guarantee true mathematical serializability, it is
necessary for a database system to enforce predicate locking, which
means that a transaction cannot insert or modify a row that would have
matched the WHERE condition of a query in another concurrent
transaction"

Now that is exactly whats happens when you use SERIALIZABLE is MSSQL.

So, by the postgres help documentation's definition of predicate
locking, MSSQL does predicate locking.

All I am trying to do is correct the help documentation - 12.2.2.1 "so
far as we are aware no other production DBMS does either."

Regards,

Daniel Roth

On 6/12/05, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Daniel Roth <dan.c.roth@gmail.com> writes:
> > But MSSQL (SQL Server) does predicate locking.
>
> > Places a range lock on the data set, preventing other users from
> > updating or inserting rows into the data set until the transaction is
> > complete. This is the most restrictive of the four isolation levels.
>
> Range locks are a far cry from general predicate locks.
>
>                        regards, tom lane
>
> PS: kindly don't email me privately while posting the same message to
> the lists.  You think I have time to answer things twice?
>

Daniel Roth <dan.c.roth@gmail.com> writes:
> It sates: "To guarantee true mathematical serializability, it is
> necessary for a database system to enforce predicate locking, which
> means that a transaction cannot insert or modify a row that would have
> matched the WHERE condition of a query in another concurrent
> transaction"

> Now that is exactly whats happens when you use SERIALIZABLE is MSSQL.

Only for WHERE conditions that can be expressed as a simple range
constraint.

> All I am trying to do is correct the help documentation - 12.2.2.1 "so
> far as we are aware no other production DBMS does either."

The documentation is not wrong; or at least, what you've quoted does not
show that it is.
        regards, tom lane