Transaction locking with Innodb and MysqlTrasnaction

Transaction locking with Innodb and MysqlTrasnaction

Postby KW » Fri 16 May 2008 23:00

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.
KW
 
Posts: 119
Joined: Tue 19 Feb 2008 19:12

Postby Alexey.mdr » Sat 17 May 2008 09:59

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.
Alexey.mdr
 
Posts: 729
Joined: Thu 13 Dec 2007 10:24

One more question about the transaction component

Postby KW » Mon 19 May 2008 19:39

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?
KW
 
Posts: 119
Joined: Tue 19 Feb 2008 19:12

Postby Alexey.mdr » Wed 21 May 2008 08:48

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.
Alexey.mdr
 
Posts: 729
Joined: Thu 13 Dec 2007 10:24

Postby KW » Wed 21 May 2008 21:53

Alexey.mdr wrote: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.
KW
 
Posts: 119
Joined: Tue 19 Feb 2008 19:12

Postby Alexey.mdr » Fri 23 May 2008 08:14

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.
Alexey.mdr
 
Posts: 729
Joined: Thu 13 Dec 2007 10:24

Postby KW » Fri 23 May 2008 18:22

Alexey.mdr wrote: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.
KW
 
Posts: 119
Joined: Tue 19 Feb 2008 19:12

Postby Alexey.mdr » Mon 26 May 2008 16:19

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
Alexey.mdr
 
Posts: 729
Joined: Thu 13 Dec 2007 10:24

Postby KW » Mon 26 May 2008 17:21

Alexey.mdr wrote: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.
KW
 
Posts: 119
Joined: Tue 19 Feb 2008 19:12

Postby Alexey.mdr » Tue 27 May 2008 06:25

Feel free to contact us if you have any problems or concerns with MyDirect .NET.
Alexey.mdr
 
Posts: 729
Joined: Thu 13 Dec 2007 10:24


Return to dotConnect for MySQL