|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Which isolation level to use?I have some problems with scalability of my application and I think it is due to the isolation level I use. Suppose I run the following query from analyzer A: SET TRANSACTION ISOLATION LEVEL SERIALIZABLE BEGIN TRANSACTION UPDATE [TBL_Test] SET [Value] = [Value] * 2 WAITFOR DELAY '00:01:00' COMMIT TRANSACTION This transaction locks all records in the TBL_Test table for about one minute. Now I run the following query from the analyzer B: SET TRANSACTION ISOLATION LEVEL READ COMMITTED BEGIN TRANSACTION SELECT * FROM [TMS_TST_Test] COMMIT TRANSACTION I thought that this query would return the committed data at that time, which is the data before it doubled the value. In fact it doesn't. It blocks until the other transaction is committed and returns the committed data. Of course this is very bad for scalability and performance. I can change the isolation level to READ UNCOMMITTED, but then I may introduce dirty reads. I thought that there would be an isolation level that returned the old data if the transaction hasn't been completed yet. This would be great for scalability and wouldn't introduce dirty reads. I can imagine that you have old data, which might be inconvenient at some times, but in my case it is a lot better then blocking. Am I correct that the requested isolation level is missing from SQL Server and doesn't this exist in other database (i.e. Oracle) too? -- Greetings, Ramon de Klein > UPDATE [TBL_Test] SET [Value] = [Value] * 2 How often are you updating the whole table?> WAITFOR DELAY '00:01:00' Why extend the xlock on the table for a full minute *after* your update is complete? > I thought that there would be an isolation level that returned the old What is the old data? You're updating EVERY ROW in the table. Since you > data > if the transaction hasn't been completed yet. This would be great for > scalability and wouldn't introduce dirty reads. I can imagine that you > have > old data, which might be inconvenient at some times, but in my case it is > a > lot better then blocking. have SERIALIZABLE then the entire table is locked completely until the update is complete. I think the fix is to take out the WAITFOR unless you can explain what the heck it is used for. The WAITFOR is for reproducing the problem only :-) I wish it was that easy.
Our system updates some records in the table that fires a trigger which does other things. The entire operation is done based on triggers, stored procedures and takes a fair amount of time. Updating one table might result in updates on other tables too. During this operation another GUI locks up, because it tries to read _ALL_ data from the table (for creating the navigation tree). Displaying the new data (that might be rolled back) is not appropriate. Displaying old data won't be harmful, because that is the actual current status of the database. Unfortenately, the latter solutions seems impossible in SQL Server. -- Show quoteGreetings, Ramon de Klein "Aaron Bertrand [SQL Server MVP]" wrote: > > UPDATE [TBL_Test] SET [Value] = [Value] * 2 > > How often are you updating the whole table? > > > WAITFOR DELAY '00:01:00' > > Why extend the xlock on the table for a full minute *after* your update is > complete? > > > I thought that there would be an isolation level that returned the old > > data > > if the transaction hasn't been completed yet. This would be great for > > scalability and wouldn't introduce dirty reads. I can imagine that you > > have > > old data, which might be inconvenient at some times, but in my case it is > > a > > lot better then blocking. > > What is the old data? You're updating EVERY ROW in the table. Since you > have SERIALIZABLE then the entire table is locked completely until the > update is complete. > > I think the fix is to take out the WAITFOR unless you can explain what the > heck it is used for. > > > > During this operation another GUI locks up, because it tries to read _ALL_ Why don't you generate the treeview and cache it (I assume it produces HTML > data from the table (for creating the navigation tree). or something) and re-generate it whenever the data is updated. The GUI shouldn't be waiting for triggers. A SQL Server 2005 will have snapshot isolation level (read "the old data", a consistent snapshot).
Currently, you have to minimize locking, making the code snappier and/or work with dirty data. -- Show quoteTibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://www.solidqualitylearning.com/ Blog: http://solidqualitylearning.com/blogs/tibor/ "Ramon de Klein" <RamondeKl***@discussions.microsoft.com> wrote in message news:26357756-DF63-40AC-B4D7-C3E8C05697F9@microsoft.com... > The WAITFOR is for reproducing the problem only :-) I wish it was that easy. > Our system updates some records in the table that fires a trigger which does > other things. The entire operation is done based on triggers, stored > procedures and takes a fair amount of time. Updating one table might result > in updates on other tables too. > > During this operation another GUI locks up, because it tries to read _ALL_ > data from the table (for creating the navigation tree). Displaying the new > data (that might be rolled back) is not appropriate. Displaying old data > won't be harmful, because that is the actual current status of the database. > Unfortenately, the latter solutions seems impossible in SQL Server. > > -- > Greetings, > Ramon de Klein > > > "Aaron Bertrand [SQL Server MVP]" wrote: > >> > UPDATE [TBL_Test] SET [Value] = [Value] * 2 >> >> How often are you updating the whole table? >> >> > WAITFOR DELAY '00:01:00' >> >> Why extend the xlock on the table for a full minute *after* your update is >> complete? >> >> > I thought that there would be an isolation level that returned the old >> > data >> > if the transaction hasn't been completed yet. This would be great for >> > scalability and wouldn't introduce dirty reads. I can imagine that you >> > have >> > old data, which might be inconvenient at some times, but in my case it is >> > a >> > lot better then blocking. >> >> What is the old data? You're updating EVERY ROW in the table. Since you >> have SERIALIZABLE then the entire table is locked completely until the >> update is complete. >> >> I think the fix is to take out the WAITFOR unless you can explain what the >> heck it is used for. >> >> >>  |
|||||||||||||||||||||||
Other interesting topics