pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedpredicate locking
4+ messages / 2 participants
[nested] [flat]
* predicate locking
@ 2005-06-12 10:39 Daniel Roth <dan.c.roth@gmail.com>
2005-06-12 15:04 ` Re: predicate locking Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 4+ messages in thread
From: Daniel Roth @ 2005-06-12 10:39 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: predicate locking
2005-06-12 10:39 predicate locking Daniel Roth <dan.c.roth@gmail.com>
@ 2005-06-12 15:04 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-06-13 02:16 ` Re: predicate locking Daniel Roth <dan.c.roth@gmail.com>
0 siblings, 1 reply; 4+ messages in thread
From: Tom Lane @ 2005-06-12 15:04 UTC (permalink / raw)
To: Daniel Roth <dan.c.roth@gmail.com>; +Cc: pgsql-sql
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?
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: predicate locking
2005-06-12 10:39 predicate locking Daniel Roth <dan.c.roth@gmail.com>
2005-06-12 15:04 ` Re: predicate locking Tom Lane <tgl@sss.pgh.pa.us>
@ 2005-06-13 02:16 ` Daniel Roth <dan.c.roth@gmail.com>
2005-06-13 03:04 ` Re: predicate locking Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 4+ messages in thread
From: Daniel Roth @ 2005-06-13 02:16 UTC (permalink / raw)
To: pgsql-sql
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?
>
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: predicate locking
2005-06-12 10:39 predicate locking Daniel Roth <dan.c.roth@gmail.com>
2005-06-12 15:04 ` Re: predicate locking Tom Lane <tgl@sss.pgh.pa.us>
2005-06-13 02:16 ` Re: predicate locking Daniel Roth <dan.c.roth@gmail.com>
@ 2005-06-13 03:04 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 4+ messages in thread
From: Tom Lane @ 2005-06-13 03:04 UTC (permalink / raw)
To: Daniel Roth <dan.c.roth@gmail.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2005-06-13 03:04 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-06-12 10:39 predicate locking Daniel Roth <dan.c.roth@gmail.com>
2005-06-12 15:04 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-06-13 02:16 ` Daniel Roth <dan.c.roth@gmail.com>
2005-06-13 03:04 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox