-
delete without locking the table?
Hi,
is there anyway i can run delete statements on an audit table that will not lock the whole table?
As it is an audit table, when i run the delete query, the whole application freezes until the delete query is complete.
the process is taking several minutes as the table is approx 40M rows, the where clause in the delete is indexed but thats it really.
any thoughts greatly appreciated by my system users!
-
Sql has to put lock on each row involved at least, so try use rowlock hint in your delete statement.
-
Check the query paln to see if index is used, it may be doing full table scan and locking the entire table. Are you deleting all rows or just few?. Also Try to delete few thousands at a time.
-
how do i enforce that the sql uses the index?
have i misunderstood bol? Doesnt it say hints about rowlocks dont work for deletes?
-
Where did you see rowlock doesn't work for deletes? It's part of delete statement's syntax. And you can use index hint to enforce index.
-
my mistake, have re-read bol - was reading nolock and rowlock as the same thing, weeellllll they sound the same!!!
am now experimenting with small numberes of rows to see if the rowlock sorts the problem. if it does for sure then its a done deal.
thanks a lot.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|