Prev: Policies
Next: Serial Motor
From: simon on 28 Jun 2010 05:31
Hi,

if I have delete statement on my table over ident column, is it
possible that it blocks insert statements from other users?

DELETE from my table WHERE idIdenty<@idIdenty

New inserts would have idIdenty larger than the ones, that are
currentyl deleting, so, the delete statement should't block other
users from insert new ones(only if delete statement creates table
lock)?
Is it possible to force delete statement to create only page locks and
not table lock?
Any other idea? If i put idIdentity column into clustered index whould
than be only page lock?

Thanks, Simon
From: Erland Sommarskog on 28 Jun 2010 17:54
simon (zupan.net(a)gmail.com) writes:
> if I have delete statement on my table over ident column, is it
> possible that it blocks insert statements from other users?
>
> DELETE from my table WHERE idIdenty<@idIdenty
>
> New inserts would have idIdenty larger than the ones, that are
> currentyl deleting, so, the delete statement should't block other
> users from insert new ones(only if delete statement creates table
> lock)?

That could happen if the lock is escalated to a table lock. It could
also happen if there are other indexes on the table than on idIdentity.
And of course, if there are no indexes at all, there will be a table lock.

> Is it possible to force delete statement to create only page locks and
> not table lock?
> Any other idea? If i put idIdentity column into clustered index whould
> than be only page lock?

I think the best is to run the DELETE in batches:

WHILE EXISTS (SELECT * FROM mytable WHERE idIdentity < @idIdentity)
DELETE TOP (5000) FROM mytable WHERE idIdentity < @idIdentity

Whether 5000 is the best number is something you have to try out.


--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

From: Uri Dimant on 29 Jun 2010 03:06
Issue DELETE statement in small batches
WHILE 1 = 1
BEGIN

DELETE TOP (500000)
FROM tbl;

IF @@ROWCOUNT < 500000 BREAK;

END



"simon" <zupan.net(a)gmail.com> wrote in message
news:1748eb1c-2c6a-4fad-a32b-917232b33c4a(a)c10g2000yqi.googlegroups.com...
> Hi,
>
> if I have delete statement on my table over ident column, is it
> possible that it blocks insert statements from other users?
>
> DELETE from my table WHERE idIdenty<@idIdenty
>
> New inserts would have idIdenty larger than the ones, that are
> currentyl deleting, so, the delete statement should't block other
> users from insert new ones(only if delete statement creates table
> lock)?
> Is it possible to force delete statement to create only page locks and
> not table lock?
> Any other idea? If i put idIdentity column into clustered index whould
> than be only page lock?
>
> Thanks, Simon


 | 
Pages: 1
Prev: Policies
Next: Serial Motor