What are the risks or performance degradation when using SET TRANSACTION ISOLATION LEVEL REPEATABLE READ?
We have some SP that are called from BizTalk services. We are getting deadlock sometimes. If we change the isolation level, what are the risks and possible perfomance degradations?
Repeatable Read will put locks on all rows that have been fetched. In situations where you are working with cursors fetching large amounts of data this can cause contention with other users because they cannot obtain locks to update any of the rows read by cursors with Repeatable Read until the cursor is closed.
The risk of performance degradation is that transactions may suffer an increased number of timeouts and/or deadlocks. This risk is proportional to the probability that two transactions need to read/update the same rows at the same time. Another factor that can impact your application is the size of lock taken. If locks are taken at a page level then contention may occur if the data different transactions need to access lie on the same page - not necessarily the same row.
On the other hand, when you use a lower isolation level, cursor stability for example, you leave open the possibility that rows you have previously fetched during your transaction may be updated by other transactions before your unit of work has completed.
Try it. There is no way we can tell you what potential risks or performance issues you might run into with a single data point (TRANSACTION ISOLATION LEVEL). We know NOTHING else about your data, data volume, TPS, data dependencies. Whenever performance is a question, try it, measure it. There is no other answer.
To add to what has been said already: You can t eliminate deadlocks altogether. All you can do is minimize the likelyhood for them to happen.
Repeatable read does not lift shared locks after the reads had been made.
This means that if you performed a SELECT in a RR transaction, the concurrent transactions will not be able to update the rows locked by your SELECT.
I m trying to export some tables from SQL Server 2005 and then create those tables and populate them in Oracle. I have about 10 tables, varying from 4 columns up to 25. I m not using any constraints/...
In our database we have an SQL server account that has the correct roles to access some of the databases. We are now switching to windows authentication and I was wondering if we can create a NT user ...
I have been trying to execute a MS SQL Server stored procedure via JDBC today and have been unsuccessful thus far. The stored procedure has 1 input and 1 output parameter. With every combination I ...
Is it possible to use the sqlcmd command to dump table structure the way it can be done with mysqldump?
Hi I have the following SP, however when I use LINQ to SQL it generates 2 multiple recordsets. For my sanity I am trying to fathom out what it is in the stored procedure that is doing this and would ...
I want to create a table in MS SQL Server 2005 to record details of certain system operations. As you can see from the table design below, every column apart from Details is is non nullable. CREATE ...
I love the new DATE datatype in SQL Server 2008, but when I compare a DATE field to a DATETIME field on a linked server (SQL 2005, in this case), like this: DECLARE @MyDate DATE SET @MyDate = CONVERT(...
How to determine if a Transaction is active i.e. before issuing Begin Transaction I want to ensure that no previous transaction are open.. the platform is VB6, MS-SQL Server 2000 and ADO 2.8
