Transaction & with(nolock)

Transaction & with(nolock)

Postby erik78 » Mon 21 Jul 2008 08:39

Hello everybody,

I have a problem when I start one transaction and I try to execute a select with(nolock) parameter. It dosen't works.

I don't know if it's possible to execute this types of sentences inside transactions of Unidirect.

If it is possible, what options I must to active when I start a transaction?

Thanks.
erik78
 
Posts: 23
Joined: Thu 14 Feb 2008 10:11

Postby AlexeyB » Mon 21 Jul 2008 16:50

Please, send us test code which produces the mentioned problems.

I tried the following example: one application runs an update within a transaction, other application reads the results simultaneously using (nolock). However, I did not find any problems with that.

First application code:

uniConnection1.Open();
try {

using (UniTransaction transaction = uniConnection1.BeginTransaction()) {

UniCommand updateCommand = uniConnection1.CreateCommand();
updateCommand.CommandText = "UPDATE emp SET ename = ename + '_' WHERE sal > 1000";
updateCommand.ExecuteNonQuery();
DataSet dataSet = new DataSet();
UniCommand selectCommand = uniConnection1.CreateCommand();
selectCommand.CommandText = "SELECT * FROM Emp WITH (NOLOCK)"; // in this case (nolock) is actually not needed
UniDataAdapter dataAdapter = new UniDataAdapter(selectCommand);
dataAdapter.Fill(dataSet);
MessageBox.Show("Data will be rolled back");
transaction.Rollback();
}
}
finally {
uniConnection1.Close();
}


Second application, read the temporary transaction results:

uniConnection1.Open();
try {
UniCommand selectCommand = uniConnection1.CreateCommand();
selectCommand.CommandText = "SELECT * FROM Emp WITH (NOLOCK)";
DataSet dataSet = new DataSet();
UniDataAdapter dataAdapter = new UniDataAdapter(selectCommand);
dataAdapter.Fill(dataSet);
...
}
finally {
uniConnection1.Close();
}
AlexeyB
 

Postby erik78 » Thu 24 Jul 2008 07:17

I am an error, this problem dosen't exist really.

Thanks.

AlexeyB wrote:Please, send us test code which produces the mentioned problems.

I tried the following example: one application runs an update within a transaction, other application reads the results simultaneously using (nolock). However, I did not find any problems with that.

First application code:

uniConnection1.Open();
try {

using (UniTransaction transaction = uniConnection1.BeginTransaction()) {

UniCommand updateCommand = uniConnection1.CreateCommand();
updateCommand.CommandText = "UPDATE emp SET ename = ename + '_' WHERE sal > 1000";
updateCommand.ExecuteNonQuery();
DataSet dataSet = new DataSet();
UniCommand selectCommand = uniConnection1.CreateCommand();
selectCommand.CommandText = "SELECT * FROM Emp WITH (NOLOCK)"; // in this case (nolock) is actually not needed
UniDataAdapter dataAdapter = new UniDataAdapter(selectCommand);
dataAdapter.Fill(dataSet);
MessageBox.Show("Data will be rolled back");
transaction.Rollback();
}
}
finally {
uniConnection1.Close();
}


Second application, read the temporary transaction results:

uniConnection1.Open();
try {
UniCommand selectCommand = uniConnection1.CreateCommand();
selectCommand.CommandText = "SELECT * FROM Emp WITH (NOLOCK)";
DataSet dataSet = new DataSet();
UniDataAdapter dataAdapter = new UniDataAdapter(selectCommand);
dataAdapter.Fill(dataSet);
...
}
finally {
uniConnection1.Close();
}
erik78
 
Posts: 23
Joined: Thu 14 Feb 2008 10:11


Return to dotConnect Universal