Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

I need to control duplicate row

6 posts in General Discussion Last posting was on 2011-03-10 19:10:28.0Z
RGS Posted on 2011-03-04 22:52:23.0Z
Sender: 660c.4d716a63.1804289383@sybase.com
From: RGS
Newsgroups: sybase.public.ase.general
Subject: I need to control duplicate row
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4d716d27.665d.1681692777@sybase.com>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 4 Mar 2011 14:52:23 -0800
X-Trace: forums-1-dub 1299279143 10.22.241.41 (4 Mar 2011 14:52:23 -0800)
X-Original-Trace: 4 Mar 2011 14:52:23 -0800, 10.22.241.41
Lines: 30
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30020
Article PK: 79249

Hi!

In a stored procedure, I need to make a validate before I
insert a row in a table

My program looks like:

if not exists (select * from T1 where C1 = value1)
insert into T1 values (values1)

But, if I have two users executing the same program in the
same time and if both users execute the SELECT command:

if not exists (select * from T1 where C1 = value1)

But will apply a SHARED LOCK and both will not find any row
and both will insert their rows and this is incorrect for me

One way is create a unique index, is there other way to
control this?

I traid with:

if not exists (select * from T1 where C1 = value1 at
isolation level 3)

But it didn't work....


Thanks!!!


"Mark A. Parsons" <iron_horse Posted on 2011-03-05 00:05:14.0Z
From: "Mark A. Parsons" <iron_horse@no_spamola.compuserve.com>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9.2.13) Gecko/20101207 Thunderbird/3.1.7
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: I need to control duplicate row
References: <4d716d27.665d.1681692777@sybase.com>
In-Reply-To: <4d716d27.665d.1681692777@sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4d717e3a$1@forums-1-dub>
Date: 4 Mar 2011 16:05:14 -0800
X-Trace: forums-1-dub 1299283514 10.22.241.152 (4 Mar 2011 16:05:14 -0800)
X-Original-Trace: 4 Mar 2011 16:05:14 -0800, vip152.sybase.com
Lines: 104
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30021
Article PK: 79250

There are a few approaches that come to mind ...

1 - use of a unique index is the easiest to implement, should be the most efficient to process, and should scale well;
is there a reason why you're looking for a solution other than a unique index?

2 - first user has to block the second user's SELECT; one way to do this would be for the first user to obtain an
exclusive lock on the table thus blocking the second user from accessing the table until the first user has completed
the conditional insert, eg:

====================
begin tran
lock table T1 in exclusive move
if not exists(select * from T1 where C1 = value1)
insert into T1 values (value1)
commit tran
====================

'course, this doesn't scale well if you've got lots of users trying to insert different values at the same time, ie,
you'll end up with a lot of unnecessary blocking for users with different values

3 - a variation on blocking the other user consists of using a trigger; since the trigger code is running inside of a
transaction you can block the other user with the exclusive lock you're holding while inside the trigger, eg:

====================
create trigger T1_i on T1 for insert
as
if (select count(*) from inserted i, T1 t where t.C1 = i.C1) > 1
begin
print 'duplicate record found, rolling back'
rollback trigger
end
====================

