|
|
Posted by Erland Sommarskog on 06/14/05 01:36
(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/techinfo/productdoc/2000/books.asp
[Back to original message]
|