What transaction IsolationLevel for?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi;

What IsolationLevel is best for transactions for the following (by sub-row I
mean a pk:fk to a second table that has N rows in the second table that
"belong" to a row in the first table):

0) A select for a single row and it's 0 - 5 sub-rows.
1) A select where I am returning 1 - 10 rows - and each row has 0 - 5
sub-rows?
2) An insert of a row and it's 0 - 5 sub-rows.
3) An update of a row. I delete all sub-rows and then insert the sub-rows
for the update. I first read the row and compare timestamps throwing an
exception if they do not match (another user updated between this user's read
and write).
4) A delete of a row and it's sub-rows.
 
Well, this much information makes it impossible to answer this Q.

You've gotta ask yourself for each of the operations below, do you mind
dirty reads? Do you need repeatable reads? Do you mind phantom reads? What
kind of blocking penalty are you willing to pay?

And between the pk/fk queries (2 queries), do you need locking in advance
(to ensure consistency between the two queries).

Bottomline, this is an area with a lot of gray in it - no black or white.
And the only way you can answer this question, is to grab a book and read
about transactions, and understand the consequences and benefits of each
isolation level.

- Sahil Malik [MVP]
ADO.NET 2.0 book -
http://codebetter.com/blogs/sahil.malik/archive/2005/05/13/63199.aspx
 
Hi;

I've read a bit and I think RepeatableRead is what I need - but I am by no
means certain. I definitely want consistency across everything I read. This
is for a web app so after I complete a read, the next read could be 2 seconds
later, or 2 weeks for a given user.

I am handling two user's editing the same record by checking the row's
timestamp before doing an update and not allowing the second one. So I need
consistency across the select (to read the timestamp) and update. But I do
not need locking for a row that was read and is presently being edited in a
web page.

Does this help any?

--
thanks - dave
david_at_windward_dot_net
http://www.windwardreports.com
 
Hi dave,

Yes, I think you can use RepeatableRead as IsolationLevel. The
IsolationLevel works within the transaction and will allow others to select
or update during one user is modifying on the webpage.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."
 
Whoaa .. hold on !! :)

Let me make sure I understood your scenario first ---

Your web page reads data, including timestamp, then you disconnect from the
db, the user takes 2 seconds - 2 weeks, and then you update modifications
back into the db, and there is where you check for timestamp (during
update).


I think, for this scenario, the more appropriate isolation level will be
ReadCommitted, not RepeatableRead.

Why?

If you have a transaction block thta looks like this

BEGIN TRAN
Select
Update
COMMIT

... Then yes, you would want RepetableRead, because between the Select and
Update, you want to ensure repetable reads - thus ensuring that any other
transaction doesn't screw up what you read out of the Select statement ..
right?


BUT ..

really your transaction block looks like this ..

BEGIN TRAN
Update
COMMIT

The Update Query itself has a where clause, and SQL Server guarantees data
consistency over the lifetime of the query execution, so a simple Update
Query, with the timestamp in the where clause .. under ReadCommitted, should
do the trick :).

Of course the next Q is, "What if two updates are issued together"?

Well, even then, SQL Server will automatically serialize them in an
execution order - they never execute together.

So, my vote is for ReadCommitted - lower cost, same effect. What am I
missing heya?

- Sahil Malik [MVP]
ADO.NET 2.0 book -
http://codebetter.com/blogs/sahil.malik/archive/2005/05/13/63199.aspx
 
Back
Top