20 replies to this topic

[ibad]

Hello, I'm getting a rather bizarre problem.

I am using mysql with c# and .net, visual studio 2005. I have a simple loop to read data based on a query until the results are exhausted:

while(datarader.Read())
{
//do stuff
}

Now...I am trying to go through many thousands of records...and this loop is sort of required to run for more than an hour. It works fine for a while... but in the middle it freezes.

In debug mode I found out that it freezes on the .Read() method, so the culprit is not my own code. if I try to step over Read() it simply stays there.

If I try to make it "continue" it simply returns to the freeze state. It does not terminate the application or throw an exception however.

What is going on and how can I fix it? Thank you for any help!! :)

[FlashM]

This is really strange... Usually I never work with mysql database, but I have successfully run long time consuming queries (8 hours and longer) with no problem.

I think it could also be a problem with database provider. One thing you could try is to set connection time out if this might help?

[ibad]

Well... this loops may have to run for over two hours I think... there are scores of thousands of rows. I will try changing the timeout setting... but a timeout should result in an exception...any other suggestions?

Edited by ibad, 27 November 2009 - 04:26 AM.

[FlashM]

Well, actually I have no clue for this issue you're expiriencing... But I don't think this has anything to do with VS.NET 2005.


You could try to set some CommandBehavior when Executing your datareader:

eg.: SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.SingleRow);

[ibad]

Ok..one possible scenario... if my sql query is like:

select * from table where field1 is null

and in the loop I am putting a value into field 1...would that cause a problem with the reader? Though I think I remember that the problem existed even when field1 was not included in the query...but..just in case.

[ibad]

Ok setting the command behavior to singlerow did not work as it resulted in the loop breaking after one read :(

Maybe I should include an executereader statement at the end of the loop...but that would go to the first row of the result again. any ideas?

[ibad]

Ok...INSIDE my loop I have an update command occuring on a different mysqlconnection...

could this be some form of blocking or deadlock?

[FlashM]

If this other connection is connecting to the same database and trying to update so field that is somehow related to the row you are selecting with your first connection, that most probably yes. Is it possible that you post your loop code and DB schema to get more clear picture of what you are trying to do and why it is blocking?

[ibad]

Yes! the connection is connecting tot he same database and it is trying to update the row that is the current one being read by the loop.

The problem is that this worked fine in an earlier loop that was meant to update something else. And the lock only occurs several minutes into the script, always at the same record, after having processed hundreds of records the same way.

Both the connections are just trying to access the same table. the connection that supplies the Read() method driving the loop gets some text, it gets parsed and processed, and the second connection writes the processed data into a previously null field in the same record.

It's bizarre that this algo has worked for so long and in other situations yet it is locking now. What should I do to make it fool-proof?

here is the code:

conn1.open()
conn2.open()




MySqlCommand cmd = conn.CreateCommand();
MySqlDataReader reader;

MySqlCommand cmd2 = conn2.CreateCommand();
MySqlDataReader reader2;

string qry;

string updateQry;


qry = "SELECT CIK, AccessionNo, Company, Sincerely "
+ "FROM sec_new_name_candidates "
+ "WHERE Sincerely <> '' AND Sincerely IS NOT NULL "


cmd.CommandText = qry;
cmd.CommandTimeout = 100;
reader = cmd.ExecuteReader();

string CIK;
string AccessionNo;
string SECExaminers;
string Sincerely;



while (reader.Read())
{
CIK = reader["CIK"].ToString();
AccessionNo = reader["AccessionNo"].ToString();
Sincerely = reader["Sincerely"].ToString();

SECExaminers = Parse_SECExaminers(ref Sincerely);

if (SECExaminers.Length < 100)
{

updateQry = "UPDATE sec_new_name_candidates SET sec_new_name_candidates.SECExaminers='" + SECExaminers + "' WHERE sec_new_name_candidates.CIK='" + CIK + "' AND sec_new_name_candidates.AccessionNo='" + AccessionNo + "'";

cmd2.CommandText = updateQry;
cmd2.ExecuteNonQuery();
}


Counter++;
}
cmd2.Dispose();
cmd.Dispose();
conn.Close();
conn2.Close();
reader.Dispose();

[ibad]

Viewing the mysql processes this is what I see...

the connection that is driving the loop (with the SELECT statement) turns on as it should..and the update connection also turns on as the update command in the loop is called...

then after a while the select connection dies or goes to sleep and has no query text associated with it anymore (viewing processes in heidi mysql front) but the loop keeps on going and the update command and its connection stay alive and healthy, doing their thing.

My guess is that the select connection dies or freezes and the reader goes on until its cached results last (do readers cache rows?), then it freezes.

[FlashM]

I really don't have the time to investigate the code you posted while I'm at work, but I think that when the first connection is reading the a row, this row gets ReadLock, so other connection is trying to get a WriteLock and they are waiting each other, which means you get a deadlock. It is very common this happes.

Well, I don't do much with databases anyway, but you really should try to update this row with the same connection. Or somehow disable readwrite locking which is not a good idea anyway.

One solution that should work is to select a row with DataAdapter, fill it into DataTable, update this row in DataTable and the use DataAdapter to update this row in database. You will have to use CommandBuilder to get this to work.

If you need more specific instructions, feel free to ask and I will send you some example.

[gaylo565]

In general it isn't good practice to open multiple sql connection objects as they try to use the same resources as each other and can cause problems. The best way to do this is to put the second sql connection in a seperate method and then close the first while calling the second. Then when the method returns you to the original sql method just re-open and continue with handling its data. I am not positive that this is the problem as your code isn't complete enough to really debug well, but I have seen this cause similar problems (although it was with 3 concurent connections and not 2) and would not be surprised if this is the issue.