How To Lock Record From Read/Write using Transactions

[Guest]

What is desried, during a transaction, with its IsolationLevel set to
RepeatableRead, will it block out other transactions from performing a read
or write to the same record until it's transaction has completed?
See code snippet below:

SqlConnection connection = new SqlConnection(connectionString))
connection.Open();

SqlCommand command = connection.CreateCommand();
SqlTransaction transaction;

// Will this block another transaction from reading/writing same record?
transaction = connection.BeginTransaction(IsolationLevel.RepeatableRead);

command.Connection = connection;
command.Transaction = transaction;
command.CommandText ="SELECT MyTable.OpenForEdit FROM MyTable WHERE
KeyIndex=0";
SqlDataReader reader = command.ExecuteReader();
bool openForEdit = reader.GetBoolean(0);

if (openForEdit == false)
{
command.CommandText = "UPDATE MyTable SET OpenForEdit=1 WHERE
KeyIndex=0;"; // Set OpenForEdit to 1
command.ExecuteNonQuery();
}

transaction.Commit();

// Reset Isolation Level to default
transaction = connection.BeginTransaction(IsolationLevel.ReadCommitted);
transaction.Commit();

 

[Sahil Malik [MVP C#]]

Take out the second BeginTransaction - that is unecessary.

Also, how big is your table, and what kind of indexes do you have in place
on it?

Other than that, all looks good.

- Sahil Malik
http://www.winsmarts.com

 

[Guest]

The table will not become too large. Maybe 5000 to 10,0000 records.
There in only one key index, that being "KeyIndex", as stated in the code
snippet.
The concern is will the value "IsolationLevel.RepeatableRead" disallow
another transaction from reading/writing during an active transaction?

Also looking at Visual Studio 2005 documentation about setting
IsolationLevel (see SqlConnection.BeginTransaction Method) says the following:
After a transaction is committed or rolled back, the isolation level of the
transaction persists for all subsequent commands that are in autocommit mode
(the SQL Server default). This can produce unexpected results, such as an
isolation level of REPEATABLE READ persisting and locking other users out of
a row. To reset the isolation level to the default (READ COMMITTED), execute
the Transact-SQL SET TRANSACTION ISOLATION LEVEL READ COMMITTED statement, or
call SqlConnection.BeginTransaction followed immediately by
SqlTransaction.Commit.

So shouldn't the IsolationLevel be set back to it's defaut? Or does it do
this when the transaction completes?

 

[Sahil Malik [MVP C#]]

Scott -

So there are two questions you have, let me answer them one by one.

The second Q first ~

Calling BeginTransaction(IsolationLevel) - will in effect create a nested
transaction. And nested transactions are weird in SQL Server i.e. Commit
reduces Trancount by one, and rollback takes it to zero. Calling
BeginTransaction is the equivalent of calling BEGIN TRANSACTION - with some
other goo thrown in.

When you call BeginTransaction ~ "BEGIN TRANSACTION" ~ you are no longer in
the autocommit mode of transactions. You are now in the explicit transaction
mode. The other two modes are implicit and batch scoped. So the "connection"
(not the transaction) will revert to "autocommit" when your transaction
completes. This means, subsequent transactions will automatically run under
"ReadCommitted" unless you ask for an exception.

Thus, the second call to BeginTransaction is not only unecessary ~ it is
also detrimental .

Now the first Q second ~

RepeatableRead will indeed lock the rows that you have *read*. This is one
step below Serializable where all your rows that match the predicate are
locked in advance. RepeatableRead will start locking data as it considers
necessary. So as you are iterating through your DataReader, it will go on
locking more and more rows ~ pages ~ maybe even the entire Table. Locking is
a bit like going to Burger King - you drive upto a suggestion box, and ask
for what you want, and then they give you .. what they think you should
rather have (wink wink). In other words, it is really the database's
decision on what is locked, and what isn't. But RepeatableRead guarantees
"Repeatable Reads" - in other words, if there is some data that you have
read, you are guaranteed to get the same "values" back, within the same
transaction, because your transaction will block all other requests (due to
the locks placed on the data that you have read). So, you are able to
*repeat* your reads. Note that phantom reads are still possible, because
only the data is locked, "anything that may match the predicate" is not
locked.

Of course the downside is blocking for other concurrent requests. If you
need RepeatableReads with non-blocking behavior, the answer is "Snapshot" in
SQL2k5. And if you don't mind snapshot with non-repeatable reads, then the
right answer is Snapshot Read Committed. You do not have these options in
SQL2k, but you have something similar to this in Oracle 8i by default. You
can however simulate Snapshot in SQL2k using some clever data layer code.

I usually blog about this a lot (http://blah.winsmarts.com) along with other
junk, and I recently wrote a chapter on transactions in Andrew Brust's SQL
Server 2005 book by MSPress. I'd recommend reading them ;-)

Hope this helps, whew long answer.

- Sahil Malik
http://www.winsmarts.com