On INSERT and UPDATE with NOT EXISTS and the phantom reads problem

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

On INSERT and UPDATE with NOT EXISTS and the phantom reads problem

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2004-05-27 : 10:40:14

Ronald writes "SQL SERVER: 2000 SP 3
OS:Windows XP Pro SP 1
Suppose i have two tables, SRC and DEST


CREATE TABLE SRC
(
    [PK] INT PRIMARY KEY,
    [GROUP] INT,
    [FLAG] BIT
) 
CREATE TABLE DEST
(
    [PK] INT PRIMARY KEY,
    [GROUP] INT,
    [FLAG] BIT
)

Now, if i want to insert into table DEST all record from table SRC where SRC.GROUP = x and SRC.FLAG = 0, and then update all record from table SRC set SRC.FLAG = 1 where SRC.GROUP = x.
The rule here is that there mustn't be any record in table DEST where DEST.GROUP = x and there mustn't be any record in table SRC where SRC.GROUP = x and SRC.FLAG = 1. Now my question:
1. Is my query below an overkill with regard to the rule i state above (NOT EXISTS, can u suggest a better way) ?
2. With the default transaction isolation level of SQL Server set to READ COMMITED, is my query below subject to the phantom reads problem ? if it it then what is the best way to solve it ?
3. Can u explain to me the locking mechanism SQL Server used when executing the query below ?


INSERT INTO DEST
SELECT * FROM SRC 
WHERE SRC.GROUP = x AND SRC.FLAG = 0
AND NOT EXISTS
(
    SELECT * FROM SRC WHERE SRC.GROUP = x AND SRC.FLAG = 1
)
AND NOT EXISTS
(
    SELECT * FROM DEST WHERE DEST.GROUP = x
)
UPDATE SRC
SET SRC.FLAG = 1
WHERE SRC.GROUP = x AND SRC.FLAG = 0
AND NOT EXISTS
(
    SELECT * FROM SRC WHERE SRC.GROUP = x AND SRC.FLAG = 1
)

thanks in advance
Ronald"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources