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();
}
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();