How do I lock just the relevant rows in a .NET transaction

Question

I have a Project Table, a Stakeholder Table, and an Association Table (which takes a ProjectID and a StakeholderID as foreign keys).

I want to delete a single Project but must first delete all that Project's rows in the Association Table.

Here is the method. ProjectRow is a strongly typed DataRow created with the DataSet Designer.

 public void RemoveProject(ProjectRow project)
    {
        try
        {
            var associations = from a in ds.Association.AsEnumerable()
                               where a.Project == project.ProjID
                               select a;
            foreach (DataRow assoc in associations)
            {
                assoc.Delete();
            }

            project.Delete();

            using (TransactionScope scope = new TransactionScope())
            {
                assocTableAdapter.Update(ds.Association);

                System.Threading.Thread.Sleep(40000); // to test the transaction.

                projTableAdapter.Update(ds.Project);

                scope.Complete();
            }
        }
        catch (Exception e)
        {
            Console.WriteLine(e.Message);
        }
    }

This method does achieve the effect required (stops associations being added to the deleted project during the transaction) but it seems to place a read and write lock on all the tables so I cannot even read from the Project Table during the sleep period.

I would like to be able to add other Project/Stakeholder pairs to the Association Table during the transaction. How do I achieve this? Cheers.

You might want to look into 'Delete Cascades' if you can change the table associations. It's not a code solution, but it's something you might want to consider. — Tipx, Oct 26 '11 at 01:59
http://blogs.msdn.com/b/dbrowne/archive/2010/06/03/using-new-transactionscope-considered-harmful.aspx — Remus Rusanu, Oct 26 '11 at 06:03

score 2 · Accepted Answer · edited May 23 '17 at 12:32

A few links but you can hint that you'd like row level locking and the databaase engine may or may not take the suggestion. However, since you're letting the library handle the deletes, who knows what it's doing (short of turning on profiler and capturing statements). It could very well be issuing table locks or you simply have the misfortune of the row locks escalating to page locks and the rows you are attempting to access in your query outside the transaction happen to be on the same page.

What's a body to do? You need to balance your concurrency needs against your risk for bad data. Here's a fun poster about SQL Server Isolation Levels

How do I lock just the relevant rows in a .NET transaction

1 Answers1