Multiple DataReader per connection

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for PostgreSQL
Post Reply
EvilShrike
Posts: 19
Joined: Mon 14 Mar 2016 17:11

Multiple DataReader per connection

Post by EvilShrike » Fri 08 Dec 2017 16:27

Do I understand correctly that subj isn't support?
So can't create several DataReader in parallel and read from them?
It seems it doesn't.
But anyway currently in such a case NullReferenceException happens which is bad I believe.

Code: Select all

            	var cmd1 = con.CreateCommand();
            	cmd1.CommandText = "select * from t1";
            	var cmd2 = con.CreateCommand();
            	cmd2.CommandText = "select * from t2";
            	DbCommand[] cmds = {cmd1, cmd2};
            	Console.WriteLine("Run parallel commands");
            	Parallel.ForEach(cmds, (cm) => {
            	    var tag = cm.CommandText + ":" ;
            	    Console.WriteLine(tag + "begin:" + System.Threading.Thread.CurrentThread.ManagedThreadId);
            	    using(var reader = cm.ExecuteReader())
            	    {
            	        Console.WriteLine(tag + "execute: " + System.Threading.Thread.CurrentThread.ManagedThreadId);
            	        while(reader.Read())
            	        {
            	            Console.WriteLine(tag + "read");
            	            Console.WriteLine(tag + reader[0]);
            	        }
            	    }
            	    Console.WriteLine(tag + "done");
            	});
            	Console.WriteLine("done");
Unhandled Exception: System.AggregateException: One or more errors occurred. (Object reference not set to an instance of an object.) ---> System.NullReferenceException: Object reference not set to an instance of an object.
at Devart.Data.PostgreSql.e.b(Boolean A_0)

Shalex
Site Admin
Posts: 8245
Joined: Thu 14 Aug 2008 12:44

Re: Multiple DataReader per connection

Post by Shalex » Tue 12 Dec 2017 16:27

PgSqlConnection instance is not guaranteed to be thread safe. You should avoid using the same PgSqlConnection in several threads at the same time. It is recommended to open a new connection per thread and to close it when the work is done. Actually, connections will not be created/disposed every time with the Pooling=true; connection string option - connections will be stored at connection pool. This boosts performance greatly.

A single PgSqlConnection can be used by many PgSqlCommand objects on the assumption that all operations will be done consecutively. In other words, you can not execute a SQL statement while an asynchronous operation is in progress.

Post Reply