is that thread safe for PgSqlTransaction method?

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for PostgreSQL
Post Reply
Posts: 9
Joined: Sat 07 Jun 2014 01:52

is that thread safe for PgSqlTransaction method?

Post by 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;
                if (pgSqlConnection != null && IsConnected)
                    command = pgSqlConnection.CreateCommand();
                    command.CommandText = cmd;
                    //command.CommandTimeout = 30;

                    //cmd.CommandText = "INSERT INTO public.test (id) VALUES (1)";
                    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();
                    //IAsyncResult cres=command.BeginExecuteNonQuery(null,null);
                    //Console.Write("In progress...");
                    //while (!cres.IsCompleted)
                        //Perform here any operation you need
                    if (cres.IsCompleted)
                        Console.WriteLine("Have to wait for operation to complete...");
                    //int RowsAffected = command.EndExecuteNonQuery(cres);
                    //Console.WriteLine("Done. Rows affected: " + RowsAffected.ToString());
                     //int aff = command.ExecuteNonQuery();
                    //Console.WriteLine(RowsAffected + " rows were affected.");
                    command = null;
                    ThreadPool.QueueUserWorkItem(callback =>
                        Console.ForegroundColor = ConsoleColor.Cyan;
                        Console.WriteLine(RowsAffected + " rows were affected.");
                        Console.WriteLine("sql Write:\r\n" + cmd);
                        log.Info("sql Write:\r\n" + cmd);
                    // 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);
                command = null;



Devart Team
Posts: 2108
Joined: Wed 02 Nov 2011 09:44

Re: is that thread safe for PgSqlTransaction method?

Post by 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.

Post Reply