none
Lock a Sql table or use lock keyword? RRS feed

  • Question

  • Hi to all

    I have a MVC web service with a method that have to check the maximum number of athletes for race, before to add a new record. Is not possible to register in a Sql table more than x number of athletes; i find every time, this limit, by quering the same table. As the web services are stateless, before to write a new record in a table, i have to read the same table to get limit.

    The problem is the concurrency call to web service (for not exceed the limit). If i use a TransactionScope with IsolationLevel.Serializable i lock the table when i write new record. Inside this transaction, first i get the limit (with a Select), then if the limit is not exceeded, i add a new record (Insert). If 2 process (2 calls at WS) are concurrent, when i get the limit, i will have a deadlock error. For the problem of deadlock i have used, inside the TransactionScope, the lock keyword (first get the limit and second adda new record).

    Is this technique correct? or can I use only a lock keyword without using IsolationLevel.Serializable?

    THANKS

    Thursday, January 3, 2019 9:47 AM

All replies

  • I have a MVC web service

    I would think that you have an ASP.NET WebAPI Web program. What do you mean that you have a MVC Web service? Are you trying to call a MVC action method in a ASP.NET MVC project calling it a service, by using AJAX?

    Is this technique correct? or can I use only a lock keyword without using IsolationLevel.Serializable?

    No the whole thing doesn't seem to be right that you are locking or trying to lock a table under any circumstances in a multi user Web application environment.

    Are you using ADO.NET Entity Framework and trying to use a System.Transaction isolation level?

    Thursday, January 3, 2019 10:18 AM
  • Hello,

    Consider the following, what if when submitting the limit had already been reached you put them on a wait list? Then if someone drops out those in the waitlist have an opportunity to but then registered.

    This requires change in your database along with a method in the user interface to allow an athlete to unregister and also a method to get a person from the waitlist, most them to registered and then notify them.

    This is what I did in a class registration application build in a SPA architecture.

     


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Thursday, January 3, 2019 10:39 AM
  • Hi DA924x

    I have wrote "MVC Web service", because is not Asp.Net Core project. 

    I agree that lock a table is invasive. So my question is: using lock keyword in C# code in a WS, is it correct and solves problems?

    Are you using ADO.NET Entity Framework and trying to use a System.Transaction isolation level?

    Yes: 

    using (var scope = new TransactionScope(TransactionScopeOption.Required, new TransactionOptions()
    {
        IsolationLevel = IsolationLevel.Serializable
    })).....



    • Edited by Pengo11 Thursday, January 3, 2019 11:01 AM
    Thursday, January 3, 2019 10:50 AM
  • Hello Kareninstructor

    no, if the limit is exceeded, my WS simply throw new exception and the client (web site) show an error like: "race colpleted".

    it's easier than your example. So my question is: using lock keyword in C# code in a WS, is it correct and solves problems (without using a lock on a Sql table)?

    Thanks

    Thursday, January 3, 2019 10:58 AM
  • Hello Kareninstructor

    no, if the limit is exceeded, my WS simply throw new exception and the client (web site) show an error like: "race colpleted".

    it's easier than your example. So my question is: using lock keyword in C# code in a WS, is it correct and solves problems (without using a lock on a Sql table)?

    Thanks

    Easier is not always better :-) One should avoid locking anything in a database. Are you working with a transaction when performing these operations to rollback if there is an issue?

    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Thursday, January 3, 2019 11:18 AM
  • Yes, because i have others Insert in different table. I know that i should avoid locking anything in a database, but i'm not sure if "lock (this._sync)....", solves my problems of concurrenty when i get a limit with a query, in a web service.

    Thursday, January 3, 2019 12:15 PM
  • I have wrote "MVC Web service", because is not Asp.Net Core project. 

    Yes, you can use MVC in an ASP.NET WebAPI service that is not ASP.NET Core. I'll assume that's what you mean.

    I agree that lock a table is invasive. So my question is: using lock keyword in C# code in a WS, is it correct and solves problems?

    Well, I would think that you should somehow detect that the table is in a lock state and send the user a message to wait/try again, if you must lock the table.

    You can post to the ADO.NET EF forum about this locking of a table and EF is involved.

    https://social.msdn.microsoft.com/Forums/en-US/home?forum=adodotnetentityframework


    • Edited by DA924x Thursday, January 3, 2019 12:29 PM
    Thursday, January 3, 2019 12:26 PM
  • In my opinion, you can re-consider the idea of transactions (including BEGIN TRAN and COMMIT TRAN in SQL instead of TransactionScole in C#, maybe in a single stored procedure, avoiding multiple queries), but when you occasionally receive the deadlock error, this is a non-critical one, because you can usually retry the operation (in C# or SQL) several times: https://www.bing.com/search?q=detect+transient+deadlock+error+and+retry.

     

    Using lock in C# is not good if you intend to have several servers.



    Thursday, January 3, 2019 1:04 PM
  • Yes, because i have others Insert in different table. I know that i should avoid locking anything in a database, but i'm not sure if "lock (this._sync)....", solves my problems of concurrenty when i get a limit with a query, in a web service.

    I would say doubtful that this._sync is going to be of any assistance with this task.

    One way to find out is to run test against this task to mimic a real life situation in the wild.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Thursday, January 3, 2019 1:36 PM
  • Yes Kareninstructor, i have check the code in a ConsoleApplication (without call a WS), but obviously in a Windows process the lock keyword not work...I have to try using a console that call a WS.

    I update you tomorrow.

    Regards

     
    Thursday, January 3, 2019 4:44 PM
  • If using EF, you could just go to the EF backdoor and use ADO.NET with in T-SQL or a stored procedure and lock the table.

    The link shows you how to use the EF backdoor using EF6.

    https://social.msdn.microsoft.com/Forums/en-US/9966dc5f-8e7b-4add-b5f9-15eeb566bfa2/order-of-insertion-in-ef-6?forum=adodotnetentityframework

    Thursday, January 3, 2019 6:43 PM
  • Hi all,

    my solution with lock keyword work. Clearly the variable must be static.

    I know that a webservice, should not use a static field, bacause is a fire and forghet, but i have only one server (without reverse proxy as load balancer), and lock a sql table, I think it's more expensive.  The code, is something like this:

    public static Object _sync = new Object();
    lock (Utils._sync)
    {
        //NOT use IsolationLevel.Serializable:
        using (var scope = Utils.CreateTransactionScope())
        {
            int limit = ReadLimit();
            if(limit > maxAthlete)
                throw new PippoException("Race full");
                
            WriteRecordInRaceTable();
            WriteLog();
            WriteOther();
            
            scope.Complete();
        }
    }

    With this technique, I will never have a deadlock and all concurrent calls, will be queued (tested).

    What do you think?


    Friday, January 4, 2019 2:36 PM
  • What do you think?

    If it works for you, then use it. 

    Friday, January 4, 2019 10:34 PM
  • Hi Pengo11,

    Thank you for posting here.

    According your post, your question is more relevant to ASP.NET Core, please post in the following forum.

    https://forums.asp.net/1255.aspx/1?ASP+NET+Core

    The Visual C# forum discuss and ask questions about the C# programming language, IDE, libraries, samples, and tools.

    Best regards,

    Jack


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, January 7, 2019 8:26 AM