Execute multiple queries in one operation

Question

I have a multi-client application that needs to retrieve and update data from two database tables: enter image description here
Each client will need to retrieve the next(top) unused serial number from the SerialNumber table, mark it as used and update the NextSerial in the WorkOrder table with the SerialNumber value.
How can I make sure that each retrieval action (select and 2 updates) will happen separately? Is there some kind of a lock I can put on multiple queries?

Surely you dont mean "happen separately" - you mean the [total opposite](http://en.wikipedia.org/wiki/ACID)! — Jamiec, Mar 10 '15 at 14:38
Use `TransactionScope` , See this [question](http://stackoverflow.com/questions/14136295/update-multiple-table-using-one-query-in-sql-server) also search for Scope Identity or see this [question](http://stackoverflow.com/questions/9319532/return-value-from-sql-server-insert-command-using-c-sharp) — Habib, Mar 10 '15 at 14:38
@Jamiec I do mean "separately" since I'm referring to one action that holds 3 different operations. by "separately" I mean that each client will run it "separately" without interference. — Yoav, Mar 10 '15 at 14:42
That makes no sense, but perhaps were just arguing over language semantics. I would say you want your three actions to occur "together, without interference" rather than "separately" — Jamiec, Mar 10 '15 at 14:44
@Jamiec It's not an argue :). English is not my native language so I'll rephrase my question. thanks — Yoav, Mar 10 '15 at 14:46
Doing this type of "roll your own" NextSerial is a very slippery slope. Concurrency is very difficult to work out accurately. You can manage a portion of this using transactions but you are going to have take a read lock on the table that contains your next serial. This is one reason using an identity is so simple. It already has all the challenge dealt with. Another option might be to use a sequence but not sure what version of sql server you are using. — Sean Lange, Mar 10 '15 at 14:48
Just wrapping statements in a transaction does not. But inside a transaction any DML statements will start locking. It may be only at the row level as there is a whole set of rules about lock escalation. What it wouldn't do is lock your NextSerial table (assuming you are only reading the row you want). That is why I said you have to create a read lock on the table. No offense meant but given your current understanding of locking you are fighting a loosing battle. This stuff is very complicated and is very easy to get it wrong. I would go with an identity or sequence instead of this. — Sean Lange, Mar 10 '15 at 14:58
So creating some kind of a web service that will hold a lock on top of the transaction so that only one client will be able to access it at a time is also a valid solution? — Yoav, Mar 10 '15 at 15:09
I didn't mention anything about a webservice. You should be able to do all this inside a stored procedure. Again I will recommend not trying to roll your own locking mechanism here. It is extremely easy to get it wrong and you likely won't notice it until you have serious problems in production. I did something like this early in my career thinking I was being super smart and it required a total rethinking of the keys to get it sorted out. — Sean Lange, Mar 10 '15 at 15:58

score 0 · Accepted Answer · answered Mar 11 '15 at 09:45

This is the solution I came up with:
A WCF service to handle the client calls one by one as described here:

//set the service to receive one call at a time
[ServiceBehavior(InstanceContextMode = InstanceContextMode.Single)]
public class Service1 : IService1
{
    StringBuilder sb = new StringBuilder();
    private SqlConnection con = new SqlConnection(
        @"Data Source=YOAV-DEV\SQLEXPRESS2012;
        Initial Catalog=Test;Integrated Security=True");

    private string query = @"DECLARE @val int;
SET @val =(select top(1) VALUE from SerialNumber where Used = 0);
UPDATE SerialNumber SET Used = 1 WHERE Value = @val;
UPDATE WorkOrder SET NextSerial = (@val +1);
SELECT @val;";


    public string GetData()
    {
        //dapper dot net query
        var res = con.Query<int>(query).Single(); 
        return res.ToString();
    }
}

A WPF application to test it:

private void Window_Loaded(object sender, RoutedEventArgs e)
    {
        for (int i = 0; i < 50; i++)
        {
            Thread t = new Thread(() =>
            {
                Thread.Sleep(TimeSpan.FromSeconds(Helpers.GetRandomInt(1, 5)));
                var value = sr.GetData();
                Application.Current.Dispatcher.Invoke(
                    new Action(() => { txt1.Text += value + Environment.NewLine; }));
            });
            t.Start();
        }
    }

That get executed multiple times from a Console Application:

static void Main(string[] args)
    {
        string path = @"C:\Visual Studio 2013\Projects\WcfTester\WcfTester\bin\Debug\WcfTester.exe";
        for (int i = 0; i < 5; i++)
        {
            Process.Start(path);
        }
    }

I guess that using a WCF service might be an overkill for this but lacking the knowledge on how to handle this at the SQL Server level this solution works for me.

Execute multiple queries in one operation

1 Answers1