Transaction locking with Innodb and MysqlTrasnaction

[KW]

Hi,

I'm hoping I can get some help. I am trying to lock a record from being viewed by using Select for update. For example:

Code: Select all

 MySqlConnection myConnection = new MySqlConnection(Properties.Settings.Default.Meridian_dboConnectionString);
            myConnection.Open();
            MySqlCommand myCommand = new MySqlCommand();
            MySqlTransaction myTrans;
            // Start a local transaction 
            myTrans = myConnection.BeginTransaction(System.Data.IsolationLevel.ReadCommitted);
            // Assign transaction object for a pending local transaction 
            myCommand.Transaction = myTrans;
            myCommand.Connection = myConnection;
            this.ASqlTransaction = myTrans;
            try
            {
                myCommand.CommandText = "Select * From Reservation Where ReservationID = '" + ReservationID + "' For Update";

                object o = myCommand.ExecuteScalar();

                MeridianTableAdapters.ReservationTableAdapter reservation = new DataAccessLayer.MeridianTableAdapters.ReservationTableAdapter();

                reservation.Connection = myCommand.Connection;

                reservation.FillBy(aset.Reservation, o.ToString());

                FillRelatedRecords(aset);                            
               
            }
            catch 
            {
                throw new Exception("The file is locked, unavailable, or waiting to timeout.");
            }
            finally
            {
               // myConnection.Close();
            }

This does lock the record just fine. I store the local transaction in a global variable called ASqlTransaction - which is later called to unlock the transaction.

However, the record doesn't always unlock when I call ASqlTransaction .Commit();

I'm not quite sure why sometimes it works and sometimes it doesn't not work.

Here is a code snippet of the end transaction call

Code: Select all

  ReservationTableAdapter reservation = new ReservationTableAdapter();

  reservation.Connection = this.ASqlTransaction.Connection;

  reservation.Update(DataSet.Reservation);

 ASqlTransaction.Commit(); 

I'm having problems with the latter code snippet - it doesn't always unlock the record after the transaction has committed. Sometimes the Commit() unlocks the "Select For Update" statement, and sometimes it does not. I'm not sure if there is anything your component is doing or if it is all MySql problem. It could also be my incorrect use of your Transaction compotent. I'd appreciate any advice.

[Alexey.mdr]

Hello,

It's likely this.ASqlTransaction is used incorrectly. Please see the following code snippet:

Code: Select all

...
try { 
   if (this.ASqlTransaction == null)
	   this.ASqlTransaction = myTrans; 
   else
	   throw new ... // already in transaction
   ...
   myConnection.Open(); 
   myConnection.BeginTransaction(System.Data.IsolationLevel.ReadCommitted);
   ...
   // when Commit
   this.ASqlTransaction.Commit();
   this.ASqlTransaction = null; 
} 
catch { 
   // when rollback
   this.ASqlTransaction.Rollback();
   this.ASqlTransaction = null;
   throw new Exception("The file is locked, unavailable, or waiting to timeout.");
} 
finally { 
   myConnection.Close(); 
}

This should solve the problem.

[KW]

Thank you. I do believe that was the case.

I have one more question - What is the proper way to join a transaction with a tableadapter? I have looked at your example I am failing to find an example.

In other words, if i start a transaction

Code: Select all

myConnection.Open(); 
MySqlTransaction MyTransaction = myConnection.BeginTransaction(System.Data.IsolationLevel.ReadCommitted); 

I can then assign this transaction to a mysql command

Code: Select all

myCommand.Transaction = MyTransaction;

Now, if I use a tableadapter, what is the proper way to join a transaction? Do you simple set the TableAdapter instance transaction propery to the transaction? For example,

Code: Select all

SomeTableAdapter MyTableAdapter= new SomeTableAdapter();

MyTableAdapter.Transaction = MyTransaction;

MyTableAdapter.Update(dataset.SomeTable);

    

Is this correct?

[Alexey.mdr]

Hello,

SomeTableAdapter MyTableAdapter= new SomeTableAdapter();
MyTableAdapter.Transaction = MyTransaction;
MyTableAdapter.Update(dataset.SomeTable);

Standard Microsoft TableAdapters doesn't provide this possibility.
There is another option for. You can use MySqlDataTable component.
This component doesn't use standard TableAdapter.
From the MySqlDataTable you can reach the needed *Command property and specify a transaction for this command there.
Hope this helps.

[KW]

