0

I have a table (TableA).

There is a probability that more than one process may try to delete data from TableA and at the same time more than one process may try to insert data into same table i.e. TableA.

Will this result in table lock? If yes, how can I avoid locking the table and making sure that read, insert and delete can happen in parallel.

Mahendra Dhaker
  • 43
  • 8
  • It's more likely to result in table locking, rather than table locking. As for if they can happen in parallel, that depends on your indexing and what data you are trying to access. If, for example, you are trying to `INSERT` data with a date of `20221101` in one transaction, and trying to `DELETE` data *prior* to `20221101` then provided SQL Server has an index it can use it won't conflict. – Thom A Nov 01 '22 at 17:15
  • Thank You @Larnu . Let us say I am deleting data based on a city name (DELETE FROM TableA WHERE city = 'City1'). At the same time, below processes are also running in parallel (in different sessions): DELETE FROM TableA WHERE city = 'City2'; DELETE FROM TableA WHERE city = 'City6' INSERT INTO TableA (For city = 'City9') INSERT INTO TableA (For city = 'City11') INSERT INTO TableA (For city = 'City900') etc. Basically, the user is not going to insert/delete at the same time for a specific city. If I create a non clustered Index on City field then I wont run into locking issues? – Mahendra Dhaker Nov 02 '22 at 09:25
  • You *shouldn't*; something you can easily test in a sandbox enviroment. – Thom A Nov 02 '22 at 09:34

0 Answers0