|
dev
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
What transaction IsolationLevel for?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 ---------------------------------------------------------------------------- Show quote "David Thielen" <thielen@nospam.nospam> wrote in message news:8199F471-319C-4CA0-8C84-D04D3EAF3B77@microsoft.com... > 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. > > -- > thanks - dave > david_at_windward_dot_net > http://www.windwardreports.com > 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? Show quote "Sahil Malik [MVP C#]" wrote: > 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 > ---------------------------------------------------------------------------- > > > > > "David Thielen" <thielen@nospam.nospam> wrote in message > news:8199F471-319C-4CA0-8C84-D04D3EAF3B77@microsoft.com... > > 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. > > > > -- > > 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 ---------------------------------------------------------------------------- Show quote "Kevin Yu [MSFT]" <v-k***@online.microsoft.com> wrote in message news:Jj%23Z3OGWGHA.4708@TK2MSFTNGXA01.phx.gbl... > 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." >  |
|||||||||||||||||||||||
Other interesting topics