Again, this may not scale well for multiple users inserting different values ... depends on how you design your table
and any indexes (eg, datarows locking will help minimize lock contention at the data level, while an index will probably
be needed to keep from performing a table scan which could block on other users' insert activity).

It's also possible that you could run into deadlocks with this scenario, which would certainly insure one user's insert
doesn't succeed, but a relying on a deadlock is rather inefficient.

-----------

There may be other solutions but we'd need to know more about the bigger picture.

Why/How do 2 users end up processing the same value?

Where does this duplicate value come from?

Could the users be forced to work with unique values to start with?

----------

As for the isolation level 3 query not working ... what do you mean it didn't work?

Did you get duplicates?

Did you get deadlocks?

Did something else happen?

The following generates a deadlock in a scenario where T1 is configured to use datarows locking, no rows exist with
C1=100, and multiple users attempt to insert C1=100 at the same time:

===================
declare @c1 int
begin tran
select @c1=C1 from T1 where C1 = 100 at isolation 3
if @c1 != 100
insert T1 values (100)
commit tran
===================

On 03/04/2011 17:52, RGS wrote:
> Hi!
>
> In a stored procedure, I need to make a validate before I
> insert a row in a table
>
> My program looks like:
>
> if not exists (select * from T1 where C1 = value1)
> insert into T1 values (values1)
>
> But, if I have two users executing the same program in the
> same time and if both users execute the SELECT command:
>
> if not exists (select * from T1 where C1 = value1)
>
> But will apply a SHARED LOCK and both will not find any row
> and both will insert their rows and this is incorrect for me
>
> One way is create a unique index, is there other way to
> control this?
>
> I traid with:
>
> if not exists (select * from T1 where C1 = value1 at
> isolation level 3)
>
> But it didn't work....
>
>
> Thanks!!!


RGS Posted on 2011-03-07 17:12:57.0Z
Sender: 6bf9.4d750e00.1804289383@sybase.com
From: RGS
Newsgroups: sybase.public.ase.general
Subject: Re: I need to control duplicate row
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4d751218.6c5b.1681692777@sybase.com>
References: <4d717e3a$1@forums-1-dub>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 7 Mar 2011 09:12:57 -0800
X-Trace: forums-1-dub 1299517977 10.22.241.41 (7 Mar 2011 09:12:57 -0800)
X-Original-Trace: 7 Mar 2011 09:12:57 -0800, 10.22.241.41
Lines: 149
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30024
Article PK: 79252

Thanks a lot Mark

Only one thing, in the solution with a trigger:

> create trigger T1_i on T1 for insert
> as
> if (select count(*) from inserted i, T1 t where t.C1 =
> i.C1) > 1 begin
> print 'duplicate record found, rolling back'
> rollback trigger
> end

What happens if two users execute in the same time the
trigger in the line:

> if (select count(*) from inserted i, T1 t where t.C1 =
> i.C1) > 1 begin

Is possible that this condition will return TRUE for both
users (this is wrong for me) or only will return TRUE for
one users?

thanks!


> There are a few approaches that come to mind ...
>
> 1 - use of a unique index is the easiest to implement,
> should be the most efficient to process, and should scale
> well; is there a reason why you're looking for a solution
> other than a unique index?
>
> 2 - first user has to block the second user's SELECT; one
> way to do this would be for the first user to obtain an
> exclusive lock on the table thus blocking the second user
> from accessing the table until the first user has
> completed the conditional insert, eg:
>
> ====================
> begin tran
> lock table T1 in exclusive move
> if not exists(select * from T1 where C1 = value1)
> insert into T1 values (value1)
> commit tran
> ====================
>
> 'course, this doesn't scale well if you've got lots of
> users trying to insert different values at the same time,
> ie, you'll end up with a lot of unnecessary blocking for
> users with different values
>
> 3 - a variation on blocking the other user consists of
> using a trigger; since the trigger code is running inside
> of a transaction you can block the other user with the
> exclusive lock you're holding while inside the trigger,
> eg:
>
> ====================
> create trigger T1_i on T1 for insert
> as
> if (select count(*) from inserted i, T1 t where t.C1 =
> i.C1) > 1 begin
> print 'duplicate record found, rolling back'
> rollback trigger
> end
> ====================
>
> Again, this may not scale well for multiple users
> inserting different values ... depends on how you design
> your table and any indexes (eg, datarows locking will
> help minimize lock contention at the data level, while an
> index will probably be needed to keep from performing a
> table scan which could block on other users' insert
> activity).
>
> It's also possible that you could run into deadlocks with
> this scenario, which would certainly insure one user's
> insert doesn't succeed, but a relying on a deadlock is
> rather inefficient.
>
> -----------
>
> There may be other solutions but we'd need to know more
> about the bigger picture.
>
> Why/How do 2 users end up processing the same value?
>
> Where does this duplicate value come from?
>
> Could the users be forced to work with unique values to
> start with?
>
> ----------
>
> As for the isolation level 3 query not working ... what do
> you mean it didn't work?
>
> Did you get duplicates?
>
> Did you get deadlocks?
>
> Did something else happen?
>
> The following generates a deadlock in a scenario where T1
> is configured to use datarows locking, no rows exist with
> C1=100, and multiple users attempt to insert C1=100 at the
> same time:
>
> ===================
> declare @c1 int
> begin tran
> select @c1=C1 from T1 where C1 = 100 at isolation 3
> if @c1 != 100
> insert T1 values (100)
> commit tran
> ===================
>
>
> On 03/04/2011 17:52, RGS wrote:
> > Hi!
> >
> > In a stored procedure, I need to make a validate before
> > I insert a row in a table
> >
> > My program looks like:
> >
> > if not exists (select * from T1 where C1 = value1)
> > insert into T1 values (values1)
> >
> > But, if I have two users executing the same program in
> > the same time and if both users execute the SELECT
> command: >
> > if not exists (select * from T1 where C1 = value1)
> >
> > But will apply a SHARED LOCK and both will not find any
> > row and both will insert their rows and this is
> incorrect for me >
> > One way is create a unique index, is there other way to
> > control this?
> >
> > I traid with:
> >
> > if not exists (select * from T1 where C1 = value1 at
> > isolation level 3)
> >
> > But it didn't work....
> >
> >
> > Thanks!!!


"Mark A. Parsons" <iron_horse Posted on 2011-03-07 21:14:54.0Z
From: "Mark A. Parsons" <iron_horse@no_spamola.compuserve.com>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9.2.13) Gecko/20101207 Thunderbird/3.1.7
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: I need to control duplicate row
References: <4d717e3a$1@forums-1-dub> <4d751218.6c5b.1681692777@sybase.com>
In-Reply-To: <4d751218.6c5b.1681692777@sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4d754ace$1@forums-1-dub>
Date: 7 Mar 2011 13:14:54 -0800
X-Trace: forums-1-dub 1299532494 10.22.241.152 (7 Mar 2011 13:14:54 -0800)
X-Original-Trace: 7 Mar 2011 13:14:54 -0800, vip152.sybase.com
Lines: 182
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30028
Article PK: 79256

