Delete 100,000 records.Lock issues 1

[avihaimar]

I want to delete every hour more than 100,000 records (there is on delete cascade on the reference tables).

I decide to delete them in limit of 40000 (is it a good decision ?)

I heard that i need to worry about locking (there is no select on this records).

Can someone please give the point that i need to consider?

Thank you

 

[TonyGroves]

Regarding limiting the number of deletes at a time, that subject has already been discussed at length in another thread.

Regarding locking, that would only be necessary if you are running more than one statement and it is important that there are no other updates done in the meantime. If you are only running one statement, then no locking is needed.

 

[avihaimar]

I execute - delete from A.
This query delete also the sons of A (Add constarints on delete cascade).

all the time records are inserted to A.

So do i need to lock rows or table?

 

[TonyGroves]

If it's only one SQL statement, then no locking is needed; updates/inserts will be blocked by the server until the statement has been fully executed. And you can't lock rows, only tables.

 

[avihaimar]

Thank you,

I delete big amout so the delete will take time.
What about the risks that in this time the server will block the inserts to those tables?

 

[TonyGroves]

The inserts won't be rejected, they will only be delayed. If you think the time delay will be too long, then you can use LIMIT to reduce the processing time of your delete query.