June 28, 2005 at 10:27 am
We are students from Argentina and we are reading this paper: http://www.cs.duke.edu/~junyang/courses/cps216-2003-spring/papers/berenson-etal-1995.pdf
We have the following example, where In SQL SERVER under REPETABLE READ isolation level it produces WRITE SKEW
WRITE SKEW UNDER REPEATABLE READ ISOLATION LEVEL.
CREATE TABLE PADRE (
COL_PADRE INT);
CREATE TABLE HIJO (
COL_REF_PADRE INT, COL_HIJO VARCHAR (2 ));
INSERT INTO PADRE VALUES ( 1);
INSERT INTO PADRE VALUES ( 2);
INSERT INTO PADRE VALUES ( 3);
INSERT INTO HIJO VALUES ( 1, 'A');
INSERT INTO HIJO VALUES ( 1, 'B');
INSERT INTO HIJO VALUES ( 2, 'C');
COMMIT;
Transacci?n 1:
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN TRAN
select col_padre from padre where col_padre = 3
IF @@rowcount > 0 begin
-- si existe el padre inserto un hijo
Waitfor Delay '000: 00:10'
INSERT INTO hijo VALUES (3,'D')
COMMIT TRAN
END
ELSE ROLLBACK TRAN
Transacci?n 2:
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN TRAN
select col_ref_padre from hijo where col_ref_padre = 3
IF @@rowcount = 0 begin
-- si no existen hijos borro el padre
delete from padre where col_padre = 3
COMMIT TRAN
END
ELSE ROLLBACK TRAN
We keep referential integrity at application level.
In SQL SERVER under REPETABLE READ isolation level it produces WRITE SKEW if we execute transaction 1 fisrt and transaction 2 immediatly, leaving a children row without father.
Your paper says that under REPEATABLE READ is imposible WRITE SKEW. what is wrong in our example above?
thanks in advance
Rodrigo Diaz
Rodolfo Errecart
June 28, 2005 at 11:02 am
The isolation level that will work for you in this situation is Serializable. This can be done with the SET statement or by using With (holdlock)
select col_padre from padre with (holdlock)
where col_padre = 3
The isolation level of Repeatable Read will allow phantom rows. That is it only prevents rows that have already been read from being modified but it does not prevent NEW rows from being inserted.
Serializable will place a range lock (on an index if it exists) and prevent new rows where "col_padre = 3" from being inserted.
Edit: See my corrected post below.
June 28, 2005 at 11:12 am
This also serves as a very good illustration of why DRI is better than "application enforced RI" You are including a bunch of logic to accomplish what SQL Server can do much more efficiently.
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
June 28, 2005 at 11:14 am
Thanks
Yes we try this in SERIALIZABLE and worked out fine but the paper says that the example should work out fine in REPEATABLE READ.
So, is the paper incorrect or is the example incorrect ?
Thank in advance
link to the paper =
A Critique of ANSI SQL Isolation Levels: http://research.microsoft.com/research/pubs/view.aspx?tr_id=5
June 28, 2005 at 11:50 am
Since you are determined to do this the hard way... Repeatable read would work if your WAITFOR statement was AFTER the INSERT statement. There is no need in this case to use serializable.
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
June 28, 2005 at 12:07 pm
thanks dcpeterson
but the example was done just to prove if it is posible to have WRITE SKEW under REPEATABLE READ or not.
This is not a real case where we obviously would create a Foreing Key in the database.
The paper we mentioned says that it is not posible to have WRITE SKEW under REPEATABLE READ but we got a diferent result.
Is really WRITE SKEW what appears in the example mentioned??
Or the paper is incorrect?
What We want to know is if it is posible to have WRITE SKEW under REPEATABLE READ or not.
Thank in advanced
June 28, 2005 at 1:31 pm
OK, I suspect what you are seeing is the effects of the WAITFOR command rather than some problem with the isolation level.
If you eliminate the WAITFOR command in Tran 1 and just do not issue the COMMIT statement, then run Tran 2 you will see the anticipated results.
WAITFOR effectively splits your statement block in two so SQL Server can now execute the two parts of it somewhat independently. Without the WAITFOR the entire statement block would be evaluated, the resources (locks) acquired and then executed as a single block...
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
June 28, 2005 at 3:10 pm
As dcpeterson stated earlier you will see the correct results if you move your "waitfor " to just after the INSERT but this has little to do with Repeatable Read.
With the "WaitFor" after the Insert, transaction 2 is trying to read the uncommitted insert from transaciton 1 and is therefore forced to wait for transaction 1 to commit. At that point Transaction 2 reads the record and gets a count of 1 so it does not do the delete.
With the "WaitFor" before the Insert, transaction 2 has no reason to be blocked so it is able to read from hijo and does not find a row and does its delete from padre (edited) after waiting for transaction 1 to commit. The Repeatable Read isolation level did its job of preventing a modification to a record selected in padre but only until transaction 1 completed. At that point transaction 2 is allowed to delete the row from padre as transaction 1 has ended and the Repeatable Read is no longer in effect. This behavior appears to be correct.
The article you mentioned appears to be correct. Your example is not a true representation of the situation the article depicted.
To take this one more step, transaction 2 is the one causing the problem by taking action based on a read or failure to read a record but not locking the record in the Select before taking action on a record in another table based on the false premise that the record still does not exist. In your example transaction 2 needs to Select from hijo with (holdlock) or set the isolation level to Serializable. Both of which would prevent transaction 1 from doing its insert into hijo. At the same time transaction 2 would be unable to delete from padre because of the Repeatable Read in transaction 1. You would have deadlock situation and one or both transactions would need to be terminated but the data integrity would have been preserved.
June 29, 2005 at 7:11 am
YES YOU ARE RIGHT!!!
This is how Write Skew is defined in the paper
A5B: r1[x]...r2[y]...w1[y]...w2[x]...(c1 and c2 occur)
(Write Skew)
and this is our example
r1[x] = select col_padre from padre where col_padre = 3
r2[y] = select col_ref_padre from hijo where col_ref_padre = 3
w1[y] = INSERT INTO hijo VALUES (3,'D')
w2[x]... = delete from padre where col_padre = 3
The delays are only to force this schueduler´s history (really possible in concurrent transactions)
This is what really happens because of locks of REPEATABLE READ
r1[x]...r2[y]...w1[y]...<<>> … w2[x]...C2 …
the w2 wait for the <<>>
so there is an error (a son without father) but this is not WRITE SKEW
THANKS
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply