Hi
I am having a debate with one of the Postgres developers (Tom Lane)
according to him ms-sql does not implement predicate
locking(SERIALIZABLE).
Where predicate locking is defined as (from the postgres help):
"12.2.2.1. Serializable Isolation versus True Serializability"
It states: "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 I put it to him that that is exactly what SERIALIZABLE does in
MS-SQL.
But his response was "Only for WHERE conditions that can be expressed
as a simple range constraint."
Now is he correct i.e SERIALIZABLE works "Only for WHERE conditions
that can be expressed as a simple range constraint." ?
Regards,
Daniel Roth
MCSD.NETDepends on how you want to quantify it. What is a Range Lock? It is the
demarcation of the begining and the end of records within an Index that
satisfies a particular Where condition. That is for a simple condition that
can be satisfied by an explicit index; however, given a more complex
condition, the range locks can be placed on the Clustered Index that would
satisfy a range of conditions.
However, it is true that there is no "true" predicate locking behavior in
SQL Server. But do not be dismayed, no commercial vendor does so either.
It would have to BLOCK, not lock, upon examination of each subsequently
submitted query to "test" whether or not there may be a logical overlap
between the initiated transaction and any subsequently requested
transaction.
Mathematical Locking is nice, but it is theoretical. A lock is where the
rubber meets the road, and some form of physical lock must be acquired.
That does not mean that it can not be done, but I have failed to see any
real implementations suggested that make practical sense.
Sincerely,
Anthony Thomas
"Daniel Roth" <Daniel Roth@.discussions.microsoft.com> wrote in message
news:7E778689-BEDD-49C4-9ADA-7261222E7593@.microsoft.com...
Hi
I am having a debate with one of the Postgres developers (Tom Lane)
according to him ms-sql does not implement predicate
locking(SERIALIZABLE).
Where predicate locking is defined as (from the postgres help):
"12.2.2.1. Serializable Isolation versus True Serializability"
It states: "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 I put it to him that that is exactly what SERIALIZABLE does in
MS-SQL.
But his response was "Only for WHERE conditions that can be expressed
as a simple range constraint."
Now is he correct i.e SERIALIZABLE works "Only for WHERE conditions
that can be expressed as a simple range constraint." ?
Regards,
Daniel Roth
MCSD.NET
Showing posts with label predicatelocking. Show all posts
Showing posts with label predicatelocking. Show all posts
Friday, March 30, 2012
predicate locking
Hi
I am having a debate with one of the Postgres developers (Tom Lane)
according to him ms-sql does not implement predicate
locking(SERIALIZABLE).
Where predicate locking is defined as (from the postgres help):
"12.2.2.1. Serializable Isolation versus True Serializability"
It states: "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 I put it to him that that is exactly what SERIALIZABLE does in
MS-SQL.
But his response was "Only for WHERE conditions that can be expressed
as a simple range constraint."
Now is he correct i.e SERIALIZABLE works "Only for WHERE conditions
that can be expressed as a simple range constraint." ?
Regards,
Daniel Roth
MCSD.NET
Depends on how you want to quantify it. What is a Range Lock? It is the
demarcation of the begining and the end of records within an Index that
satisfies a particular Where condition. That is for a simple condition that
can be satisfied by an explicit index; however, given a more complex
condition, the range locks can be placed on the Clustered Index that would
satisfy a range of conditions.
However, it is true that there is no "true" predicate locking behavior in
SQL Server. But do not be dismayed, no commercial vendor does so either.
It would have to BLOCK, not lock, upon examination of each subsequently
submitted query to "test" whether or not there may be a logical overlap
between the initiated transaction and any subsequently requested
transaction.
Mathematical Locking is nice, but it is theoretical. A lock is where the
rubber meets the road, and some form of physical lock must be acquired.
That does not mean that it can not be done, but I have failed to see any
real implementations suggested that make practical sense.
Sincerely,
Anthony Thomas
"Daniel Roth" <Daniel Roth@.discussions.microsoft.com> wrote in message
news:7E778689-BEDD-49C4-9ADA-7261222E7593@.microsoft.com...
Hi
I am having a debate with one of the Postgres developers (Tom Lane)
according to him ms-sql does not implement predicate
locking(SERIALIZABLE).
Where predicate locking is defined as (from the postgres help):
"12.2.2.1. Serializable Isolation versus True Serializability"
It states: "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 I put it to him that that is exactly what SERIALIZABLE does in
MS-SQL.
But his response was "Only for WHERE conditions that can be expressed
as a simple range constraint."
Now is he correct i.e SERIALIZABLE works "Only for WHERE conditions
that can be expressed as a simple range constraint." ?
Regards,
Daniel Roth
MCSD.NET
I am having a debate with one of the Postgres developers (Tom Lane)
according to him ms-sql does not implement predicate
locking(SERIALIZABLE).
Where predicate locking is defined as (from the postgres help):
"12.2.2.1. Serializable Isolation versus True Serializability"
It states: "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 I put it to him that that is exactly what SERIALIZABLE does in
MS-SQL.
But his response was "Only for WHERE conditions that can be expressed
as a simple range constraint."
Now is he correct i.e SERIALIZABLE works "Only for WHERE conditions
that can be expressed as a simple range constraint." ?
Regards,
Daniel Roth
MCSD.NET
Depends on how you want to quantify it. What is a Range Lock? It is the
demarcation of the begining and the end of records within an Index that
satisfies a particular Where condition. That is for a simple condition that
can be satisfied by an explicit index; however, given a more complex
condition, the range locks can be placed on the Clustered Index that would
satisfy a range of conditions.
However, it is true that there is no "true" predicate locking behavior in
SQL Server. But do not be dismayed, no commercial vendor does so either.
It would have to BLOCK, not lock, upon examination of each subsequently
submitted query to "test" whether or not there may be a logical overlap
between the initiated transaction and any subsequently requested
transaction.
Mathematical Locking is nice, but it is theoretical. A lock is where the
rubber meets the road, and some form of physical lock must be acquired.
That does not mean that it can not be done, but I have failed to see any
real implementations suggested that make practical sense.
Sincerely,
Anthony Thomas
"Daniel Roth" <Daniel Roth@.discussions.microsoft.com> wrote in message
news:7E778689-BEDD-49C4-9ADA-7261222E7593@.microsoft.com...
Hi
I am having a debate with one of the Postgres developers (Tom Lane)
according to him ms-sql does not implement predicate
locking(SERIALIZABLE).
Where predicate locking is defined as (from the postgres help):
"12.2.2.1. Serializable Isolation versus True Serializability"
It states: "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 I put it to him that that is exactly what SERIALIZABLE does in
MS-SQL.
But his response was "Only for WHERE conditions that can be expressed
as a simple range constraint."
Now is he correct i.e SERIALIZABLE works "Only for WHERE conditions
that can be expressed as a simple range constraint." ?
Regards,
Daniel Roth
MCSD.NET
predicate locking
Hi
I am having a debate with one of the Postgres developers (Tom Lane)
according to him ms-sql does not implement predicate
locking(SERIALIZABLE).
Where predicate locking is defined as (from the postgres help):
"12.2.2.1. Serializable Isolation versus True Serializability"
It states: "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 I put it to him that that is exactly what SERIALIZABLE does is
MS-SQL.
But his response was "Only for WHERE conditions that can be expressed
as a simple range constraint."
Now is he correct i.e SERIALIZABLE works "Only for WHERE conditions
that can be expressed as a simple range constraint." ?
Regards,
Daniel Roth
MCSD.NET(dan.c.roth@.gmail.com) writes:
> I am having a debate with one of the Postgres developers (Tom Lane)
> according to him ms-sql does not implement predicate
> locking(SERIALIZABLE).
> Where predicate locking is defined as (from the postgres help):
> "12.2.2.1. Serializable Isolation versus True Serializability"
> It states: "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 I put it to him that that is exactly what SERIALIZABLE does is
> MS-SQL.
> But his response was "Only for WHERE conditions that can be expressed
> as a simple range constraint."
> Now is he correct i.e SERIALIZABLE works "Only for WHERE conditions
> that can be expressed as a simple range constraint." ?
But what cannot be expressed a range constraint? It would be interesting
to see a counter-example where Tom Lane believes that SQL Server does
not live up the definition of serializable.
Here is one repro that I tried:
In the first query window I ran:
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
go
BEGIN TRANSACTION
SELECT CustomerID FROM Customers C
WHERE NOT EXISTS (SELECT *
FROM Orders O
WHERE O.CustomerID = C.CustomerID)
This query returns two rows: FISSA and PARIS
In the second I ran:
insert Orders (CustomerID, EmployeeID, OrderDate, RequiredDate,
ShippedDate, ShipVia, Freight, ShipName, ShipAddress,
ShipCity, ShipRegion, ShipPostalCode, ShipCountry)
values ('ALBIN', 1, '990817', '990820',
'990819', NULL, 12, NULL, 'adddd',
'London', 'UK', NULL, 'UK')
This query blocked. Note that ALBIN is not a valid CustomerID, so once
I rolled back the first transaction, the INSERT statement failed.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||(dan.c.roth@.gmail.com) writes:
> I am having a debate with one of the Postgres developers (Tom Lane)
> according to him ms-sql does not implement predicate
> locking(SERIALIZABLE).
> Where predicate locking is defined as (from the postgres help):
> "12.2.2.1. Serializable Isolation versus True Serializability"
> It states: "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 I put it to him that that is exactly what SERIALIZABLE does is
> MS-SQL.
> But his response was "Only for WHERE conditions that can be expressed
> as a simple range constraint."
> Now is he correct i.e SERIALIZABLE works "Only for WHERE conditions
> that can be expressed as a simple range constraint." ?
But what cannot be expressed a range constraint? It would be interesting
to see a counter-example where Tom Lane believes that SQL Server does
not live up the definition of serializable.
Here is one repro that I tried:
In the first query window I ran:
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
go
BEGIN TRANSACTION
SELECT CustomerID FROM Customers C
WHERE NOT EXISTS (SELECT *
FROM Orders O
WHERE O.CustomerID = C.CustomerID)
This query returns two rows: FISSA and PARIS
In the second I ran:
insert Orders (CustomerID, EmployeeID, OrderDate, RequiredDate,
ShippedDate, ShipVia, Freight, ShipName, ShipAddress,
ShipCity, ShipRegion, ShipPostalCode, ShipCountry)
values ('ALBIN', 1, '990817', '990820',
'990819', NULL, 12, NULL, 'adddd',
'London', 'UK', NULL, 'UK')
This query blocked. Note that ALBIN is not a valid CustomerID, so once
I rolled back the first transaction, the INSERT statement failed.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks Erland.
Tom had me worried for a second.
Regards,
Daniel Roth
MCSD.NET
Erland Sommarskog wrote:
> (dan.c.roth@.gmail.com) writes:
> > I am having a debate with one of the Postgres developers (Tom Lane)
> > according to him ms-sql does not implement predicate
> > locking(SERIALIZABLE).
> > Where predicate locking is defined as (from the postgres help):
> > "12.2.2.1. Serializable Isolation versus True Serializability"
> > It states: "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 I put it to him that that is exactly what SERIALIZABLE does is
> > MS-SQL.
> > But his response was "Only for WHERE conditions that can be expressed
> > as a simple range constraint."
> > Now is he correct i.e SERIALIZABLE works "Only for WHERE conditions
> > that can be expressed as a simple range constraint." ?
> But what cannot be expressed a range constraint? It would be interesting
> to see a counter-example where Tom Lane believes that SQL Server does
> not live up the definition of serializable.
> Here is one repro that I tried:
> In the first query window I ran:
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
> go
> BEGIN TRANSACTION
> SELECT CustomerID FROM Customers C
> WHERE NOT EXISTS (SELECT *
> FROM Orders O
> WHERE O.CustomerID = C.CustomerID)
> This query returns two rows: FISSA and PARIS
> In the second I ran:
> insert Orders (CustomerID, EmployeeID, OrderDate, RequiredDate,
> ShippedDate, ShipVia, Freight, ShipName, ShipAddress,
> ShipCity, ShipRegion, ShipPostalCode, ShipCountry)
> values ('ALBIN', 1, '990817', '990820',
> '990819', NULL, 12, NULL, 'adddd',
> 'London', 'UK', NULL, 'UK')
> This query blocked. Note that ALBIN is not a valid CustomerID, so once
> I rolled back the first transaction, the INSERT statement failed.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks Erland.
Tom had me worried for a second.
Regards,
Daniel Roth
MCSD.NET
Erland Sommarskog wrote:
> (dan.c.roth@.gmail.com) writes:
> > I am having a debate with one of the Postgres developers (Tom Lane)
> > according to him ms-sql does not implement predicate
> > locking(SERIALIZABLE).
> > Where predicate locking is defined as (from the postgres help):
> > "12.2.2.1. Serializable Isolation versus True Serializability"
> > It states: "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 I put it to him that that is exactly what SERIALIZABLE does is
> > MS-SQL.
> > But his response was "Only for WHERE conditions that can be expressed
> > as a simple range constraint."
> > Now is he correct i.e SERIALIZABLE works "Only for WHERE conditions
> > that can be expressed as a simple range constraint." ?
> But what cannot be expressed a range constraint? It would be interesting
> to see a counter-example where Tom Lane believes that SQL Server does
> not live up the definition of serializable.
> Here is one repro that I tried:
> In the first query window I ran:
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
> go
> BEGIN TRANSACTION
> SELECT CustomerID FROM Customers C
> WHERE NOT EXISTS (SELECT *
> FROM Orders O
> WHERE O.CustomerID = C.CustomerID)
> This query returns two rows: FISSA and PARIS
> In the second I ran:
> insert Orders (CustomerID, EmployeeID, OrderDate, RequiredDate,
> ShippedDate, ShipVia, Freight, ShipName, ShipAddress,
> ShipCity, ShipRegion, ShipPostalCode, ShipCountry)
> values ('ALBIN', 1, '990817', '990820',
> '990819', NULL, 12, NULL, 'adddd',
> 'London', 'UK', NULL, 'UK')
> This query blocked. Note that ALBIN is not a valid CustomerID, so once
> I rolled back the first transaction, the INSERT statement failed.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp|||As a basic transaction question, why did the second one block if the
first hadn't changed any rows and the query was done executing?|||pb648174 (google@.webpaul.net) writes:
> As a basic transaction question, why did the second one block if the
> first hadn't changed any rows and the query was done executing?
Because that transaction had isolation level serializable. This means
that if you resubmit a query with in the transaction, you should
essentially get the same results. Therefore writes must be blocked
until the reader transaction commits.
In the default isolation level "Read Committed", the same query in
the same transaction can give different results, and thus locks can
be released once data has been read.
There is also "Repeatable Read" which is between Serializable and Read
Committed. With Repeatable Read, reading the same row twice should yield
the same result. However, a for a range query that is resubmitted newly
entered rows may appear, so called "phantom inserts".
Finally there is "read uncommitted" in which no locks are taken for
reading at all, and thus you can get to read uncommitted data.
And, oh, in SQL 2005, there is a new level called snapshot. The result
is essentially the same as in serializable, but instead of locking
rows, old versions of changed rows are added to a snapshot. In this
way readers do not block writers. Also, the reader gets a wholly
consistent view of the database at a certain point in time, which you
in fact cannot achieve with Serializable.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||It seems that the serializable should just not "get" the updated rows
when it does queries, not lock the table for everyone else... It seems
like that is what the snapshot is I guess.|||pb648174 (google@.webpaul.net) writes:
> It seems that the serializable should just not "get" the updated rows
> when it does queries, not lock the table for everyone else... It seems
> like that is what the snapshot is I guess.
Without a snapshot like there is an Oracle and Rdb, it's difficult to
do serializable without blocking writers. A snapshot effectively means
that there is more than once instance of the data, and in SQL Server
there is only one single.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
Subscribe to:
Posts (Atom)