Nature of Lock is child table while deletion(sql server)
- by Mubashar Ahmad
Dear Devs
From couple of days i am thinking of a following scenario
Consider I have 2 tables with parent child relationship of kind one-to-many. On removal of parent row i have to delete the rows in child those are related to parents. simple right?
i have to make a transaction scope to do above operation i can do this as following; (its psuedo code but i am doing this in c# code using odbc connection and database is sql server)
begin transaction(read committed)
Read all child where child.fk = p1
foreach(child) delete child where child.pk = cx
delete parent where parent.pk = p1
commit trans
OR
begin transaction(read committed)
delete all child where child.fk = p1
delete parent where parent.pk = p1
commit trans
Now there are couple of questions in my mind
Which one of above is better to use specially considering a scenario of real time system where thousands of other operations(select/update/delete/insert) are being performed within a span of seconds.
does it ensure that no new child with child.fk = p1 will be added until transaction completes?
If yes for 2nd question then how it ensures? do it take the table level locks or what.
Is there any kind of Index locking supported by sql server if yes what it does and how it can be used.
Regards
Mubashar
