Hot answers tagged isolation-level
11
is because it has to write to cache and then to the TempDb (row version) and then it can return to the caller.
No, this is incorrect. It somehow implies that writes in the presence of versioning have higher latency as each write has to touch the disk (for tempdb) which is not true. The write into the tempdb is also a write into 'cache'. The only ...
9
I think the best approach for you would be to actually expose your module to high concurrency and see for yourself. Sometimes UPDLOCK alone is enough, and there is no need for HOLDLOCK. Sometimes sp_getapplock works out very well. I would not make any blanket statement here - sometimes adding one more index, trigger, or indexed view changes the outcome. We ...
8
Just addressing the SERIALIZABLE isolation level aspect. Yes this will work but with deadlock risk.
Two transactions will both be able to read the row concurrently. They will not block each other as they will either take an object S lock or index RangeS-S locks dependant on table structure and these locks are compatible. But they will block each other when ...
7
The setting to disable page locking applies per index, so applying this change to the clustered index only affects execution plans that access the data via that index. If there are nonclustered indexes on the table, you may have to disable page locking for them as well. The following script demonstrates this:
CREATE TABLE dbo.LockTest
(
col1 ...
7
In this particular case the addition of a UPDLOCK lock to the SELECT would indeed prevent anomalies. The addition of HOLDLOCK isn't necessary as an update lock is held for the duration of the transaction, but I confess to including it myself as a (possibly bad) habit in the past.
Imagine doing something more legitimate than an ID update, some
...
7
In an ideal world you would have two choices, SNAPSHOT and READ COMMITTED SNAPSHOT (RCSI). Make sure you understand the basics of transaction isolation levels before you decide which is appropriate for your workload. Specifically be aware of the different results you may see as a result of moving to RCSI.
This sounds like it's not an ideal world as you ...
6
Correct, use SNAPSHOT isolation to get consistent, commited data from before the transaction started.
The READ UNCOMMITTED isolation (aka NOLOCK hint) will read dirtz, inconsistent data
When you enable SNAPSHOT isolation, then it takes effect for all SELECTs going forward. You run ALTER DATABASE with READ_COMMITTED_SNAPSHOT in this case
Edit: added link ...
6
TRUNCATE table will take a SCH-M lock on the table.
Even at READ UNCOMMITTED level the SELECT query will need to take a SCH-S lock. This won't be possible until the TRUNCATE transaction has ended and the conflicting SCH-M lock has been released.
5
As the SELECT is uncommitted, does it start executing before the DELETE is committed?
No, T-SQL statements always execute sequentially in SQL Server. The point is the SELECT may read uncommitted changes made by other concurrently-executing transactions.
Would this mean that removing NOLOCK should stop the error?
Yes, but only because error 601 is ...
5
No. It doesn't affect anything regarding the internal management within SQL Server. You're setting that connection for you, for your queries. SQL Server manages it's own locking it's own way.
Why would you turn off page and row locking on an index? You're more likely to see more severe locking than if you let SQL Server manage that index as it sees fit. By ...
4
I believe it will mean the same thing for DDL as it does for DML. The msdn article on the topic actually gives you a pretty clear idea under the SERIALIZABLE section:
This option has the same effect as setting HOLDLOCK on all tables in all SELECT statements in a transaction.
Basically as long as your transaction is running, no DDL can be performed on ...
3
The difference lies between a query and a transaction. A transaction can contain any number of queries. To illustrate the difference, I set up a small example:
CREATE TABLE table_to_be_updated (
id serial PRIMARY KEY,
other_column text,
column_changing text
);
INSERT INTO table_to_be_updated (other_column, column_changing)
VALUES
('value', ...
3
What if some other transaction updates a record so that it would match with my WHERE clause in the UPDATE statement, thereby violating the REPEATABLE READ.
That would be SERIALIZABLE READ, not REPEATABLE. REPEATABLE only guarantees that the rows that qualified for the WHERE clause the first time will not disappear or be modified. It doesn't say anything ...
3
I'd suggest you read the following question and its answers: Database locking issues?.
Finding the right isolation level to use at the db level is the fastest thing that you can do right now to help you fix this issue, because it's difficult now to change all applications that touch the database and change their code. Since you said "I don't have any ...
3
For most databases including Data Warehouses Read Committed is a sufficient isolation level. I wouldn't use Read Uncommitted due to the possibility of incorrect answers. On SQL Server we can also add Read Committed Snapshot Isolation. This versions rows and avoids queries being blocked by updates. Don't know if you have something similar on DB2.
3
The following behavior may be caused by missing indexes on referring side of your FKs: "the price changes take approx 1 hour to process (vs. 1-2 minutes) and sys.dm_tran_locks shows the transaction taking almost 90,000 different locks, compared to around 100-150 when foreign keys were being dropped/recreated"
When a row is deleted or its PK/Unique is ...
2
NEW ANSWER (MySQL-style dynamic SQL):
Ok, this one tackles the problem in the way one of the other poster's described - reversing the order in which mutually incompatible exclusive locks are acquired so that regardless of how many occur, they occur only for the least amount of time at the end of transaction execution.
This is accomplished by separating the ...
2
When you set the transaction level without the way you do it applies the Read Committed to the next transaction only, thus (set auto commit). This mean after autocommit=0, you're not in Read Committed anymore .
I would write it this way:
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
START TRANSACTION;
DELETE c FROM child c
INNER JOIN parent p ON
p.id ...
2
I can see how READ_COMMITTED can cause this situation.
READ_COMMITTED allows for three things:
Visibility of committed changes by other transactions using READ_COMMITTED isolation level.
Non-Repeatable Reads : Transaction performing same retrieval with the possibility of getting a different result each time.
Phantoms : Transactions may have rows appear ...
2
I looked at the query and the explain. I am not sure, but have a gut feeling, that the problem is the following. Let's look at the query:
DELETE pw
FROM proc_warnings pw
INNER JOIN day_position dp
ON dp.transaction_id = pw.transaction_id
INNER JOIN ivehicle_days vd
ON vd.id = dp.ivehicle_day_id
WHERE vd.ivehicle_id=? AND dp.dirty_data=1;
The ...
2
AFAIK, snapshot isolation works on DML only. DDL still gets it's Sch-M/S locks, which - in your present case - are caused by drop/recreate FKs.
Another way of doing that is creating an price map (in fault of a english word equivalent for this concept) with validity date (I.e the datetime where it becomes valid).
Things become more complex, I agree, but ...
2
Snapshot isolation is robust. It does work. I have been using it in my system for several years, and it does reduce the amount of deadlocks.
However, snapshot isolation adds more workload to your tempdb, so you need a skilled DBA who can ensure that your system withstands the load. Before you start using snashot isolation in production, make sure you have a ...
2
OK, went back home and tested. Here is the observation.
CREATE DATABASE TEST;
GO
CREATE TABLE TABLE1
(
ID tinyint,
Details varchar(10)
);
GO
INSERT INTO TABLE1
VALUES (1, 'Original');
GO
SELECT
name,
snapshot_isolation_state_desc,
is_read_committed_snapshot_on
FROM sys.databases
WHERE name = 'TEST';
GO
First test with both settings ...
2
Your understanding is correct. It does get a little confusing.
Kim Tripp (one of the programmers of SQL Server and a integral part of SQLSkills) goes through exactly what you stated in the MCM videos on Snapshot Isolation. Fast fwd to 41:45 in the video to get to the part where she answers your question.
If you use ALLOW_SNAPSHOT_ISOLATION make sure you ...
2
OK, two things:
an UPDATE statement will always place an X exclusive lock on the row(s) it updates. This is true for any isolation level - and since those rows are already exclusively locked - what point would there be to add a S shared lock in addition?
under REPEATABLE READ isolation level, any rows that you SELECT within the transaction will have a S ...
2
On the flip side read committed snapshot will give only the latest committed version of data
Incorrect. Snapshot will give you the data that was committed at the moment the snapshot was taken. This moment means either when you issued BEGIN TRAN if you use true SNAPSHOT isolation level, or the moment your statement started if you use ...
1
It does. The first sentence you marked in boldface relate to "concurrent transactions," i.e., transactions that change data but that are not committed while your transaction is still going on.
If these concurrent transactions commit before yours, then you see their data in your transaction in successive select.
1
Be careful with READ_COMMITTED_SNAPSHOT: if you set it on, it can cause lots of subtle bugs.
Also READ_COMMITTED_SNAPSHOT is the default isolation level, which may be overridden by something. Run DBCC USEROPTIONS to determine the actual isolation level your select runs under.
I would explicitly SET TRANSACTION ISOLATION LEVEL SNAPSHOT right before your ...
1
When you are doing read committed snapshot, you are going to work your tempdb out since you are making use of the row versioning functionality (previous row state is kept available while other transactions are in process updating your row(s)). Read uncommitted is a different paradigm, and it shouldn't be thought of as being the same as read committed ...
Only top voted, non community-wiki answers of a minimum length are eligible
