none
conflicts between foreground and background RRS feed

  • Question

  • I have a program with a lot of database activity (MS SQL Server) performed by a BackgroundWorker. Some database activity has to be performed in the foreground, and this causes some calls to get an error about already an open read cursor.

    Right now I cancel the background work before each foreground call and restart it after each call. This is very cumbersome. Is there any way I can make each database read and write wait for an action in progress and avoid having to stop and start the entire background process?

    Thank you,


    Jon Jacobs, There are 10 kinds of people: those who understand binary and those who don't

    Wednesday, March 7, 2018 3:40 PM

Answers

  • Without seeing your code we're just guessing here but I suspect before your changes you probably stored the connection in a field somewhere and used that field everywhere. Switching your code to simply set that field to a new connection each time doesn't solve the actual problem of not using shared connections. You still are.

    You need to ensure that every place you are using a connection that it creates the connection in a local variable, uses the connection in various calls (passing as a parameter if needed) and then closes it when it is done. This guarantees the various threads won't interact with each other.

    void Foo ()
    {
      //Do not use a field for this...
      using (var conn = CreateConnection())
      {
          //Pass connection to any methods that need it
          MakeACall(conn);
    
          MakeAnotherCall(conn);
      };
    }


    Michael Taylor http://www.michaeltaylorp3.net

    • Marked as answer by Jon Q Jacobs Thursday, March 8, 2018 8:21 PM
    Thursday, March 8, 2018 2:47 PM
    Moderator

All replies

  • Perhaps you can be more specific with what you are doing in regards to database activities. You should not need to cancel a background worker but those who are here to assist can not fully know what to suggest w/o more details and perhaps short code snipplets.

    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Wednesday, March 7, 2018 4:11 PM
    Moderator
  • "this causes some calls to get an error about already an open read cursor."

    The only way that would happen is if you're trying to use the same connection each time. You should never do that. Each DB request should create its own connection to the DB. This eliminates issues with multiple reads, transactions, etc. Under the hood ADO.NET will use connection pooling to optimize the connections so you shouldn't really notice a performance issue.

    ADO.NET does support multiple reads on the same connection but it should be used sparingly. To use it you must enable MARS on the connection string. But if you're turning this on you're probably not using connections properly.


    Michael Taylor http://www.michaeltaylorp3.net

    Wednesday, March 7, 2018 6:32 PM
    Moderator
  • So before each Select, Insert, Delete, and Update I could open a connection and close it after each such call.

    If a call in the background is going on when a user action in the foreground causes another call to the database, it will be on a different connection and not interfere.

    Is that what you are saying?

    Thank you,


    Jon Jacobs, There are 10 kinds of people: those who understand binary and those who don't

    Wednesday, March 7, 2018 7:39 PM
  • >Each DB request should create its own connection to the DB

    Now I open a new connection for each query and close it when done. That has improved things a lot. However, sometimes the connection closed at the end of a foreground query actually belongs to a background query in progress, and sometimes the connection closed at the end of a background query belongs to a foreground query in progress. How can I prevent this?

    Thank you,


    Jon Jacobs, There are 10 kinds of people: those who understand binary and those who don't

    Thursday, March 8, 2018 4:51 AM
  • Without seeing your code we're just guessing here but I suspect before your changes you probably stored the connection in a field somewhere and used that field everywhere. Switching your code to simply set that field to a new connection each time doesn't solve the actual problem of not using shared connections. You still are.

    You need to ensure that every place you are using a connection that it creates the connection in a local variable, uses the connection in various calls (passing as a parameter if needed) and then closes it when it is done. This guarantees the various threads won't interact with each other.

    void Foo ()
    {
      //Do not use a field for this...
      using (var conn = CreateConnection())
      {
          //Pass connection to any methods that need it
          MakeACall(conn);
    
          MakeAnotherCall(conn);
      };
    }


    Michael Taylor http://www.michaeltaylorp3.net

    • Marked as answer by Jon Q Jacobs Thursday, March 8, 2018 8:21 PM
    Thursday, March 8, 2018 2:47 PM
    Moderator
  • >creates the connection in a local variable

    That did the trick! I create an instance for the background tasks and another instance for the foreground tasks. Where a given method could be called by either, I pass in the appropriate instance.

    I have put the application through various high-volume stress-tests, and it performed like a champ!

    Thank you very much.


    Jon Jacobs, There are 10 kinds of people: those who understand binary and those who don't

    Thursday, March 8, 2018 8:26 PM