There is no row but (XLOCK,ROWLOCK) locked it?

Question

Consider this simple table :

table create statement is :

CREATE TABLE [dbo].[Test_Serializable](
[Id] [int] NOT NULL,
[Name] [nvarchar](50) NOT NULL
)

so there is not any primary key or index.

consider it's emopty and has not any row.I want to Insert this row (1,'nima') but I want to check if there is a row with Id=1 or not.if yes call RAISERROR and if no Insert row.I write this script:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRY
BEGIN TRAN ins

    IF EXISTS(SELECT * FROM Test_Serializable ts WITH(xlock,ROWLOCK) WHERE ts.Id=1)
        RAISERROR(N'Row Exists',16,1);


    INSERT INTO Test_Serializable
    (
        Id,
        [Name]
    )
    VALUES
    (
        1,
        'nima'
    )
   COMMIT TRAN ins
     END TRY
     BEGIN CATCH
        DECLARE @a  NVARCHAR(1000);
        SET @a=ERROR_MESSAGE();
        ROLLBACK TRAN ins
        RAISERROR(@a,16,1);
     END CATCH

this script works fine but there is interesting point.

I run this script from 2 SSMS and step by step run this 2 scripts(in debug mode).Interesting point is however my table has no row but one of the script when reach IF EXIST statement lock the table.

My question is whether (XLOCK,ROWLOCK) locks entire table because there is no row?or it locks phantom row :) !!???

Edit 1)

This is my scenario:

I have a table with for example 6 fields

this is Uniqueness Rules:

1)City_Code + F1_Code are Unique

2)City_Code + F2_Code are Unique

3)City_Code + F3_Code + F4_Code are uinque

the problem is user may want to fill city_code and F1_Code and when it want Insert it in other fileds we must have Empty String or 0 (for numeric fields) value.

If user want to fill City_Code + F3_Code + F4_Code then F1_Code and F2_Code must have Empty String values

How I can check this better?I can't create any Unique Index for every rules

Answer 1

XLOCK is exclusive lock: so as WHERE traverses rows, rows are locked.

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE isn't about duplicates or locking of rows, it simply removes the chance of "Phantom reads". From a locking perspective, it takes range locks (eg all rows between A and B)

So with XLOCK and SERIALIZABLE you lock the table. You want UPDLOCK which isn't exclusive.

With UPDLOCK, this pattern is not safe. Under high load, you will still get duplicate errors because 2 concurrent EXISTS won't find a row, both try to INSERT, one gets a duplicate error.

So just try to INSERT and trap the error:

BEGIN TRAN ins
    INSERT INTO Test_Serializable
    (
        Id,
        [Name]
    )
    VALUES
    (
        1,
        'nima'
    )
   COMMIT TRAN ins
END TRY
BEGIN CATCH
   DECLARE @a  NVARCHAR(1000);
   IF ERROR_NUMBER() = 2627
     RAISERROR(N'Row Exists',16,1);
   ELSE
   BEGIN
     SET @a=ERROR_MESSAGE();
     RAISERROR(@a,16,1);
   END
   ROLLBACK TRAN ins
END CATCH

I've mentioned this before

Edit: to force various uniques for SQL Server 2008

Use filtered indexes

CREATE UNIQUE NONCLUSTERED INDEX IX_UniqueF1 ON (City_Code, F1_Code)
   WHERE F2_Code = '' AND F3_Code = '' AND AND F4_Code = 0;

CREATE UNIQUE NONCLUSTERED INDEX IX_UniqueF1 ON (City_Code, F2_Code)
   WHERE F1_Code = '' AND F3_Code = '' AND AND F4_Code = 0;

CREATE UNIQUE NONCLUSTERED INDEX IX_UniqueF3F4 ON (City_Code, F3_Code, F4_Code)
   WHERE F1_Code = '' AND F2_Code = '';

You can do the same with indexed views on earlier versions

Answer 2

To answer your question, the SERIALIZABLE isolation level performs range locks which would include non-existant rows within the range.

http://msdn.microsoft.com/en-us/library/ms191272.aspx

Key-range locking ensures that the following operations are serializable:

Range scan query

Singleton fetch of nonexistent row

Delete operation

Insert operation