delete from table without locking it

looking for the most efficient method to delete multiple records from tables
without locking them for other users

when i try    Begin Transaction
                        Delete from [table name]
                        Where ....

sometimes table(s) gets locked, looking for more elegant / intuitive way to get that done

Thanks in advance,

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Aneesh RetnakaranDatabase AdministratorCommented:
it's because of lock escalation, when there are more than 5000 rows has to be deleted the lock will be esacalated to a page level or a table level lock, the ebest option will be to delete records below that number

  DELETE FROM [table name]
  Where ....
  IF ROWCOUNT = 4000
  GOTO Here;

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Éric MoreauSenior .Net ConsultantCommented:
do not use transaction when you don't want to lock to much. Also, you can always redo a Delete operation with the same where clause so transactions are not as important as for other operations.

ensure that there is an index on the fields covered by your where clause to be as effective as possible.
you cannot prevent mysql from locking either the table or the rows depending in the engine you use.

assuming you are using innodb, you can do two things
1) delete using a limit clause and run the delete as many times as needed. looks dumb but experience proves it is a workable solution in many environments
2) use different transaction_isolation_level (s). beware this helps in many cases but will also make your database less ACID compliant which you may or may not want
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

you can also use handlers instead of selects for some statements,

and you can also avoid deadlocks by changing some queries so deletes and selects lock in the same order, and possibly lock gaps as well

you definitely need to think that if mysql moves to page or table locking, it is right to do so because it will actually be more efficient or it detected an existing or potential deadlock


if what you delete is chunks of data overtime, partitionning horizontally by date and removing partitions instead of rows may be a good idea (the operation will be instantaneaous)


give us more information about what happens when you get the locks, maybe the output of "show full processlist" would help us to understand better
Anthony PerkinsCommented:
>>Zones:SQL Server 2008, SQL Server 2005, MySQL Server<<
You may want clarify what database you are using, as there appears to be some confusion on that subject.  Is it MS SQL Server or MySql?
yep. fyi handlers are not available on sql server, but cursors indirectly produce a similar functionnality.
for the rest, what was said above concerning innodb should also be true using mssql

deleting with a LIMIT clause should be portable on mssql by doing something like
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.