Well, I'm thinking the trigger is either going to abort on dup's or it's going to cause deadlocks ... it all depends on
table/index design ... [I wrote my previous response with a simple APL/no-index table in mind.]

Since the trigger code will be operating under the same transaction as the INSERT, each insert will have an exclusive
lock of some type, which in turn means the opposite session won't be able to complete its SELECT/count until said
exclusive lock is released.

You can run a battery of tests with various combos (locking scheme, partitioned table, (no) index) to figure out what
happens; here are a few:

allpages, no index, partitioned : rows on different partitions, deadlock

allpages, no index, no partitions : rows on same page, first insert blocks second insert, abort due to dup row

allpages, no index, no partitions : different page, deadlock

datapages, no index, partitioned/no partitions : different pages, deadlock

datarows, no index, partitioned/no partitions : different rows, deadlock

I can't think of a situation where the trigger would allow dup's ... though dup's would certainly be doable if the
trigger were to perform dirty reads, or use the 'readpast' attribute against a DOL table ("Duh, Mark!" ?).

I wouldn't necessarily recommend the trigger method (regardless of deadlocks/abort on dup) as it's a bunch of processing
that occurs *after* the INSERT is performed, which in turn means someone's INSERT will have to be rolled back (deadlock
or dup), which means extra work for the dataserver.

A solution that eliminates the dup's before they occur would tend to be my choice.

