We're having mysterious network problems with MySQL. Simple update query (updating single row using index) usually runs immediately, then sometimes (let's say 1 in 1000 times) fails with timeout. Same with simple insert query. Database is not overloaded. We suspect network problems and are looking for solution, so if anyone has had this kind of problems I'd definitely like to head about it.
Now to the main point. To make matters worse, insert query is failing in during transaction commit (we have few similar queries, but this happens even in the simplest one - just single insert into single table, no locks, nothing). When it happens, transaction.Commit throws Exception. We catch it and assume transaction was rolled back and retry inserting data again.
The result is two rows with same data in table. What assumptions are possible if Commit fails? Was row inserted or not? Is this behaviour bug in MySql Connector/Net or is it by design. Any advice (beside manually checking if data is in table or not)?
Thanks in advance. Branko
p.s. I posted this same message to MySQL .NET connector forum. I apologize if that bothers anyone, but I'm getting desperate. O:-)