0

In our application, we need to make an exclusive access to a certain place. It is a legacy application with no communication between client and server, all clients communicate directly to the database.

Edit: This is an implementation of a semaphore for sql server

What we were using and working with no problem was this:

using (var sqlConnection = new SqlConnection(Parameters.ConnectionStringUncommited))
{
   sqlConnection.Open();             

   using (var sqlTransaction = sqlConnection.BeginTransaction(IsolationLevel.ReadUncommitted))
   {
      using (var sqlCommand = new DstSqlCommand($"SELECT COUNT(*) FROM Locks WHERE Id = {IDTOCHECK}", sqlConnection, sqlTransaction))
      {
         if ((int)sqlCommand.ExecuteScalar() > 0)
           ShowMessage("Locked");
         else
         {
            sqlCommand.CommandText = "INSERT INTO Locks(Id) VALUES({IDTOCHECK})";
            sqlCommand.ExecuteNonQuery();

            // Here we make lot of work, can be locked a lot of time, minutes even, because we show windows and things
           
            // We never commit the transaction, just rollback it
            sqlTransaction.Rollback();
         }
      }
   }
}

This was working perfectly with our version using SqlServer 2008 R2 and .NET Framework 3.5. We just made an update and updated to SqlServer 2014 and .NET Framework 4.8 and we are having sometimes timeouts in the SELECT COUNT(*).....statement. The problem is that only happens on our customers, so the debug is very hard.

I really don't get it, the transaction has an isolation level of read uncommited and the code has not changed. What is really happening here?

Rafa S.
  • 35
  • 6
  • 3
    "the transaction has an isolation level of read uncommited" - that's an incorrect design for starters.... Your logic above has always been flawed... – Mitch Wheat Jul 09 '21 at 11:12
  • 2
    Yes, `read uncommitted` a.k.a. `nolock` may randomly [result](https://stackoverflow.com/q/6496301/11683) in these things. And [other things](https://stackoverflow.com/q/46835425/11683) too. You should also fix your [SQL injections](https://stackoverflow.com/q/332365/11683). – GSerg Jul 09 '21 at 11:17
  • What I'm trying to implement is a semaphore in an application that has not client-server architecture. It is a PoS, so there are very few connections. So if you have a better implementation, just let me know – Rafa S. Jul 09 '21 at 11:55
  • 1
    You are trying to reinvent [`sp_getapplock`](https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-getapplock-transact-sql)? Are you sure you need it? The way you ensure single use of a resource is placing a lock (`updlock`, `xlock`) on that resource itself, such as a table or some rows. You should however [absolutely not](https://learn.microsoft.com/en-us/sql/relational-databases/sql-server-transaction-locking-and-row-versioning-guide#avoid-user-interaction-in-transactions) do any of that from a transaction where you "show windows and things". – GSerg Jul 09 '21 at 12:09
  • When this was created several years ago, the guy that made it I suppose did not know sp_getapplock. I can try to refactor and change this code and try using sp_getapplock, but I really don't understand why is this failing..... If I don't find an answer, I will try to implement a sp_getapplock – Rafa S. Jul 09 '21 at 12:11
  • 2
    Regardless of what you will rewrite it to, the current code is fundamentally wrong, as a lock may be inserted by someone else after you have checked that it doesn't exist, but before you have inserted your own. See https://stackoverflow.com/q/3407857/11683. – GSerg Jul 09 '21 at 12:16
  • 1
    Locking for such a long time is always going to cause problems. `NOLOCK` doesn't help for inserts, and there may be index rebalancing going on which locks the entire table. You really need `sp_getapplock` with no transaction. @GSerg It's a race condition sometimes known as Time of Check To Time of Use – Charlieface Jul 09 '21 at 12:19
  • Oki doki, I'm going to rewrite the entire thing to use sp_getapplock. I will post the results once customers have tested it. Thank everyone for the help – Rafa S. Jul 09 '21 at 12:26
  • If this is SQL Server then *no* clients can ever communicate directly with the database, they have to communicate with the *service*. Databases like MS-Access, which have no intervening service work like that, SQL Server does not. – RBarryYoung Jul 15 '21 at 13:14
  • Also, implementing your own locking design for a database (and that *includes* using `sp_getapplock`), and abandoning the proper use of transactions (which is the proper locking design for a database) is a really bad idea 99.9% of the time. – RBarryYoung Jul 15 '21 at 13:17

1 Answers1

0

Just to update this, with sp_getapplock I have no problems, so I mark this as an answer.

Rafa S.
  • 35
  • 6