MySqlDataTable you can reach the needed *Command property and specify a transaction for this command there.
Hope this helps.

To clarify, to take part in a transaction with MysqlDataTable:

MysqlDataSetInstance.TableInstance.UpdateCommand.Transaction = SomeTransaction;

MysqlDataSetInstance.UpDateAll();

Correct?

///

Side Note, I'm having trouble with the MySqlDataSetGenerator as far as generating the DataSet Project File in another Project besides the one it was created in. MysqlDataSet.Dataset.Designer.cs won't generate for example in another project (type class library).

Perhaps I'm overlooking something but the MSGenerated dataset works fine if I generate the Data Project File in another project. I simply set the 'DataSet Project' and 'DataSetFile' properties in the designer and click the Run Customer Tool on the .xsd file.

In order to use your approach, I need to get your MysqlDataSet tool working with my N-Tier scenario.

[Alexey.mdr]

I wasn't quite right with the “specify” word.
You can only get the transaction that has been set in MySqlConnection.

Code: Select all

protected override DbTransaction DbTransaction {
      get {
        if (connection == null)
          return null;
        else
          return connection.transaction;
      }
      set {
      }
    }

Use MySqlConnection BeginTransaction(), Commit() and Rollback() methods.
Could you please make it clear what you need to accomplish with transactions?
About the DataSet and Data project problem,
could you please provide a step by step instructions to reproduce the behaviour of DataSet wizards?
Or send me some screenshots that may help to reproduce the problem.

[KW]

I wasn't quite right with the “specify” word.
You can only get the transaction that has been set in MySqlConnection

Could you please make it clear what you need to accomplish with transactions?
About the DataSet and Data project problem,
could you please provide a step by step instructions to reproduce the behaviour of DataSet wizards?
Or send me some screenshots that may help to reproduce the problem

I just wanted to join a transaction with a tableadapter. For example, if I start a transaction

Code: Select all

  MySqlTransaction ASqlTransaction = myConnection.BeginTransaction(System.Data.IsolationLevel.RepeatableRead);
MySqlCommand myCommand = new MySqlCommand();            
myCommand.Transaction = ASqlTransaction;
myCommand.Connection = this.ASqlTransaction.Connection;            
myCommand.CommandText = "Select * From atable where id = 1 For Update";    

Now I want to join into that transaction with a tableadapter or MySqlDataTable update command. My initial thought was to set the tableadapter transaction property = to the transaction created in the above code. But this doesn't work -

Code: Select all

TableAdapter SomeAdapter = new TableAdapter();
SomeAdapter.Transaction = asqltrasnaction;

This doesn't seem to be the correct way. Hoping to be advised on proper procedure to join transaction.

///
As far as the side note,

http://www.condoresorts.com/SS.bmp (5mb)

I think that will clarify the problem I'm having with the MySql Dataset generator. It might not be possible to separate the code out. I'm trying to follow the N-Tier design for datasets - http://msdn.microsoft.com/en-us/library/cc178916.aspx

Thank you for your time.

[Alexey.mdr]

Please see the following code:

Code: Select all

MySqlConnection myConnection = new MySqlConnection("host=db;user=root;password=root");
            myDataTable.Connection = myConnection;
            try {
                myConnection.Open();
                myConnection.BeginTransaction(System.Data.IsolationLevel.RepeatableRead);
                myDataTable.Active = true;                

                //modify myDataTable here
                myDataTable.Update();
                myConnection.Commit();
            } catch {
                myConnection.Rollback();
            } finally {
                myConnection.Close();
            }

For the DataSet problem please follow this link:
http://crlab.com/forums/viewtopic.php?p=36152#36152

[KW]

Please see the following code:

Code: Select all

MySqlConnection myConnection = new MySqlConnection("host=db;user=root;password=root");
            myDataTable.Connection = myConnection;
            try {
                myConnection.Open();
                myConnection.BeginTransaction(System.Data.IsolationLevel.RepeatableRead);
                myDataTable.Active = true;                

                //modify myDataTable here
                myDataTable.Update();
                myConnection.Commit();
            } catch {
                myConnection.Rollback();
            } finally {
                myConnection.Close();
            }

For the DataSet problem please follow this link:
http://crlab.com/forums/viewtopic.php?p=36152#36152

I think that clears things up. Thanks.

[Alexey.mdr]

Feel free to contact us if you have any problems or concerns with MyDirect .NET.