Transaction is not working properly for some rare cases

[newtomysql]

Dear All,
I have this application which is using Innodb engine and .net connector 5.0.8.1. So I am using C# for my coding. So I want to run transaction base appplication where either all my sql statement go through or all fail. So the funny part I notice it works well for most of my cases but for certain cases only the second sql statement go through. I really cant figure out why is this happening. I have attached the code below. So the problem there is that my myInsertQuery1 goes through but not my myUpdateQuery1 ? So why is this happening. I have separately attached below my function for the transactionConnectionLocal1 where I keep the connection details. The rare cases are like 5 out of 400 records.

int rollbackBoolean = 0;
MySqlTransaction transactionLocal1 = null;
MySqlConnection connectionLocal1 = null;
transactionConnectionLocal1 callTransactionConnectionLocal1 = null;
try
{
callTransactionConnectionLocal1 = new transactionConnectionLocal1();
connectionLocal1 = callTransactionConnectionLocal1.localConnection1;
connectionLocal1.Open();
transactionLocal1 = connectionLocal1.BeginTransaction();
}
catch (MySql.Data.MySqlClient.MySqlException ex)
{
rollbackBoolean = 1;
MessageBox.Show("Error From Database Connection " + ex.Message);
}
catch (System.Net.Sockets.SocketException ex)
{
rollbackBoolean = 1;
MessageBox.Show("Error Sockets From Database Connection " + ex.Message);
}

String myUpdateQuery1 = "Update tblStock " +
"Set tblStock.stockStatus = 'b'" +
"Where tblStock.stockSerial ='" + serial + "'" ;
MySqlCommand myCommand1 = new MySqlCommand(myUpdateQuery1);

try
{
myCommand1.Connection = connectionLocal1;
myCommand1.Transaction = transactionLocal1;
myCommand1.ExecuteNonQuery();
}
catch (MySql.Data.MySqlClient.MySqlException ex)
{
rollbackBoolean = 1;
MessageBox.Show("Error From myUpdateQuery1 " + ex.Message);
}
catch (System.Net.Sockets.SocketException ex)
{
rollbackBoolean = 1;
MessageBox.Show("Error Sockets From myUpdateQuery1 " + ex.Message);
}
finally
{
myCommand1.Dispose();
}

String myInsertQuery1 = "Insert into tblTemp " +
"Set stockSerial ='" + serial+ "', " +
"tempTimeStamp='" +
DateTime.Now.ToString(("yyyy:MM:dd HH:mm:ss")) + "';";
MySqlCommand myCommand2 = new MySqlCommand(myInsertQuery1);

try
{
myCommand2.Connection = connectionLocal1;
myCommand2.Transaction = transactionLocal1;
myCommand2.ExecuteNonQuery();
}
catch (MySql.Data.MySqlClient.MySqlException ex)
{
rollbackBoolean = 1;
MessageBox.Show("Error From myInsertQuery1 " + ex.Message);
}
catch (System.Net.Sockets.SocketException ex)
{
rollbackBoolean = 1;
MessageBox.Show("Error Sockets From myInsertQuery1 " + ex.Message);
}
finally
{
myCommand2.Dispose();
}

