Multiple connections to a SQL database

[Steven Bates]

Hi, I am attempting to go through a SQL database, find an encrypted string, decrypt it, and then re-encrypt it with a new passphrase. The problem occurs when I try to write to the database while the Reader is still open.

My code roughly looks like:

SqlConnection sc = new SqlConnection("Server=" + dbserver + ";Database=" + db + ";Trusted_Connection=True;MultipleActiveResultSets =True;);
string qs = "SELECT Page, Revision, Content FROM [" + db + "].[dbo].[" + dbtable + "]";
sc.Open();
SqlCommand command = new SqlCommand(qs, sc);
myReader = command.ExecuteReader();

while(myReader.Read())
{
...
qs = "UPDATE [wiki_data_test].[dbo].[PageContent_v2] SET Content=@catContent WHERE Page=@thePage AND Revision=@theRevision";
command = new SqlCommand(qs, sc);
command.ExecuteNonQuery();
...
}

I get the error "Transaction was deadlocked on lock generic waitable...". I tried to create a separate connection just for the writes, but I get an error stating that I cannot connect while the reader is open.

[arie]

MSDN says:

While the SqlDataReader is being used, the associated SqlConnection is busy serving the SqlDataReader, and no other operations can be performed on the SqlConnection other than closing it. This is the case until the Close method of the SqlDataReader is called. For example, you cannot retrieve output parameters until after you call Close.

Changes made to a result set by another process or thread while data is being read may be visible to the user of the SqlDataReader. However, the precise behavior is timing dependent.

So you can't use the same connection for update.

You are getting the "deadlock" error because your Reader sets a lock on the database. It is probably SHARED lock, as your Reader only "reads" data and other connections can "read" as well, but cannot write. And UPDATE command's lock "changes" during execution. First, when it searches in database for the row to update, it has SHARED lock (because it doesn't know if it'll find anything and may not need to change locks for writing), and then, if it finds anything, the lock type is changed. This may cause a deadlock.

Maybe you should use DataSets and DataTables instead of DataReaders?

What SQL database you're using? MSSQL Server? If so, this http://www.sql-server-performance.co...d-sql-locking/ may help you understand your problem.