Concurrency Control in ADO.NET using Threads and Monitors

The concurrency controls system refers to the lowest level in architecture of a DBMS. It is relative to the input/output operations, which carry out the transfer of blocks from the secondary memory to the main memory and vice versa. Consider read and write actions. Each read operation consists of the transfer of a block from the secondary memory to the main memory, and each write operation consists of the opposite transfer. The read and write operations are managed by a module of thesystem generally known as the scheduler, which determines whether the requests can be satisfied.

Anomalies of concurrent transactions

The simultaneous execution of varios transactions can cause problems, termed anomalies; theri precedence causes the need of a concurrency control system.. There are three typical cases:

• Update loss
• Inconsisten Read
• Ghost Update

The following example uses the class System.Monitor to access in a secuential way the blocks of code by blocking and singnaling. For example we have 3 methods that interact with a database and we do not want to read while we are in the middle of an update operation. The monitor ensures that the code will be executed as a transaction. So it will lock the resources while the monitor is being used.

The steps you will need to follow to use concurrency control with ADO.NET in the following example are focused on the concurrency control:

Step 1:  You will need to use the following header files

using System.Data;
using System.Data.OleDb; // If you are using SQL Server change this line
using System.Threading;

Step 2:  Make sure your database connection, datasets and datareaders are properly configured.

Step 3: The methods where you want to restrict access to one thread at a time should begin with the following sentence Monitor.Enter(this); and should release the Monitor with the sentence Monitor.Exit(this); the code that is executed between this two sentences will be executed as a block and will restrict the access to one thread at a time.

Step 4:  Once you have declared your blocks of code with the Monitor, then you need to declare your methods as threads. First you will need to declare them like this:

ThreadStart myThread = new ThreadStart(someMethod);
Thread thread_A = new Thread(myThread);
thread_A.Start();

After you declare inside of your methods the blocks of code that will be executed with the monitor and declared the threads and instantianting them you are using threads as a mechanism of concurrency control. The following methods are an example of the Insert, Update and Delete operations to a database using ADO.NET with concurrency control using monitors.

Complete Example Source Code

Insert Method

public void insertDB()
{
Monitor.Enter(this); // Locks the monitor
txtLog.AppendText("\n>>>Monitor LOCK>>>");
comando.Connection = oleDbConnection1;
comando.CommandText = "INSERT INTO Sintomas(Sintomas) VALUES ( ' "+ txtContent.Text + " ' )" ;
int count = 0;
int lockingTime = 2500;
count = comando.ExecuteNonQuery();
txtLog.AppendText("\nSQL Command >> INSERT INTO Sintomas(Sintomas) VALUES ( ' "+ txtContent.Text + " ' )");
txtLog.AppendText("\nThe database will be locked for the next seconds.");
Thread.Sleep(lockingTime); // Sleep the thread to lock the database for a moment
Monitor.Exit(this); // Realeases the monitor
txtLog.AppendText("\n<<<Monitor UNLOCK<<<");
} 

Delete Method

public void deleteDB()
{
Monitor.Enter(this);
txtLog.AppendText("\n>>>Monitor LOCK>>>");
comando.Connection = oleDbConnection1;
comando.CommandText = "DELETE * FROM Sintomas WHERE ID=" + txtID.Text ;
int count = 0;
int lockingTime = 2500;
count = comando.ExecuteNonQuery();
txtLog.AppendText("\nSQL Command >> DELETE * FROM Sintomas WHERE ID=" + txtID.Text);
txtLog.AppendText("\nThe database will be locked for the next seconds." );
Thread.Sleep(lockingTime);
Monitor.Exit(this);
txtLog.AppendText("\n<<<Monitor UNLOCK<<<");
}

public void updateDB()
{
try
{
Monitor.Enter(this);
txtLog.AppendText("\n>>>Monitor LOCK>>>");
comando.Connection = oleDbConnection1;
comando.CommandText = "UPDATE Sintomas SET Sintomas = ' " + txtContent.Text + " ' WHERE ID=" + txtID.Text ;
int count = 0;
int lockingTime = 2500;
count = comando.ExecuteNonQuery();
txtLog.AppendText("\nSQL Command >> UPDATE Sintomas SET Sintomas = ' " + txtContent.Text + " ' WHERE ID=" + txtID.Text );
txtLog.AppendText("\nThe database will be locked for the next seconds." );
Thread.Sleep(lockingTime);
Monitor.Exit(this);
txtLog.AppendText("\n<<<Monitor UNLOCK<<<");
}
catch(Exception ex){MessageBox.Show("ERROR: " + ex.ToString());}
}

Update Method

private void btnInsert_Click(object sender, System.EventArgs e)
{
ThreadStart insert = new ThreadStart(insertDB);
Thread t_Insert = new Thread(insert);
t_Insert.Start();
}

private void btnDelete_Click(object sender, System.EventArgs e)
{
ThreadStart delete = new ThreadStart(deleteDB);
Thread t_Delete = new Thread(delete);
t_Delete.Start();
}

private void btnUpdate_Click(object sender, System.EventArgs e)
{
ThreadStart update = new ThreadStart(updateDB);
Thread t_Update = new Thread(update);
t_Update.Start();
}

Bibliography

• Atzeni, Paolo & Ceri, Stefano & Paraboschi, Stefano & Torlone, Riccardo. Database Systems. Ed. McGrawHill 1999 UK p.p.287-289.