Locking problem.
Asif H. rajwani
We have a database with a table whose structure is something like:
Create table tester
(f1 int,
f2 int,
f3 int,
f4 int)
and we have a "unique clustered index on f1, f2, f3".
When two users issues the following update from isql
first user
update tester
set f4 = 5
where f1 = 123
second user
update tester
set f4 = 4
where f1 = 129
We are getting deadlock when I look at the sp_lock I can see
Ex_Page_Blk. I do NOT understand how could it happen as I have cluster
index on f1,
f2, f3 so all the pages with same f1 are clustered together.
In this table we have about 5Million rows and there could be upto 25
thousands rows with same f1.
All suggestion and pointer appreciated.
Thank you.
Asif
===
Eoin Woods (Sybase UK)
> We have a database with a table whose structure is something like:
>
> Create table tester (f1 int, f2 int, f3 int, f4 int)
> and we have a "unique clustered index on f1, f2, f3".
>
> When two users issues the following update from isql
>
> first user
> update tester
> set f4 = 5
> where f1 = 123
>
> second user
> update tester
> set f4 = 4
> where f1 = 129
>
> We are getting deadlock when I look at the sp_lock I can see
> Ex_Page_Blk. I do NOT understand how could it happen as I have cluster
> index on f1,
> f2, f3 so all the pages with same f1 are clustered together.
well expect contention if two updates got in each other's way, but
not a deadlock.
Are the columns you are updating nullable? Or to get to the point,
are the rows fixed length? If not, what could be happening is that
rows are not being updated in place but are being moved. If this
happens, index pages need updated and page splits could be occurring.
Therefore you could be getting deadlocks on index pages rather than
data pages.
Eoin.
--
Eoin Woods, NT Development, ASE Engineering, Sybase Inc.
Sybase (UK), Crown Lane, Maidenhead, Berkshire, SL6 8QZ
Tel: +44-1628-597215 Fax: +44-1628-597000
Email: Eoin....@sybase.com
John McVicker
Are you getting a true deadlock (error 1205) or is it just a blocking
lock? Probably just a blocking lock where one user waits for the
other to process his/her update.
What you may want to do is this. Change the table's LWM and HWM
lock escalation points above the default 200 pages. That way,
more page locks will be taken before escalating to a table lock.
Sounds like the first user updates many rows (like you said,
25 thousand rows where f1 = <n>). When this occurs, the first
update will take an exclusive table lock during the statement which
is done after it reaches 200 page locks - if you have say 40 rows
per page, that is 8000 rows updated before a table lock. If fewer
rows per page, then it is even faster to escalate to a table
lock.
The Sybase Perforamnce & Tuning guide goes into tuning things
like this - and setting the thredhold for lock escalation either
in the server as a serverwide parameter (sp_configure) or for
the table will do the trick for you to stop or reduce considerably
the blocking you are seeing.
On Mon, 02 Mar 1998 22:36:34 -0500,
in sybase.public.sqlserver.general
Asif H. rajwani <as...@noln.com> wrote:
>Hi,
> We have a database with a table whose structure is something like:
>
>Create table tester
>(f1 int,
> f2 int,
> f3 int,
> f4 int)
>
>and we have a "unique clustered index on f1, f2, f3".
>
>When two users issues the following update from isql
>
>first user
>update tester
>set f4 = 5
>where f1 = 123
>
>second user
>update tester
>set f4 = 4
>where f1 = 129
>
>We are getting deadlock when I look at the sp_lock I can see
>Ex_Page_Blk. I do NOT understand how could it happen as I have cluster
>index on f1,
>f2, f3 so all the pages with same f1 are clustered together.
>
>In this table we have about 5Million rows and there could be upto 25
>thousands rows with same f1.
>
>All suggestion and pointer appreciated.
>
>Thank you.
>
>Asif
>===
>
John McVicker
Sybase Professional Services
Philadelphia, PA
Asif H. rajwani
Thank you for your reply. This is exactly what seems like happening.
Could you please advise me what is usually done is this cases.
Just to give the added information HWM value is fairly high around
100,000. There are no table lock and we are getting deadlock even if the f1
values being updated are far apart.
Thank you again.
Asif
===
Eoin Woods (Sybase UK) wrote:
> > We have a database with a table whose structure is something like:
> >
> > Create table tester (f1 int, f2 int, f3 int, f4 int)
> > and we have a "unique clustered index on f1, f2, f3".
> >
> > When two users issues the following update from isql
> >
> > first user
> > update tester
> > set f4 = 5
> > where f1 = 123
> >
> > second user
> > update tester
> > set f4 = 4
> > where f1 = 129
> >
> > We are getting deadlock when I look at the sp_lock I can see
> > Ex_Page_Blk. I do NOT understand how could it happen as I have cluster
> > index on f1,
> > f2, f3 so all the pages with same f1 are clustered together.
Asif H. rajwani
Thank you for the reply. Yes, we are getting true dead lock with error
1205. Seems like index pages are being locked as describe in the response
below yours. Please let me know if you could think of any way to fix this.
Asif
===
John McVicker wrote:
> Are you getting a true deadlock (error 1205) or is it just a blocking
> lock? Probably just a blocking lock where one user waits for the
> other to process his/her update.
>
> What you may want to do is this. Change the table's LWM and HWM
> lock escalation points above the default 200 pages. That way,
> more page locks will be taken before escalating to a table lock.
> Sounds like the first user updates many rows (like you said,
> 25 thousand rows where f1 = <n>). When this occurs, the first
> update will take an exclusive table lock during the statement which
> is done after it reaches 200 page locks - if you have say 40 rows
> per page, that is 8000 rows updated before a table lock. If fewer
> rows per page, then it is even faster to escalate to a table
> lock.
>
> The Sybase Perforamnce & Tuning guide goes into tuning things
> like this - and setting the thredhold for lock escalation either
> in the server as a serverwide parameter (sp_configure) or for
> the table will do the trick for you to stop or reduce considerably
> the blocking you are seeing.
>
> On Mon, 02 Mar 1998 22:36:34 -0500,
> in sybase.public.sqlserver.general
> Asif H. rajwani <as...@noln.com> wrote:
> >Hi,
> > We have a database with a table whose structure is something like:
> >
> >Create table tester
> >(f1 int,
> > f2 int,
> > f3 int,
> > f4 int)
> >
> >and we have a "unique clustered index on f1, f2, f3".
> >
> >When two users issues the following update from isql
> >
> >first user
> >update tester
> >set f4 = 5
> >where f1 = 123
> >
> >second user
> >update tester
> >set f4 = 4
> >where f1 = 129
> >
> >We are getting deadlock when I look at the sp_lock I can see
> >Ex_Page_Blk. I do NOT understand how could it happen as I have cluster
> >index on f1,
> >f2, f3 so all the pages with same f1 are clustered together.
> >
Asif H. rajwani
1. Yes updates are deffered not inplace.
2. We are getting the deadlock (1205) not the blocking.
Asif
===
> Thank you for your reply. This is exactly what seems like happening.
> Could you please advise me what is usually done is this cases.
>
> Just to give the added information HWM value is fairly high around
> 100,000. There are no table lock and we are getting deadlock even if the f1
> values being updated are far apart.
>
> Thank you again.
>
> Asif
> ===
>
> Eoin Woods (Sybase UK) wrote:
>
> > > We have a database with a table whose structure is something like:
> > >
> > > Create table tester (f1 int, f2 int, f3 int, f4 int)
> > > and we have a "unique clustered index on f1, f2, f3".
> > >
> > > When two users issues the following update from isql
> > >
> > > first user
> > > update tester
> > > set f4 = 5
> > > where f1 = 123
> > >
> > > second user
> > > update tester
> > > set f4 = 4
> > > where f1 = 129
> > >
> > > We are getting deadlock when I look at the sp_lock I can see
> > > Ex_Page_Blk. I do NOT understand how could it happen as I have cluster
> > > index on f1,
> > > f2, f3 so all the pages with same f1 are clustered together.
> > I agree that on first inspection this does look a bit odd. I might
> > well expect contention if two updates got in each other's way, but
> > not a deadlock.
> >
> > Are the columns you are updating nullable? Or to get to the point,
> > are the rows fixed length? If not, what could be happening is that
> > rows are not being updated in place but are being moved. If this
> > happens, index pages need updated and page splits could be occurring.
> > Therefore you could be getting deadlocks on index pages rather than
> > data pages.
> >
> > Eoin.
> > --
> > Eoin Woods, NT Development, ASE Engineering, Sybase Inc.
> > Sybase (UK), Crown Lane, Maidenhead, Berkshire, SL6 8QZ
> > Tel: +44-1628-597215 Fax: +44-1628-597000
> > Email: Eoin....@sybase.com
>
> ------------------------------------------------------------------------
>
> Asif Rajwani <as...@noln.com>
> Icon Solutions
>
> Asif Rajwani
> Icon Solutions <as...@noln.com>
> Home: (732)635-0701
> Netscape Conference Address
> Netscape Conference DLS Server
> Additional Information:
> Last Name Rajwani
> First Name Asif
> Version 2.1
lop...@sni13.viacom.com
If you are getting contention on the index pages, try recreating the affected
index with a max rows/page value, this will increase the size of the index
substantially ( expecially since you have 5 million rows ) but hopefully will
reduce the index page contention. you could also do the same on the clustered
index which might reduce the index page updates if the deferred update does
not change the record number in the page.
dave
In article <34FCA4C9...@noln.com>,
"Asif H. rajwani" <as...@noln.com> wrote:
>
> This is a multi-part message in MIME format.
> --------------B7EA5DDB676D2460371FC78A
> Content-Type: text/plain; charset=us-ascii
> Content-Transfer-Encoding: 7bit
> > > We have a database with a table whose structure is something like:
> > >
> > >Create table tester
> > >(f1 int,
> > > f2 int,
> > > f3 int,
> > > f4 int)
> > >
> > >and we have a "unique clustered index on f1, f2, f3".
> > >
> > >When two users issues the following update from isql
> > >
> > >first user
> > >update tester
> > >set f4 = 5
> > >where f1 = 123
> > >
> > >second user
> > >update tester
> > >set f4 = 4
> > >where f1 = 129
> > >
> > >We are getting deadlock when I look at the sp_lock I can see
> > >Ex_Page_Blk. I do NOT understand how could it happen as I have cluster
> > >index on f1,
> > >f2, f3 so all the pages with same f1 are clustered together.
> > >
> > >In this table we have about 5Million rows and there could be upto 25
> > >thousands rows with same f1.
> > >
> > >All suggestion and pointer appreciated.
> > >
> > >Thank you.
> > >
> > >Asif
> > >===
> > >
> >
> > John McVicker
> > Sybase Professional Services
> > Philadelphia, PA
>
> --------------B7EA5DDB676D2460371FC78A
> Content-Type: text/x-vcard; charset=us-ascii; name="vcard.vcf"
> Content-Transfer-Encoding: 7bit
> Content-Description: Card for Asif Rajwani
> Content-Disposition: attachment; filename="vcard.vcf"
>
> begin: vcard
> fn: Asif Rajwani
> n: Rajwani;Asif
> org: Icon Solutions
> email;internet: as...@noln.com
> tel;home: (732)635-0701
> x-mozilla-cpt: ;0
> x-mozilla-html: FALSE
> version: 2.1
> end: vcard
>
> --------------B7EA5DDB676D2460371FC78A--
>
>
-----== Posted via Deja News, The Leader in Internet Discussion ==-----
http://www.dejanews.com/ Now offering spam-free web-based newsreading
John McVicker
Asif,
As I mentioned in the private email to you, we will need
to see the following:
Table DDL with indexes shown
SET SHOWPLAN ON output from the update
SELECT @@version
This will give an indication as to why it is deferred,
such as NULLable columns. Also, in SQL Server 11,
there are more ways to make the update direct, so
the @@version will give an indication to what version you
are using - if it is SQL Server 10, could possibly
be made a direct update in SQL Server 11 or ASE 11.5.
On Tue, 03 Mar 1998 19:51:22 -0500,
in sybase.public.sqlserver.general
Asif H. rajwani <as...@noln.com> wrote:
>Just to add to my last mail.
>1. Yes updates are deffered not inplace.
>2. We are getting the deadlock (1205) not the blocking.
>
>Asif
>===
>
>> Thank you for your reply. This is exactly what seems like happening.
>> Could you please advise me what is usually done is this cases.
>>
>> Just to give the added information HWM value is fairly high around
>> 100,000. There are no table lock and we are getting deadlock even if the f1
>> values being updated are far apart.
>>
>> Thank you again.
>>
>> Asif
>> ===
>>
>> Eoin Woods (Sybase UK) wrote:
>>
>> > > We have a database with a table whose structure is something like:
>> > >
>> > > Create table tester (f1 int, f2 int, f3 int, f4 int)
>> > > and we have a "unique clustered index on f1, f2, f3".
>> > >
>> > > When two users issues the following update from isql
>> > >
>> > > first user
>> > > update tester
>> > > set f4 = 5
>> > > where f1 = 123
>> > >
>> > > second user
>> > > update tester
>> > > set f4 = 4
>> > > where f1 = 129
>> > >
>> > > We are getting deadlock when I look at the sp_lock I can see
>> > > Ex_Page_Blk. I do NOT understand how could it happen as I have cluster
>> > > index on f1,
>> > > f2, f3 so all the pages with same f1 are clustered together.
John McVicker
Asif Rajwani
Here is all the information you requested. We are using sybase version 11.0.3 and
it is on Sun box.
DLL for the table:
I recreated all the indexes to make sure that they have the correct fillfactor.
CREATE TABLE dbo.product_sale(
sale_batch_id int NOT NULL,
sale_batch_record_id int NOT NULL,
sale_batch_record_sub_id int NOT NULL,
distribution_channel_id int NULL,
status_id int NOT NULL,
owner_price_basis_id int NULL,
price_line_key char(1) NULL,
config_key char(2) NULL,
product_no char(15) NULL,
payment_process_key char(12) NULL,
seller_price_basis_id int NULL,
seller_contract_id int NULL,
seller_company_key char(4) NULL,
owner_contract_id int NULL,
owner_company_key char(4) NULL,
owner_selection_no char(15) NULL,
sale_period datetime NULL,
reporting_period datetime NULL,
pockets int NULL,
ppd float NULL,
uplift_pct float NULL,
vat_pct float NULL,
retail_price float NULL,
container_deduction_pct float NULL,
base_price float NULL,
royalty_paid_units int NULL,
royalty_free_units int NULL,
royalty_rate_pct float NULL,
participation_pct float NULL,
gross_royalty_lc float NULL,
seller_royalty_due_lc money NULL,
owner_royalty_due_lc money NULL,
withholding_tax_pct float NULL,
nonstandard_rate bit NOT NULL,
payment_processing_date datetime NULL,
compilation bit NOT NULL,
compilation_action char(1) NULL,
owner_company_key_mod char(1) DEFAULT 'N',
price_line_key_mod char(1) DEFAULT 'N',
config_key_mod char(1) DEFAULT 'N',
product_no_mod char(1) DEFAULT 'N',
ppd_mod char(1) DEFAULT 'N',
uplift_pct_mod char(1) DEFAULT 'N',
vat_pct_mod char(1) DEFAULT 'N',
retail_price_mod char(1) DEFAULT 'N',
base_price_mod char(1) DEFAULT 'N',
royalty_paid_units_mod char(1) DEFAULT 'N',
royalty_free_units_mod char(1) DEFAULT 'N',
participation_pct_mod char(1) DEFAULT 'N',
gross_royalty_mod char(1) NOT NULL,
withholding_tax_pct_mod char(1) DEFAULT 'N',
supreme_override bit NOT NULL,
prelim_override bit NOT NULL,
owner_container_deduction_pct float NULL,
owner_base_price float NULL,
owner_royalty_rate_pct float NULL,
owner_withholding_tax_pct float NULL,
owner_trademark_pct float NULL,
owner_royalty_cap_pct float NULL,
owner_penny_rate float NULL,
rerun_datacheck bit NOT NULL,
seller_variance_disposition char(1) NULL,
payout_variance_disposition char(1) NULL,
owner_vat_pct float NULL,
gl_posted bit DEFAULT 0,
exchange_rate_used float NULL,
sale_locked bit NOT NULL,
prior_pd_adjustment_made bit DEFAULT 0,
invoice_number char(30) NULL,
rerun_gl_records bit DEFAULT 0,
prior_gl_exchange_rate float NULL,
prior_gl_owner_company_key char(4) NULL,
prior_gl_seller_contract_id int NULL,
prior_gl_owner_contract_id int NULL,
last_mod_date datetime NOT NULL,
last_mod_user int NOT NULL,
timestamp timestamp NOT NULL,
new_adjustment_batch_id int NULL,
distribution_ar_id int NULL,
prior_distribution_ar_id int NULL,
prior_gross_royalty_ic float NULL)
go
IF OBJECT_ID('dbo.product_sale') IS NOT NULL
PRINT '<<< CREATED TABLE dbo.product_sale >>>'
ELSE
PRINT '<<< FAILED CREATING TABLE dbo.product_sale >>>'
go
/*
* Creating Indexes for Table dbo.product_sale
*/
CREATE UNIQUE CLUSTERED INDEX pk_product_sale
ON
dbo.product_sale(sale_batch_id,sale_batch_record_id,sale_batch_record_sub_id)
with fillfactor = 70
go
CREATE INDEX bso
ON dbo.product_sale(sale_batch_id,status_id,owner_company_key) with fillfactor
= 40
go
CREATE INDEX saleprod
ON dbo.product_sale(product_no,sale_period,sale_batch_id) with fillfactor = 40
go
Table has about 3.5 Million rows and there are about 5 to 25 thousand rows for
each bactch id.
DeadLock is created when the following query with different batchid is run from 3
to 5 interactive isql sessions.
update product_sale
set product_sale.exchange_rate_used = seller_owner_exchange_rate.exchange_rate
from product_sale, seller_owner_exchange_rate
where product_sale.sale_batch_id = 9344 and
status_id <> 4 and
product_sale.seller_company_key = seller_owner_exchange_rate.seller_company_key
and
product_sale.owner_company_key = seller_owner_exchange_rate.owner_company_key
and
product_sale.reporting_period = seller_owner_exchange_rate.period
Show Plan:
QUERY PLAN FOR STATEMENT 1 (at line 1).
STEP 1
The type of query is UPDATE.
The update mode is deferred.
FROM TABLE
product_sale
Nested iteration.
Using Clustered Index.
Index : pk_product_sale
Ascending scan.
Positioning by key.
Keys are:
sale_batch_id
Using I/O Size 2 Kbytes.
With LRU Buffer Replacement Strategy.
FROM TABLE
seller_owner_exchange_rate
Nested iteration.
Index : pk
Ascending scan.
Positioning by key.
Keys are:
seller_company_key
owner_company_key
period
Using I/O Size 2 Kbytes.
With LRU Buffer Replacement Strategy.
TO TABLE
product_sale
When the updates where running I was looking at the locks using sp_who from
different session I found
the following pages as Ex_Page_Blk
227048, 286693
here is the dbcc page output for these pages
227048:
Page found in cache default data cache.
BUFFER:
Buffer header for buffer 0xe3537000 (Mass head)
page=0xe3537800 bdnew=0x0 bdold=0x0 bhash=0x0
bmass_next=0x0 bmass_prev=0x0 bvirtpg=0 bdbid=0
bmass_head=0xe3537000 bmass_tail=0xe3537000 bcache_desc=0xdfe32fa0
bpool_desc=0x0
Mass bkeep=0 Mass bpawaited=0 Mass btripsleft=0 Mass btripsleft_orig=0
bsize=2048 (2K pool) bmass_stat=0x800 (0x00000800 (MASS_NOTHASHED))
bbuf_stat=0x0 (0x00000000)
Buffer bpageno=227048 Mass bpageno=227048 (Buffer slot #: 0)
bxls_pin = 0x00000000 bxls_next = 0x00000000b
bxls_flushseq 0 bxls_pinseq 0
PAGE HEADER:
Page header for page 0xe3537800
pageno=227048 nextpg=227049 prevpg=288255 objid=1787869436 timestamp=0001 1bcd75dd
nextrno=968 level=0 indid=3 freeoff=1004 minlen=11
page status bits: 0x2 (0x0002 (PG_LEAF))
DBCC execution completed. If DBCC printed error messages, contact a user with
System Administrator (SA) role.
286693:
Page found in cache default data cache.
BUFFER:
Buffer header for buffer 0xe3537000 (Mass head)
page=0xe3537800 bdnew=0x0 bdold=0x0 bhash=0x0
bmass_next=0x0 bmass_prev=0x0 bvirtpg=0 bdbid=0
bmass_head=0xe3537000 bmass_tail=0xe3537000 bcache_desc=0xdfe32fa0
bpool_desc=0x0
Mass bkeep=0 Mass bpawaited=0 Mass btripsleft=0 Mass btripsleft_orig=0
bsize=2048 (2K pool) bmass_stat=0x800 (0x00000800 (MASS_NOTHASHED))
bbuf_stat=0x0 (0x00000000)
Buffer bpageno=286693 Mass bpageno=286693 (Buffer slot #: 0)
bxls_pin = 0x00000000 bxls_next = 0x00000000b
bxls_flushseq 0 bxls_pinseq 0
PAGE HEADER:
Page header for page 0xe3537800
pageno=286693 nextpg=286694 prevpg=286692 objid=1787869436 timestamp=0001 1bc8cd97
nextrno=827 level=0 indid=3 freeoff=864 minlen=11
page status bits: 0x2 (0x0002 (PG_LEAF))
DBCC execution completed. If DBCC printed error messages, contact a user with
System Administrator (SA) role.
INDID 3 is the index "saleprod"
NOTE: Even if we don't have block only one session is really working and rest of
the sessions are blocked
after the running sessions.
If you need more information please let me know.
Asif
===
=========================================================
Asif Rajwani
Please look at my reply to the above thread
lop...@sni13.viacom.com wrote:
> If you are getting contention on the index pages, try recreating the affected
> index with a max rows/page value, this will increase the size of the index
> substantially ( expecially since you have 5 million rows ) but hopefully will
> reduce the index page contention. you could also do the same on the clustered
> index which might reduce the index page updates if the deferred update does
> not change the record number in the page.
>
> dave
>
> In article <34FCA4C9...@noln.com>,
> "Asif H. rajwani" <as...@noln.com> wrote:
> >
> > > On Mon, 02 Mar 1998 22:36:34 -0500,
> > > in sybase.public.sqlserver.general
> > > Asif H. rajwani <as...@noln.com> wrote:
> > > >Hi,
> > > > We have a database with a table whose structure is something like:
> > > >
> > > >Create table tester
> > > >(f1 int,
> > > > f2 int,
> > > > f3 int,
> > > > f4 int)
> > > >
> > > >and we have a "unique clustered index on f1, f2, f3".
> > > >
> > > >When two users issues the following update from isql
> > > >
> > > >first user
> > > >update tester
> > > >set f4 = 5
> > > >where f1 = 123
> > > >
> > > >second user
> > > >update tester
> > > >set f4 = 4
> > > >where f1 = 129
> > > >
> > > >We are getting deadlock when I look at the sp_lock I can see
> > > >Ex_Page_Blk. I do NOT understand how could it happen as I have cluster
> > > >index on f1,
> > > >f2, f3 so all the pages with same f1 are clustered together.
> > > >
> > > >In this table we have about 5Million rows and there could be upto 25
> > > >thousands rows with same f1.
> > > >
> > > >All suggestion and pointer appreciated.
> > > >
> > > >Thank you.
> > > >
> > > >Asif
> > > >===
> > > >
> > >
> > > John McVicker
> > > Sybase Professional Services
> > > Philadelphia, PA
> >
lop...@sni13.viacom.com
asif,
I have finally gotten around to looking at your reply ( which i didnt find ).
I am not sure what you want me to see. perhaps you feel a previous response
answered my question ( i did not see that - i went thru 12 responses).
since it is a deferred update and if it is a page split ( as someone had
suggested ) perhaps the max rows will help ( if you have the space ). i
noticed the field being updated is nullable and so rows may move. if you make
it not null with a default bound to it you may also solve your problem.
dave
In article <34FF0D41...@iconsolutions.com>,
Asif Rajwani <Rajw...@iconsolutions.com> wrote:
>
> Please look at my reply to the above thread
>
> lop...@sni13.viacom.com wrote:
>
> > If you are getting contention on the index pages, try recreating the
affected
> > index with a max rows/page value, this will increase the size of the index
> > substantially ( expecially since you have 5 million rows ) but hopefully
will
> > reduce the index page contention. you could also do the same on the
clustered
> > index which might reduce the index page updates if the deferred update
does
> > not change the record number in the page.
> >
> > dave
> >
jamie martin
your problem is most likely due to the fact that the update
is deferred. because the update is deferred, the update
is effectively processed as an insert followed by a delete.
this is probably why you are seeing a deadlock on saleprod
which isn't even being updated.
your update will always be deferred if it has a join -- there
is nothing that can be done about that. i'd recommend that
you structure your update to avoid the join (it is non-standard
sql syntax anyway). one approach would be to do something
like this:
set transaction isolation level 3
begin tran
declare c cursor for select ...
while (...)
begin
update ... where current of c
end
commit
give that a try and let us know if it changes to a direct
update.
-jamie