ASP .NET WebMethods Synchronization

Question

I have a webmethod in asp.net .asmx service, wich is supposed to check if there are records in a DB, and if there are no records it should add a record

the simplified example of code is like this:

object Mutex = new object();
[WebMethod]
public void InsertIfNotExists(string CLI)
{
    lock (Mutex)
    {
        using (SqlConnection conn = new SqlConnection(ConnectionString))
        using (SqlDataAdapter adapter = new SqlDataAdapter())
        using (DataSet ds = new System.Data.DataSet()){
        {
        //I log with log4net
        logger.Debug("InsertIfNotExists: Start Function: CLI:" + CLI);
        int dummy = 0;

        string sql = "SELECT  * CLI Promote where CLI=" + CLI + " ";
        adapter.SelectCommand = new SqlCommand(sql, conn);
        adapter.Fill(ds);
        DataTable t = ds.Tables[0];

        logger.Debug("InsertIfNotExists: " + t.Rows.Count + " records found for CLI:" + CLI);

        if (t.Rows.Count == 0)
        {
            logger.Debug("InsertIfNotExists: starting to add to table: CLI:" + CLI);

            DataRow dr = t.NewRow();
            dr["CLI"] = CLI;
            dr["DateOfSend"] = DateTime.Now;

            InsertToTable(t, dr, sql);
            logger.Debug("InsertIfNotExists: added to table: CLI:" + CLI + ", starting re-check");

            //checking if exist more then one lines - one more time
            sql = "SELECT  * CLI Promote where CLI=" + CLI + "";
            adapter.SelectCommand = new SqlCommand(sql, conn);
            adapter.Fill(ds);
            t = ds.Tables[0];

            logger.Debug("InsertIfNotExists: re-check for CLI:" + CLI + ", records count=" + t.Rows.Count);             
        }
        logger.Debug("InsertIfNotExists: Finish Function for CLI:" + CLI);
        }
    }
}

Actually it does more checks and logic, that's why I implement it in .net and not in the SQL statement itself, but essentially that is it.

Most of the time the code works well, but sometimes I get into race conditions because of multithreading, though I use lock.

Sample output I got today:

2013-09-15 11:47:14,145 [21] DEBUG Namespace.Service1 InsertIfNotExists: Start Function: CLI: 0501234567
2013-09-15 11:47:14,145 [13] DEBUG Namespace.Service1 InsertIfNotExists: Start Function: CLI: 0501234567
2013-09-15 11:47:14,148 [21] DEBUG Namespace.Service1 InsertIfNotExists: 0 records found for CLI: 0501234567
2013-09-15 11:47:14,148 [21] DEBUG Namespace.Service1 InsertIfNotExists: starting to add to table: CLI: 0501234567
2013-09-15 11:47:14,148 [13] DEBUG Namespace.Service1 InsertIfNotExists: 0 records found for CLI: 0501234567
2013-09-15 11:47:14,148 [13] DEBUG Namespace.Service1 InsertIfNotExists: starting to add to table: CLI: 0501234567
2013-09-15 11:47:14,149 [21] DEBUG Namespace.Service1 InsertIfNotExists: added to table: CLI: 0501234567, starting re-check
2013-09-15 11:47:14,149 [13] DEBUG Namespace.Service1 InsertIfNotExists: added to table: CLI: 0501234567, starting re-check
2013-09-15 11:47:14,154 [27] DEBUG Namespace.Service1 InsertIfNotExists: Start Function: CLI: 0501234567
2013-09-15 11:47:14,157 [27] DEBUG Namespace.Service1 InsertIfNotExists: 2 records found for CLI: 0501234567
2013-09-15 11:47:14,157 [27] DEBUG Namespace.Service1 InsertIfNotExists: Finish Function for CLI: 0501234567
2013-09-15 11:47:14,183 [13] DEBUG Namespace.Service1 InsertIfNotExists: re-check for CLI: 0501234567, records count=2
2013-09-15 11:47:14,184 [21] DEBUG Namespace.Service1 InsertIfNotExists: re-check for CLI: 0501234567, records count=2
2013-09-15 11:47:14,185 [13] DEBUG Namespace.Service1 InsertIfNotExists: Finish Function for CLI: 0501234567


2013-09-15 11:49:19,626 [21] DEBUG Namespace.Service1 InsertIfNotExists: Start Function: CLI:0507654321

What we see here that 3 threads attempted to insert CLI 0501234567 to the table in the parallel. Threads 21 and 13 entered the race conditons and each one inserted 1 record. Then thread 27 also tried to insert a record, but found existing records and exited.

Why did they do it when locked on mutex?

Note: Thread 21 never gets to Finish - I think it is caused by an exception in thread 21, because I try to remove the "additional" rows after the re-check in the real function, and then the second thread which tries to do it should get into an exception. I know it's ugly but that's the only solution I got for now, I'd like to know how to do it properly.

Why does asp.net behave in such a way and what is the proper way to accomplish that task without the race conditions?

Matthew Steeples · Accepted Answer · 2013-09-15 19:24:13Z

3

Your object that the locking is happening on needs to be static otherwise there will be an instance of it for every request that comes in to your service.

answered Sep 15, 2013 at 19:24

Matthew Steeples

8,1434 gold badges39 silver badges53 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

ASP .NET WebMethods Synchronization

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related