February 17, 2009 at 10:46 pm
hi,
i did a small test on my machine. i open 2 query windows.
one of the windows run this code:
-----------------------------------------------------
create table lock (col1 int, col2 int, col3 int, col4 char(6000))
declare @i int
set @i = 1
while @i <= 100000
begin
insert into lock values (@i, @i*8, @i*datepart(second, getdate()), 'some text')
set @i = @i + 1
end
begin tran
delete from lock with(rowlock) where col1 between 50 and 5000
waitfor delay '00:00:30'
rollback
another window runs this code:
-----------------------------------------------------
update lock with(nowait)
set col3 = 1234
where col1 = 99999
-----------------------------------------------------
my UPDATE statement get blocked by the DELETE statement.
now, if i swap the UPDATE and DELETE statement, my DELETE is able to execute successfully.
does anyone know why?
thanks.
February 17, 2009 at 11:35 pm
Check the lock types taken for both. You'll probably find that the delete has taken a lower granularity lock than the update
sys.dm_os_tran_locks
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 18, 2009 at 12:53 am
i ran this command to disable lock escalation:
dbcc traceon (1211)
i don't think this is what i want.
February 18, 2009 at 4:48 am
Leo Leong (2/18/2009)
i don't think this is what i want.
I don't think so either. Why are you disabling lock escalation?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 18, 2009 at 8:14 am
if you want to FORCE lock type on both use "WITH (ROWLOCK)"
* Noel
February 18, 2009 at 6:25 pm
i tried use WITH(ROWLOCK) on both.
however, DELETE will be escalated to TAB lock. It will block my UPDATE.
in my production environment, this situation is even worse.
on one hand, one SP is deleting 5-10 records. on the other hand, another SP is updating 1000-2000 records.
when DELETE get started first, all other operations will be blocked.
The records for DELETE and UPDATE are totally different. if i disable lock escalation, both statements will be executed instantly.
any thought on this?
thanks.
February 19, 2009 at 1:27 am
The delete shouldn't escalate to a table lock with 5 of 10 rows unless it's a very small table or there are no appropriate indexes.
Can you post delete statement, table structure and index definitions?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 19, 2009 at 1:58 am
well, here is a simplified version of my scripts:
Table Structure (Actual table consists of > 100 columns and 20 mil records)
---------------
CREATE TABLE [dbo].[mytable](
[id] [int] IDENTITY(1,1) NOT NULL,
[ud_agent_id] [varchar](30) NOT NULL,
[ud_status] [tinyint] NOT NULL,
[ud_upd_user] [varchar](30) NOT NULL,
[ud_upd_timestamp] [datetime] NOT NULL CONSTRAINT [de_mytable__ud_upd_timestamp] DEFAULT (getdate()),
[ud_marketability_status] [int] NULL CONSTRAINT [DF_mytable_ud_marketability_status] DEFAULT ((1)),
[ud_marketability_reason] [varchar](100) NULL,
CONSTRAINT [pk_mytable] PRIMARY KEY NONCLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
ALTER TABLE [dbo].[mytable] WITH NOCHECK ADD CONSTRAINT [CK_mytable] CHECK (([ud_agent_id]<>''))
GO
ALTER TABLE [dbo].[mytable] CHECK CONSTRAINT [CK_mytable__ud_agent_id]
Index (There are > 13 indexes in for this table)
-----
CREATE CLUSTERED INDEX [cidx_mytable] ON [dbo].[mytable]
(
[ud_agent_id] ASC,
[ud_status] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF,
DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
How I delete data
-----------------
exec usp_delete @in_agent_id=N'143915',@in_list_id=N'',@xml_id=N' '
In usp_delete,
Declare @tmpID Table(id int)
INSERT INTO @tmpID
SELECT IDToDelete.value('.', 'int') as id
FROM @xml_id.nodes('//id') t(IDToDelete)
DELETE FROM dbo.mytable
WHERE EXISTS(
SELECT 1
FROM @tmpID t
WHERE t.id=dbo.mytable.id)
do u see any reason why my deletion get escalated to TAB lock?
thanks.
February 19, 2009 at 5:54 am
How many rows does the delete affect?
Why that choice of clustered index? It doesn't look like a very optimal one (wide, not unique, not ever-increasing).
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 19, 2009 at 8:16 am
(There are > 13 indexes in for this table)
Now, That's a problem!
* Noel
February 19, 2009 at 8:32 am
ops, there should be something assigned to the @xml_id variable.
it should look like this:
normally user will select 5-8 id from the web page and pass it to db. so, there should be exactly 5-8 records will be deleted.
regarding the non-unique PK, i got to admit that is a mistake.
it was designed by somebody else. this is not the only table that they designed like this.
by the time i found out, its size is oredi 12 mil records. and now, it becomes 20 mil.
i can't simply re-create a proper clustered index in the prod env now.
let say, we ignore the PK problem, it shouldn't escalate from key/page lock to tab lock right?
thanks.
February 19, 2009 at 8:36 am
u mean, with > 13 indexes, it locks too many resources. in the end, it has to escalate key/page lock to tab lock to improve system performance?
hm ...... my test env still contains 12 mil records.
i can delete all indexes and try it out.
February 19, 2009 at 12:02 pm
Start by disabling all indexes except cluster and PK then do your thing and post back.
I am sure 13 indexes on 7 column table is just wrong.
* Noel
February 19, 2009 at 12:27 pm
Leo Leong (2/19/2009)
well, here is a simplified version of my scripts:Table Structure (Actual table consists of > 100 columns and 20 mil records)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply