Hi All,
I have a table which contains some status information.
Table1 (device varchar(200) primary key, NumberOfUsage int)

Now I have a store procedure in which I would like to insert new
devices, if they do not exists. Otherwise I just want to update the
NumberOfUsages. For this I use the merge statement.

Now I have a question to the locking. Is the table 'Table1' already
locked, when the ON condition is checked? I assume yes, because it is
a single statement.

I would like to prevent problems in multi-user scenario between
concurrent users (as described below with normal select/insert
statements).

e.g.: User1: select NumberofUsage from Table1 where device = 'A' (no
lock is here)
        User2: select NumberofUsage from Table1 where device = 'A' (no
lock is here)
        User1: Insert A because it does not exists (lock is here)
        User2: Insert A because it does not exists (lock is here)
Crash because a alreday exists.

Thanks
Marcel

>
> e.g.: User1: select NumberofUsage from Table1 where device = 'A' (no
> lock is here)
>        User2: select NumberofUsage from Table1 where device = 'A' (no
> lock is here)
>        User1: Insert A because it does not exists (lock is here)
>        User2: Insert A because it does not exists (lock is here)
> Crash because a alreday exists.

I assume the read and the mutation are in ONE transaction.
Using Isolation level serializable you are protected from double inserts.
With serializable, the reads will cause predicate locking, where the range 
indicated within the where clause is getting locked. Other users can not 
write in this range for the time the transaction is 'open'.

If the read is not within the transaction, or when using a lower level of 
isolation (snapshot isolation is lower as wel), you are not protected 
against double inserts. You could use constraints to prevent this happening. 
For example a unique constraint on the device, if all the devices are 
unique.

Ben Brugman


Do not assume that a single read statement can not cause a lock. (Even if 
not in a transaction).


>
> Thanks
> Marcel 

> I assume the read and the mutation are in ONE transaction.
> Using Isolation level serializable you are protected from double inserts.
> With serializable, the reads will cause predicate locking, where the range
> indicated within the where clause is getting locked. Other users can not
> write in this range for the time the transaction is 'open'.
>
> If the read is not within the transaction, or when using a lower level of
> isolation (snapshot isolation is lower as wel), you are not protected
> against double inserts. You could use constraints to prevent this happening.
> For example a unique constraint on the device, if all the devices are
> unique.

Thanks.
The code above was just a pseudo piece of code to demonstrate possible
uniw constraints.
But I found a very nice description about my question. It's relly
worth to read.

http://weblogs.sqlteam.com/dang/archive/2009/01/31/UPSERT-Race-Condition-With-MERGE.aspx

Regards
Marcel