On 03/07/2011 12:12, RGS wrote:
> Thanks a lot Mark
>
> Only one thing, in the solution with a trigger:
>
>> create trigger T1_i on T1 for insert
>> as
>> if (select count(*) from inserted i, T1 t where t.C1 =
>> i.C1)> 1 begin
>> print 'duplicate record found, rolling back'
>> rollback trigger
>> end
>
> What happens if two users execute in the same time the
> trigger in the line:
>
>> if (select count(*) from inserted i, T1 t where t.C1 =
>> i.C1)> 1 begin
>
> Is possible that this condition will return TRUE for both
> users (this is wrong for me) or only will return TRUE for
> one users?
>
> thanks!
>
>
>> There are a few approaches that come to mind ...
>>
>> 1 - use of a unique index is the easiest to implement,
>> should be the most efficient to process, and should scale
>> well; is there a reason why you're looking for a solution
>> other than a unique index?
>>
>> 2 - first user has to block the second user's SELECT; one
>> way to do this would be for the first user to obtain an
>> exclusive lock on the table thus blocking the second user
>> from accessing the table until the first user has
>> completed the conditional insert, eg:
>>
>> ====================
>> begin tran
>> lock table T1 in exclusive move
>> if not exists(select * from T1 where C1 = value1)
>> insert into T1 values (value1)
>> commit tran
>> ====================
>>
>> 'course, this doesn't scale well if you've got lots of
>> users trying to insert different values at the same time,
>> ie, you'll end up with a lot of unnecessary blocking for
>> users with different values
>>
>> 3 - a variation on blocking the other user consists of
>> using a trigger; since the trigger code is running inside
>> of a transaction you can block the other user with the
>> exclusive lock you're holding while inside the trigger,
>> eg:
>>
>> ====================
>> create trigger T1_i on T1 for insert
>> as
>> if (select count(*) from inserted i, T1 t where t.C1 =
>> i.C1)> 1 begin
>> print 'duplicate record found, rolling back'
>> rollback trigger
>> end
>> ====================
>>
>> Again, this may not scale well for multiple users
>> inserting different values ... depends on how you design
>> your table and any indexes (eg, datarows locking will
>> help minimize lock contention at the data level, while an
>> index will probably be needed to keep from performing a
>> table scan which could block on other users' insert
>> activity).
>>
>> It's also possible that you could run into deadlocks with
>> this scenario, which would certainly insure one user's
>> insert doesn't succeed, but a relying on a deadlock is
>> rather inefficient.
>>
>> -----------
>>
>> There may be other solutions but we'd need to know more
>> about the bigger picture.
>>
>> Why/How do 2 users end up processing the same value?
>>
>> Where does this duplicate value come from?
>>
>> Could the users be forced to work with unique values to
>> start with?
>>
>> ----------
>>
>> As for the isolation level 3 query not working ... what do
>> you mean it didn't work?
>>
>> Did you get duplicates?
>>
>> Did you get deadlocks?
>>
>> Did something else happen?
>>
>> The following generates a deadlock in a scenario where T1
>> is configured to use datarows locking, no rows exist with
>> C1=100, and multiple users attempt to insert C1=100 at the
>> same time:
>>
>> ===================
>> declare @c1 int
>> begin tran
>> select @c1=C1 from T1 where C1 = 100 at isolation 3
>> if @c1 != 100
>> insert T1 values (100)
>> commit tran
>> ===================
>>
>>
>> On 03/04/2011 17:52, RGS wrote:
>>> Hi!
>>>
>>> In a stored procedure, I need to make a validate before
>>> I insert a row in a table
>>>
>>> My program looks like:
>>>
>>> if not exists (select * from T1 where C1 = value1)
>>> insert into T1 values (values1)
>>>
>>> But, if I have two users executing the same program in
>>> the same time and if both users execute the SELECT
>> command:>
>>> if not exists (select * from T1 where C1 = value1)
>>>
>>> But will apply a SHARED LOCK and both will not find any
>>> row and both will insert their rows and this is
>> incorrect for me>
>>> One way is create a unique index, is there other way to
>>> control this?
>>>
>>> I traid with:
>>>
>>> if not exists (select * from T1 where C1 = value1 at
>>> isolation level 3)
>>>
>>> But it didn't work....
>>>
>>>
>>> Thanks!!!


RGS Posted on 2011-03-10 19:10:28.0Z
Sender: 6bf9.4d750e00.1804289383@sybase.com
From: RGS
Newsgroups: sybase.public.ase.general
Subject: Re: I need to control duplicate row
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4d792224.104d.1681692777@sybase.com>
References: <4d754ace$1@forums-1-dub>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 10 Mar 2011 11:10:28 -0800
X-Trace: forums-1-dub 1299784228 10.22.241.41 (10 Mar 2011 11:10:28 -0800)
X-Original-Trace: 10 Mar 2011 11:10:28 -0800, 10.22.241.41
Lines: 200
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30035
Article PK: 79264

Thanks a lot!!!