if (rollbackBoolean == 1)
{
transactionLocal1.Rollback();
}
else
{
try
{
transactionLocal1.Commit();
}
catch (MySql.Data.MySqlClient.MySqlException ex)
{
try
{
transactionLocal1.Rollback();
}
catch (MySqlException ex1)
{
MessageBox.Show("An exception of type " + ex.GetType() +
" was encountered while inserting the data.");
if (transactionLocal1.Connection != null)
{
MessageBox.Show("An exception of type " + ex1.GetType() +
" was encountered while attempting to roll
back the transaction.");
}
}
}
catch (System.Net.Sockets.SocketException ex)
{
rollbackBoolean = 1;
MessageBox.Show("Error Sockets From Commit Process " + ex.Message);
}
finally
{
connectionLocal1.Close();
}
}

// connection details function
using System;
using System.Collections.Generic;
using System.Text;
using MySql.Data.MySqlClient;

namespace mSytemNonFranchise
{
public class transactionConnectionLocal1
{
public MySqlConnection localConnection1;
public MySqlCommand command;

public transactionConnectionLocal1()
{
this.localConnection1 = new
MySqlConnection("Address='localhost';Database='localDB';User
Name='root';Password='local12';Pooling='false'");
// this.command = this.localConnection1.CreateCommand();
// this.localConnection1.Open();
}

// destructor - explicitly
~transactionConnectionLocal1()
{
//this.command.Dispose();
this.localConnection1.Close();
this.localConnection1.Dispose();
}
}
}

 

[whosrdaddy]

2 remarks:

- post this in the C# forum.
- I think there's something wrong with the update query syntax:

 "Update tblStock " + 
 "Set tblStock.stockStatus = 'b'" + // you are missing a space here
 "Where tblStock.stockSerial ='" + serial + "'" ;

one way to prevent this, always begin each line with a space.

 " Update tblStock " + 
 " Set tblStock.stockStatus = 'b'" +  
 " Where tblStock.stockSerial ='" + serial + "'" ;

/Daddy

-----------------------------------------------------
What You See Is What You Get
Never underestimate tha powah of tha google!

 

[newtomysql]

Dear Daddy,
I have posted in C# forum as per you request. The problem here is that if you say my code is having error then why it work for most of my cases except for some rare cases?

 

[ingresman]

i just tried an update in the mysql tool and it doesn't mind if there is a space between the " and the where, so that's probabbly not your answer.
I think you did answer it yourself in your opening comment in your other thread about using transsaction managment in .NET.
You look like your trying to do a 2-phase commit which is always a nightmare.
It is interesting why you don't get an error message back from the failing update though.

 

[newtomysql]

Dear Ingresman,
Yes I agree I need a kind of 2-phase commit. I am also looking out for the error and surprisingly this type of problem only occurs like for 5 out of 400 records. So is a very difficult problem to solve I guess because the error is not significant and I cant decide on where is the source of the error? I mean from your experience what can be the cause can it be due to network or hardware ? Thanks.

 

[ingresman]

It's really diffcult to say if no error is thrown. Essentuialy a query either works or it doesn't. But remember the basic oddness of SQL is you will get a zero response code to indicate a query worked even if no rows are changed. Whicj is why you need to check both on occasion. This is ok once you get used to it but is in sharp contract to say COBOL when you get a 23 response code for record not found or 22 for key already exists.
So you might not be getting an error because no error exists just that there were no rows to update.
Any thoughts around this ?

 

[newtomysql]

Dear Ingresman,
So if I understand you carefully is it that after each of the update statement capture the status value from the sql statement is it ? Then based on that I will decide to commit or rollback is it ? Thanks.

 

[ingresman]

Sorry to take so long coming back.
Yes, it might be worthehile logging these away to a file with keys, SQL etc and then you have some information to cross check when you get these failures.
However looking at your code you do seem to catch the errors, do you ever get them displayed. Make sure you do capture the SQL your going to execute I'm still thinking it might be a data issue

 

[newtomysql]

Dear Ingresman,
Is ok I still value your answer even is late no problem man. I dont get you what do you want me to log the sql statement or the result based on the sql whether it did successfully or not? I am catching the error in the catch part but the funny this it never came out with any error because if it would then I would by now captured it and know the source of it. What do you mean by data issue you mean data type ?

 

[ingresman]

Logging:
Just before you issue the SQL i.e. at an executenonquery
write the sql query string e.g. myinsertquery1 and write this to a simple serial file. If you can get the SQL result code and the rows effected as well write these away as well. You will probbably have to open the file for append to simply write to the end of the file. you should also log if you do a rollback as well. An interesting thing to remember is most database will rollback if you disconnect before doing a commit, it assumes it has failed.
I'm as confused as you why if you seem to get a zero response but the update is not working

 

[newtomysql]

Dear Ingresman,
Yes I have been logging all my sql statement using mysql database's internal log system. I can even show you even yesterday one incident of this sort happen. Where in the log it shows both my sql have is between the begin and commit statement. But then when I checked is not there? So what do you suspect that means the query ran successfully without error but there is no result generated is it ?

 

[ingresman]

please show some diagnostics it might give some insight.
Have you condiered re-writign the code to take away all the catch/try/finally's and just do standard ado.net error handling ?

 

[newtomysql]

Dear Ingresman,
Sure is a bit long but part of my log is below I have put for 2 cases one is for productID=1592 and the other is 1068. I dont understand what are you saying about re-writing the code ? What is just standard Ado.net ? I am reall lost can you guide me how I should rewrite my code.

080804 14:40:21 207 Connect root@localhost on mpcms4
207 Query SHOW VARIABLES
207 Query SHOW COLLATION
207 Query SET character_set_results=NULL
207 Init DB mpcms4
207 Query Select tblProduct.productTotalStock, tblProduct.productBooked, tblProduct.productCode, tblProduct.productName, tblProduct.productPrice, tblProduct.rrpMarkUpType, tblProduct.rrpMarkUpFixed, tblProduct.rrpMarkUpAmount, tblProduct.rrpMarkUpPercentage, tblProduct.productTotalAmount From tblProduct Where productID=1592
208 Connect root@localhost on mpcms4
208 Query SHOW VARIABLES
208 Query SHOW COLLATION
208 Query SET character_set_results=NULL
208 Init DB mpcms4
208 Query SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
208 Query BEGIN
208 Query Update tblProduct Set tblProduct.productBooked = tblProduct.productBooked + 1 Where tblProduct.productID=1592
208 Query Insert into tblTempReceipt Set productType='Accessory', productID=1592, stockID ='6', stockSIQ ='1', employeeID = '9', tempReceiptTimeStamp='2008:08:04 02:40:21'
208 Query COMMIT
208 Quit
207 Quit

080804 14:51:50 214 Connect root@localhost on mpcms4
214 Query SHOW VARIABLES
214 Query SHOW COLLATION
214 Query SET character_set_results=NULL
214 Init DB mpcms4
214 Query Select tblProduct.productTotalStock, tblProduct.productBooked, tblProduct.productCode, tblProduct.productName, tblProduct.productPrice, tblProduct.rrpMarkUpType, tblProduct.rrpMarkUpFixed, tblProduct.rrpMarkUpAmount, tblProduct.rrpMarkUpPercentage, tblProduct.productTotalAmount From tblProduct Where productID=1068
215 Connect root@localhost on mpcms4
215 Query SHOW VARIABLES
215 Query SHOW COLLATION
215 Query SET character_set_results=NULL
215 Init DB mpcms4
215 Query SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
215 Query BEGIN
215 Query Update tblProduct Set tblProduct.productBooked = tblProduct.productBooked + 1 Where tblProduct.productID=1068
215 Query Insert into tblTempReceipt Set productType='Accessory', productID=1068, stockID ='7', stockSIQ ='1', employeeID = '9', tempReceiptTimeStamp='2008:08:04 02:51:50'
215 Query COMMIT
215 Quit
214 Quit

 

[ingresman]

Shame it doesn't show response codes.
I think what your trying to so is issue 3 SQL statmenents against a single database. You want these 3 updates to be part of the same transaction i.e. they either all

work or they don't work.
(I misunderstood originaly in that I thought there were 3 different databases).
I think if you have a good understand of the C# syntax and how the objects hang together the way you've approached it would probabbly be ok. But as it isn't doing

ehat you want to do it isn't !
What I mena by normal ado.net would be to do is something like.

using System;
using MySql.Data.MySqlClient;

class testmysql
{
 public static void Main()
 {
  string myConnectionString;
  MySqlTransaction myTrans;

 
  myConnectionString = "data source=localhost;user id=****;password=****;database=postcodes";
  MySqlConnection myConnection = new MySqlConnection(myConnectionString);
  myConnection.Open();

 MySqlCommand  myCommand = myConnection.CreateCommand();
 myTrans = myConnection.BeginTransaction();
 myCommand.Connection = myConnection; 
 myCommand.Transaction = myTrans;

 myCommand.CommandText = "insert into fred values (2)";
 myCommand.ExecuteNonQuery(); 
 myTrans.Commit();
}
}

I'm not a C# programmer so this might be rubbish, an I havn't trapped the response code. It might be worthwhile posting in the C# forum.

 

[newtomysql]

Dear Ingresman,
I think let me clear you actually what happens is that only "Update tblProduct Set tblProduct.productBooked = tblProduct.productBooked + 1 Where tblProduct.productID=1592" is not giving the result but the other Insert is working. So both of them are in one transaction but not the select statements. Is the same for the second Update statement also.

208 Query BEGIN
208 Query Update tblProduct Set tblProduct.productBooked = tblProduct.productBooked + 1 Where tblProduct.productID=1592
208 Query Insert into tblTempReceipt Set productType='Accessory', productID=1592, stockID ='6', stockSIQ ='1', employeeID = '9', tempReceiptTimeStamp='2008:08:04 02:40:21'
208 Query COMMIT

 

[ingresman]

And you definitly have a tblproduct.productid = 1592 ?
so at line 207 in your trace you are defintly getting a row returned ?

 

[newtomysql]

Dear Ingresman,
Yes that select is in another function when it exist only then it will take to another function where I run the transaction which consist of the insert and update queries. Thanks.

 

[ingresman]

in that case I am stumped ! (and I don't get stumped that often !). Unless you have a corrupt table or index I can offer no more advice,
sorry

 

[newtomysql]

Dear Ingresman,
If you are stunned then I am dead hehe. So what can possible be the problem. I am also really lost because it is a very rare problem only happen once in a while so is not a consistent problem either.Thanks for looking into my problem too.