Transaction & with(nolock)

[erik78]

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.

[AlexeyB]

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]

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

Thanks.

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