> Well, I'm thinking the trigger is either going to abort on
> dup's or it's going to cause deadlocks ... it all depends
> on table/index design ... [I wrote my previous response
> with a simple APL/no-index table in mind.]
>
> Since the trigger code will be operating under the same
> transaction as the INSERT, each insert will have an
> exclusive lock of some type, which in turn means the
> opposite session won't be able to complete its
> SELECT/count until said exclusive lock is released.
>
> You can run a battery of tests with various combos
> (locking scheme, partitioned table, (no) index) to figure
> out what happens; here are a few:
>
> allpages, no index, partitioned : rows on different
> partitions, deadlock
>
> allpages, no index, no partitions : rows on same page,
> first insert blocks second insert, abort due to dup row
>
> allpages, no index, no partitions : different page,
> deadlock
>
> datapages, no index, partitioned/no partitions : different
> pages, deadlock
>
> datarows, no index, partitioned/no partitions : different
> rows, deadlock
>
> I can't think of a situation where the trigger would allow
> dup's ... though dup's would certainly be doable if the
> trigger were to perform dirty reads, or use the 'readpast'
> attribute against a DOL table ("Duh, Mark!" ?).
>
> I wouldn't necessarily recommend the trigger method
> (regardless of deadlocks/abort on dup) as it's a bunch of
> processing that occurs *after* the INSERT is performed,
> which in turn means someone's INSERT will have to be
> rolled back (deadlock or dup), which means extra work for
> the dataserver.
>
> A solution that eliminates the dup's before they occur
> would tend to be my choice.
>
>
>
>
> On 03/07/2011 12:12, RGS wrote:
> > Thanks a lot Mark
> >
> > Only one thing, in the solution with a trigger:
> >
> >> create trigger T1_i on T1 for insert
> >> as
> >> if (select count(*) from inserted i, T1 t where t.C1 =
> >> i.C1)> 1 begin
> >> print 'duplicate record found, rolling back'
> >> rollback trigger
> >> end
> >
> > What happens if two users execute in the same time the
> > trigger in the line:
> >
> >> if (select count(*) from inserted i, T1 t where t.C1 =
> >> i.C1)> 1 begin
> >
> > Is possible that this condition will return TRUE for
> > both users (this is wrong for me) or only will return
> > TRUE for one users?
> >
> > thanks!
> >
> >
> >> There are a few approaches that come to mind ...
> >>
> >> 1 - use of a unique index is the easiest to implement,
> >> should be the most efficient to process, and should
> scale >> well; is there a reason why you're looking for a
> solution >> other than a unique index?
> >>
> >> 2 - first user has to block the second user's SELECT;
> one >> way to do this would be for the first user to
> obtain an >> exclusive lock on the table thus blocking the
> second user >> from accessing the table until the first
> user has >> completed the conditional insert, eg:
> >>
> >> ====================
> >> begin tran
> >> lock table T1 in exclusive move
> >> if not exists(select * from T1 where C1 = value1)
> >> insert into T1 values (value1)
> >> commit tran
> >> ====================
> >>
> >> 'course, this doesn't scale well if you've got lots of
> >> users trying to insert different values at the same
> time, >> ie, you'll end up with a lot of unnecessary
> blocking for >> users with different values
> >>
> >> 3 - a variation on blocking the other user consists of
> >> using a trigger; since the trigger code is running
> inside >> of a transaction you can block the other user
> with the >> exclusive lock you're holding while inside the
> trigger, >> eg:
> >>
> >> ====================
> >> create trigger T1_i on T1 for insert
> >> as
> >> if (select count(*) from inserted i, T1 t where t.C1 =
> >> i.C1)> 1 begin
> >> print 'duplicate record found, rolling back'
> >> rollback trigger
> >> end
> >> ====================
> >>
> >> Again, this may not scale well for multiple users
> >> inserting different values ... depends on how you
> design >> your table and any indexes (eg, datarows
> locking will >> help minimize lock contention at the data
> level, while an >> index will probably be needed to keep
> from performing a >> table scan which could block on other
> users' insert >> activity).
> >>
> >> It's also possible that you could run into deadlocks
> with >> this scenario, which would certainly insure one
> user's >> insert doesn't succeed, but a relying on a
> deadlock is >> rather inefficient.
> >>
> >> -----------
> >>
> >> There may be other solutions but we'd need to know more
> >> about the bigger picture.
> >>
> >> Why/How do 2 users end up processing the same value?
> >>
> >> Where does this duplicate value come from?
> >>
> >> Could the users be forced to work with unique values to
> >> start with?
> >>
> >> ----------
> >>
> >> As for the isolation level 3 query not working ... what
> do >> you mean it didn't work?
> >>
> >> Did you get duplicates?
> >>
> >> Did you get deadlocks?
> >>
> >> Did something else happen?
> >>
> >> The following generates a deadlock in a scenario where
> T1 >> is configured to use datarows locking, no rows exist
> with >> C1=100, and multiple users attempt to insert
> C1=100 at the >> same time:
> >>
> >> ===================
> >> declare @c1 int
> >> begin tran
> >> select @c1=C1 from T1 where C1 = 100 at isolation 3
> >> if @c1 != 100
> >> insert T1 values (100)
> >> commit tran
> >> ===================
> >>
> >>
> >> On 03/04/2011 17:52, RGS wrote:
> >>> Hi!
> >>>
> >>> In a stored procedure, I need to make a validate
> before >>> I insert a row in a table
> >>>
> >>> My program looks like:
> >>>
> >>> if not exists (select * from T1 where C1 = value1)
> >>> insert into T1 values (values1)
> >>>
> >>> But, if I have two users executing the same program in
> >>> the same time and if both users execute the SELECT
> >> command:>
> >>> if not exists (select * from T1 where C1 = value1)
> >>>
> >>> But will apply a SHARED LOCK and both will not find
> any >>> row and both will insert their rows and this is
> >> incorrect for me>
> >>> One way is create a unique index, is there other way
> to >>> control this?
> >>>
> >>> I traid with:
> >>>
> >>> if not exists (select * from T1 where C1 = value1 at
> >>> isolation level 3)
> >>>
> >>> But it didn't work....
> >>>
> >>>
> >>> Thanks!!!


Bret Halford Posted on 2011-03-07 18:56:46.0Z
From: Bret Halford <bret@sybase.com>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9.2.14) Gecko/20110221 Thunderbird/3.1.8
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: I need to control duplicate row
References: <4d717e3a$1@forums-1-dub> <4d751218.6c5b.1681692777@sybase.com>
In-Reply-To: <4d751218.6c5b.1681692777@sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4d752a6e$1@forums-1-dub>
Date: 7 Mar 2011 10:56:46 -0800
X-Trace: forums-1-dub 1299524206 10.22.241.152 (7 Mar 2011 10:56:46 -0800)
X-Original-Trace: 7 Mar 2011 10:56:46 -0800, vip152.sybase.com
Lines: 105
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30027
Article PK: 79263

