is that thread safe for PgSqlTransaction method?

is that thread safe for PgSqlTransaction method?

Postby waertf » Mon 28 Jul 2014 08:43

I have multi thread to access below function
want to know if safe to use
Code: Select all
public void modify(string cmd)
        {
            Stopwatch stopWatch = new Stopwatch();
            PgSqlCommand command = null;
            PgSqlTransaction myTrans = null;
            try
            {
                if (pgSqlConnection != null && IsConnected)
                {
                    //insert
                    command = pgSqlConnection.CreateCommand();
                    command.CommandText = cmd;
                    //command.CommandTimeout = 30;

                    //cmd.CommandText = "INSERT INTO public.test (id) VALUES (1)";
                    //pgSqlConnection.BeginTransaction();
                    //async
                    int RowsAffected;
                   
                    myTrans = pgSqlConnection.BeginTransaction(IsolationLevel.ReadCommitted);
                    command.Transaction = myTrans;
                    //lock (accessLock)
                    {
                        //IAsyncResult cres = command.BeginExecuteNonQuery();
                        //RowsAffected = command.EndExecuteNonQuery(cres);
                        lock (accessLock)
                            RowsAffected = command.ExecuteNonQuery();
                        myTrans.Commit();
                    }
                    //IAsyncResult cres=command.BeginExecuteNonQuery(null,null);
                    //Console.Write("In progress...");
                    //while (!cres.IsCompleted)
                    {
                        //Console.Write(".");
                        //Perform here any operation you need
                    }
                    /*
                    if (cres.IsCompleted)
                        Console.WriteLine("Completed.");
                    else
                        Console.WriteLine("Have to wait for operation to complete...");
                    */
                    //int RowsAffected = command.EndExecuteNonQuery(cres);
                    //Console.WriteLine("Done. Rows affected: " + RowsAffected.ToString());
                   
                     //sync
                     //int aff = command.ExecuteNonQuery();
                    //Console.WriteLine(RowsAffected + " rows were affected.");
                     //command.Dispose();
                    command = null;
                    //pgSqlConnection.Commit();
                    /*
                    ThreadPool.QueueUserWorkItem(callback =>
                    {
                       
                        Console.ForegroundColor = ConsoleColor.Cyan;
                        Console.WriteLine(RowsAffected + " rows were affected.");
                        Console.WriteLine(
                            "S++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++");
                        Console.WriteLine("sql Write:\r\n" + cmd);
                        Console.WriteLine(
                            "E++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++");
                        Console.ResetColor();
                        log.Info("sql Write:\r\n" + cmd);
                    });
                    */
                    stopWatch.Stop();
                    // Get the elapsed time as a TimeSpan value.
                    TimeSpan ts = stopWatch.Elapsed;

                    // Format and display the TimeSpan value.
                    string elapsedTime = String.Format("{0:00}:{1:00}:{2:00}.{3:00}",
                        ts.Hours, ts.Minutes, ts.Seconds,
                        ts.Milliseconds / 10);
                    SiAuto.Main.AddCheckpoint(Level.Debug, "sql modify take time:" + elapsedTime, cmd);

                }

            }
            catch (PgSqlException ex)
            {
                if (myTrans != null) myTrans.Rollback();
                Console.ForegroundColor = ConsoleColor.Red;
                Console.WriteLine("Modify exception occurs: {0}" + Environment.NewLine + "{1}", ex.Error, cmd);
                log.Error("Modify exception occurs: " + Environment.NewLine + ex.Error + Environment.NewLine + cmd);
                Console.ResetColor();
                //pgSqlConnection.Rollback();
                //command.Dispose();
                command = null;


            }


        }
waertf
 
Posts: 9
Joined: Sat 07 Jun 2014 01:52

Re: is that thread safe for PgSqlTransaction method?

Postby Pinturiccio » Thu 31 Jul 2014 08:29

dotConnect for PostgreSQL is not thread safe, so you should use a separate connection for each thread or synchronize the threads yourself.

The synchronization in your code is not enough. You have placed only some parts of code to the 'lock' block. Your code can still cause the situation when several database access operations can still use the same connection simultaneously, for example, two simultaneous transaction openings. You need either to use a separate connection for each thread, or place all the transaction operations to the lock' block.
Pinturiccio
Devart Team
 
Posts: 2024
Joined: Wed 02 Nov 2011 09:44


Return to dotConnect for PostgreSQL