An update or delete statement that has no useful index on its search arguments performs a table scan and holds an exclusive table lock for the entire scan time. If the data modification task also updates other tables:
It can be blocked by select queries or other updates.
It may be blocked and have to wait while holding large numbers of locks.
It can block or deadlock with other tasks.
Creating a useful index for the query allows the data modification statement to use page or row locks, improving concurrent access to the table. If creating an index for a lengthy update or delete transaction is not possible, you can perform the operation in a cursor, with frequent commit transaction statements to reduce the number of page locks.