If you use an "INSERT SELECT WHERE NOT EXISTS" construct, ASE
will use an exclusive intent lock from the start, while with
"IF NOT EXISTS (SELECT) INSERT" ASE starts with a shared intent
lock for the select, releases it, and only then gets the exclusive
lock.

Note: traceflag 1212 traces lock requests and releases

create table t1 (c1 int, c2 int identity)
go
1> dbcc traceon(3604,1212)
2> go
1> insert into t1 (c1) select 1
2> where not exists (
3> select * from t1 where c1 = 1
4> )
5> go
lock_multiple exclusive intent <--------------------------------------
db=2 obj=241650882 splk=0xf2e2a000 suff=0
fileindex=305 (seq_lock.c) lineno=1642 LOCK_GRANTED
lock_do_logical shared page db=2 obj=241650882 pg=841 row=0 ctx=0x0 suff=0
pspid=19 fileindex=322 (startscan.c) lineno=1879 LOCK_GRANTED
lock_release shared page db=2 obj=241650882 pg=841 row=0 splk=0xf2e2a640
ctx=0x0
suff=0 fileindex=322 (startscan.c) lineno=1879
lm_internal_ctxchain_release
exclusive intent obj=241650882 db=2 ctx=0x1 suff=0
(0 rows affected)

1> if not exists
2> (select * from t1 where c1 = 1)
3> insert into t1
4> values (1)
5> go
lock_do_logical shared intent <--------------------------------------
db=2 obj=3 pg=0 row=0 ctx=0x0 suff=0 pspid=19
fileindex=53 (colnames.c) lineno=5746 LOCK_GRANTED
lock_do_logical shared row db=2 obj=3 pg=46 row=55 ctx=0x0 suff=0 pspid=19
fileindex=26 (bt_getnext.c) lineno=2767 LOCK_GRANTED
lock_release shared row db=2 obj=3 pg=46 row=55 splk=0xf2e2a200 ctx=0x0
suff=0
fileindex=26 (bt_getnext.c) lineno=2767
lock_release shared intent db=2 obj=0 pg=3 row=0 splk=0xf2e29f80 ctx=0x0
suff=0
fileindex=53 (colnames.c) lineno=5746
lock_do_logical shared intent db=2 obj=3 pg=0 row=0 ctx=0x0 suff=0 pspid=19
fileindex=53 (colnames.c) lineno=5746 LOCK_GRANTED
lock_do_logical shared row db=2 obj=3 pg=46 row=55 ctx=0x0 suff=0 pspid=19
fileindex=26 (bt_getnext.c) lineno=2767 LOCK_GRANTED
lock_do_logical shared row db=2 obj=3 pg=46 row=56 ctx=0x0 suff=0 pspid=19
fileindex=26 (bt_getnext.c) lineno=2767 LOCK_GRANTED
lock_release shared row db=2 obj=3 pg=46 row=55 splk=0xf2e2a200 ctx=0x0
suff=0
fileindex=26 (bt_getnext.c) lineno=2767
lock_release shared row db=2 obj=3 pg=46 row=56 splk=0xf2e2a200 ctx=0x0
suff=0
fileindex=26 (bt_getnext.c) lineno=2767
lock_release shared intent db=2 obj=0 pg=3 row=0 splk=0xf2e29f80 ctx=0x0
suff=0
fileindex=53 (colnames.c) lineno=5746
lock_do_logical shared intent db=2 obj=24 pg=0 row=0 ctx=0x0 suff=0 pspid=19
fileindex=326 (statsapi.c) lineno=658 LOCK_GRANTED
lock_do_logical shared row db=2 obj=24 pg=529 row=0 ctx=0x0 suff=0 pspid=19
fileindex=26 (bt_getnext.c) lineno=2767 LOCK_GRANTED
lock_release shared row <---------------------------------------------
db=2 obj=24 pg=529 row=0 splk=0xf2e2a5c0 ctx=0x0 suff=0
fileindex=26 (bt_getnext.c) lineno=2767
lock_do_logical exclusive intent <------------------------------------
db=2 obj=24 pg=0 row=0 ctx=0x1 suff=0 pspid=19
fileindex=326 (statsapi.c) lineno=8021 LOCK_GRANTED
lock_do_logical update row db=2 obj=24 pg=529 row=0 ctx=0x1 suff=0 pspid=19
fileindex=26 (bt_getnext.c) lineno=2767 LOCK_GRANTED
lock_do_logical exclusive row db=2 obj=24 pg=529 row=0 ctx=0x11 suff=0
pspid=19
fileindex=93 (dol_delete.c) lineno=500 LOCK_GRANTED
lock_do_logical exclusive row db=2 obj=24 pg=529 row=0 ctx=0x1 suff=0
pspid=19
fileindex=97 (dol_insert.c) lineno=1063 LOCK_NOTNEEDED
lock_do_logical exclusive row db=2 obj=24 pg=529 row=3 ctx=0x1 suff=0
pspid=19
fileindex=97 (dol_insert.c) lineno=1063 LOCK_GRANTED
lock_do_logical shared row db=2 obj=24 pg=529 row=0 ctx=0x1 suff=0 pspid=19
fileindex=27 (bt_insdel_main.c) lineno=2159 LOCK_NOTNEEDED
lock_do_logical exclusive row db=2 obj=24 pg=529 row=3 ctx=0x101 suff=0
pspid=19
fileindex=27 (bt_insdel_main.c) lineno=2192 LOCK_NOTNEEDED
lm_internal_ctxchain_release
exclusive intent obj=24 db=2 ctx=0x1 suff=0
update row row=0 pg=529 obj=24 db=2 ctx=0x1 suff=0
exclusive row row=0 pg=529 obj=24 db=2 ctx=0x11 suff=0
exclusive row row=3 pg=529 obj=24 db=2 ctx=0x181 suff=0
lm_internal_ctxchain_release
shared intent obj=24 db=2 ctx=0x0 suff=0
lock_multiple shared intent db=2 obj=241650882 splk=0xf2e2a000 suff=0
fileindex=305 (seq_lock.c) lineno=1642 LOCK_GRANTED
lock_do_logical shared page db=2 obj=241650882 pg=841 row=0 ctx=0x0 suff=0
pspid=19 fileindex=322 (startscan.c) lineno=1879 LOCK_GRANTED
lock_release shared page db=2 obj=241650882 pg=841 row=0 splk=0xf2e2a640
ctx=0x0
suff=0 fileindex=322 (startscan.c) lineno=1879
lm_internal_ctxchain_release
shared intent obj=241650882 db=2 ctx=0x1 suff=0
1>