Synchronization of an object

[excaliber]

Hi all,

 

I've got a minor problem that I think deals with synchronization.

I've got asynchronous socket code working on my server. When it recieves a command, it will call the follow function to perform an asynchronous SQL query on my database:

 

SyncLock pgConn
  strSQL = "SELECT * FROM usertable"

  pgCommand = New CoreLab.PostgreSql.PgSqlCommand(strSQL, pgConn)
  pgDataReader = pgCommand.BeginExecuteReader(AddressOf dbQueryReturn, _netclient, CommandBehavior.Default)
End SyncLock

 

But here is my problem. The asynch command is saving the result to pgDataReader, which is privately declared in the module (yes, module. Bad coding practice :D ). I'm figuring that is bad, as all the results will be stored there and overwrite the last. But I would like to avoid as much overhead and redundant object creation as possible.

 

So with that in mind, I'm in a quandry. Can I put another SyncLock in the callback delegate (dbQueryReturn) on the pgDataReader to prevent other threads from modifying it until it is copyied to a local variable? Wouldn't that negate the benefit of asynch programming though?

 

Should I create a new class instance for each DB query, and keep the dataobjects in there? I was avoiding that because that is a lot of additional overhead (a string, command and reader object per query, plus the class overhead itself).

 

Should I keep a "pool" of datareaders privately declared in the module? As they are used, I can change a state property for each object to used, and pass the index as the state object for the asynch call. Then on the callback delegate, the state object is used to get the datareader object from the pool. When it is done, the object is returned to "free" state. Would also allow me to dynamically increase or decrease the number of pooled readers, depending on load (and thus save some memory)

 

Last idea. Would it be possible to create a new DataReader object, then pass as the state object? Or is that not a "legal move"?

 

Thanks, I really appreciate it.