none
ado.net async operations RRS feed

  • Question

  • Hi, can i execute multiple queries asynchronously against the same dataBase using callbacks, or it could lead to threading problems and i should always wait till the active query execution is finished? 

     

    Thanks,

    Thursday, January 29, 2009 10:32 AM

All replies

  • You can achieve this using Begin.... methods of SqlCommand class. All Begin... (BeginExecuteNonQuery, BeginExecuteReader etc) allow to call statement asynchronously. I do not see any issues with the database in this case.
    Val Mazur (MVP) http://www.xporttools.net
    Thursday, January 29, 2009 5:48 PM
    Moderator
  •  I know i can achieve this using   Begin the question was can i do it multiple times without waiting for each query to return before starting the new query, for example if i will have something like this:

    AsyncCallback callback = new AsyncCallback(Callback1);

    sqlCommand cmd1 = new sqlCommand(adoCon1,"Update products set price = 100 where produce_id = 1");

    cmd1.BeginExecuteNonQuery(callback,cmd1);

    sqlCommand cmd2 = new sqlCommand(adoCon1,"select * from products");

    cmd2.BeginExecuteReader(callback,cmd2);

    sqlCommand cmd3 = new sqlCommand(adoCon1,"Update products set price = 300 where produce_id = 1");

    cmd3.BeginExecuteNonQuery(callback,cmd3);

    won't it cause problems? i ask it because i couldn't find any documantation in msdn claiming that ado commands are thread safe, but i did find something claiming they are not and also the only example i saw was blocking new commands from begin execution until the execution of the first call back.

     

     

    Thursday, January 29, 2009 9:19 PM
  • MSDN documentation states that you cannot execute any command against same SqlCommand until execution finishes. Since you are using different instance of SqlCommand for each query I do not see any issues with multithreading, since nothing is shared. It is same situation as it would be with multiple clients executing queries from different places.
    Val Mazur (MVP) http://www.xporttools.net
    Friday, January 30, 2009 2:54 AM
    Moderator
  • but the same connection is being shared, isn't that a problam? i read here  http://msdn.microsoft.com/en-us/library/ms971481.aspx that:

    ADO.NET is optimized for performance, throughput, and scalability. As a result, the ADO.NET objects do not lock resources and must only be used on a single thread. The one exception is the DataSet, which is thread-safe for multiple readers. However, you need to lock the DataSet during writes.

    so adoConnection is an ADO.NET Object and so must only be used from a single thread according to this document.

    I also understand from here - http://msdn.microsoft.com/en-us/library/8xx3tyca.aspx that even if i create different connections for each command with same connection string i can end up with the same connection, so isn't that a problam? what's the best way to handle this situation? clearing the pool? setting pooling to false in the connection string? is there some document regarding this isue in msdn?

     

     

     

     

    Friday, January 30, 2009 8:34 AM
  • I do not have enough information about thread safety of connection, but in a case of SQL Server 2005 you might enable MARS in your connection string that would allow to work with multiple result sets at same time. If you go with separate connections for each command, you will not end up with same connection even if connection string is the same. Pooling does not mean sharing connections, it means reusing connections when they are released, so I believe you will be safe if you open dedicated connection per each command.
    Val Mazur (MVP) http://www.xporttools.net
    Friday, January 30, 2009 10:58 AM
    Moderator
  • This isue can be critical to my system, so i'm afraid to base my design on a belief

    Do you know where can i post a question to make sure it is 100% safe?

    Thanks,

     

     

    Friday, January 30, 2009 1:39 PM
  • As I stated pooling is no sharing and I do not see any issues with it. If you have some doubts than best way would be to prototype scenario(s), not to rely on documentation anyway.
    Val Mazur (MVP) http://www.xporttools.net
    Monday, February 2, 2009 11:13 AM
    Moderator