October 28, 2010 at 8:00 am
I'm investigating the blocking problems we have and I discoverd there is some (major) blocking which is caused by foreign keys. The statement which is causing the blocking is updating/inserting many rows (merge statement) and cannot be devided into several smaller statements (I tried, this made the whole thing much slower).
I worked out a testcase:
create table test1 (a int not null, b int)
create table test2 (a int not null, b int)
ALTER TABLE test1
ADD CONSTRAINT pk_test1 PRIMARY KEY (a)
ALTER TABLE test2
ADD CONSTRAINT pk_test2 PRIMARY KEY (a)
ALTER TABLE test2 ADD CONSTRAINT fk
FOREIGN KEY(a)
REFERENCES test1(a)
INSERT INTO test1 VALUES(1,1)
INSERT INTO test1 VALUES(2,1)
INSERT INTO test1 VALUES(3,1)
INSERT INTO test1 VALUES(4,1)
Now open a new screen in SQL Server management studio with the following code:
begin transaction
update test1 set b = 2
waitfor delay '00:00:20'
rollback
Open another screen and post the following code:
insert into test2 values (1,2)
As you can see the last query will wait for the update test1 transaction to complete. The reason is because the foreign key needs to be checked but there is an exclusive lock on it.
I have been looking for solutions for this but I can't find the best one, I tried to set the isolation level to read uncommitted but this didn't work (I really thought it would). So for now I know 2 possible solutions for this problem:
1. Disabling the foreign key before the queries are run, after running the foreign keys must be enabled again:
alter table test2 nocheck constraint fk
... do all the magic...
alter table test2 with check check constraint fk
But this solution just doesn't feel right, the foreign keys are there for a reason and the tables are quite large. So enabling the foreign keys afterwords may take some time.
2. Make the primary key nonclustered, in this way the table is a heap and when b is updated there won't be a lock on the index. The problem with this solution is that there are a lot of index searches on the primary key, so turning the index into a nonclustered index would generate a lot of bookmark lookups and thereby decrease the overall performance.
Does anyone have some other solutions to this problem?
October 28, 2010 at 8:11 am
Have you taken a look at Snapshot Isolation for this?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
October 28, 2010 at 8:11 am
Have you verified you are using the normal read committed isolation level for both transactions?
Have you tried setting your database to use row-version isolation?
ALTER DATABASE MyDatabase SET READ_COMMITTED_SNAPSHOT ON;
ALTER DATABASE MyDatabase SET ALLOW_SNAPSHOT_ISOLATION ON;
Using Row Versioning-based Isolation Levels
October 28, 2010 at 8:39 am
I'm trying to set the snapshot isolation level, but unfortunately this takes some time 🙁
So in the meanwhile I googled it and found that someone else says it won't help:
I’m surprised setting the database options READ_COMMITTED_SNAPSHOT nor ALLOW_SNAPSHOT_ISOLATION do not help. I must admit, I did expect one of these options to ‘fix’ the behaviour and give the last good committed value – but no - the INSERT still blocks.
October 28, 2010 at 8:42 am
In the real tables, what's the execution plan look like on the updates that are blocking the inserts into the other table?
I'd expect updates of most sorts to take very short, very skinny locks.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
October 28, 2010 at 8:46 am
Michael Valentine Jones (10/28/2010)
Have you verified you are using the normal read committed isolation level for both transactions?Have you tried setting your database to use row-version isolation?
ALTER DATABASE MyDatabase SET READ_COMMITTED_SNAPSHOT ON;ALTER DATABASE MyDatabase SET ALLOW_SNAPSHOT_ISOLATION ON;
Using Row Versioning-based Isolation Levels
What are the possible side effects off those settings? I have an erp app and there are often timeout due to manual table locks being takedby the ERP for inserts and updates. I'd love to implement this but I have no time to test it out as thorougly as I want to!
October 28, 2010 at 9:44 am
Looks like the culprit isn't the foreign key as much as the fact that your update gets an exclusive lock on table1 and holds it for the duration of the transaction (20 seconds). Thats logical in your example since your update updates the entire table.
Lets assume that your example is a pretty good example of what your production locking scenario is doing. You have a pretty large transaction that is updating enough rows on table1 that sql server decides its best to lock the whole table. That looks way more like the red flag to me than the foreign key. I'd either look at speeding up the update(s) that are causing the table lock to mitigate the time frame that the exclusive lock is held, or reduce the size of the update so the update doesn't need to get above a page lock to run its updates.
Edit: saw that you believe you can't break up the updates. Have you tried feeding your MERGE statement a hint to avoid the table lock for testing purposes?
October 28, 2010 at 9:50 am
The classic way around this is to add a nonclustered index for the FK check to use.
Something like the following should work:
ALTER TABLE test1
ADD CONSTRAINT uq_test1 UNIQUE NONCLUSTERED (a)
October 28, 2010 at 9:56 am
Ninja's_RGR'us (10/28/2010)
Michael Valentine Jones (10/28/2010)
Have you verified you are using the normal read committed isolation level for both transactions?Have you tried setting your database to use row-version isolation?
ALTER DATABASE MyDatabase SET READ_COMMITTED_SNAPSHOT ON;ALTER DATABASE MyDatabase SET ALLOW_SNAPSHOT_ISOLATION ON;
Using Row Versioning-based Isolation Levels
What are the possible side effects off those settings? I have an erp app and there are often timeout due to manual table locks being takedby the ERP for inserts and updates. I'd love to implement this but I have no time to test it out as thorougly as I want to!
The link I posted before contains links to explains the pros and cons much better than I could.
It should eliminate most or all deadlocks, and will let select queries run without being blocked by inserts or updates.
I have used this on a number of systems and never noticed any harmful side-effects.
We had a problem on one system where we were getting thousands of deadlocks per day, and row-version isolation eliminated them completely. The problem was really caused by a bad database design, but when you are dealing with a vendor designed application, that's par for the course.
October 28, 2010 at 10:00 am
While snapshot isolation can be very useful, you should google 'write skew anomolies', check your code and do a lot of testing.
October 28, 2010 at 10:08 am
Ken McKelvey (10/28/2010)
While snapshot isolation can be very useful, you should google 'write skew anomolies', check your code and do a lot of testing.
That's my main concern. I'm supporting a vendor ERP (ms dynamics Nav). It's extremely well designed however it sometimes needs to update a a few ledgers simultaneously and it locks them all solid.
Now my issue is that I have 0 tolerance for dirty reads (client's request). They preffer a few timeouts rather than possible bad data.
Now I want both good data and 0 timeouts.
My real issue is that I don't see any easy solution to test this out. Even if I replay a trace I don't know how to figure out if it had any adverse effect. From the top of my head, the best option I see is to take a full back, run a trace for 1 full day. Stop the trace at 1 am and take another backup immediately.
Then restore on test and replay the trace. And then compare both after pictures table per table to see if there's anything different. Now that sounds extremely ressources consuming altho it may work.
October 28, 2010 at 10:17 am
Ken McKelvey (10/28/2010)
While snapshot isolation can be very useful, you should google 'write skew anomolies', check your code and do a lot of testing.
SQL Server handles this the following way:
Understanding Row Versioning-Based Isolation Levels
http://msdn.microsoft.com/en-us/library/ms189050.aspx
"...
Transactions running under snapshot isolation take an optimistic approach to data modification by acquiring locks on data before performing the modification only to enforce constraints. Otherwise, locks are not acquired on data until the data is to be modified. When a data row meets the update criteria, the snapshot transaction verifies that the data row has not been modified by a concurrent transaction that committed after the snapshot transaction began. If the data row has been modified outside of the snapshot transaction, an update conflict occurs and the snapshot transaction is terminated. The update conflict is handled by the Database Engine and there is no way to disable the update conflict detection.
..."
October 28, 2010 at 10:20 am
Michael Valentine Jones (10/28/2010)
Ken McKelvey (10/28/2010)
While snapshot isolation can be very useful, you should google 'write skew anomolies', check your code and do a lot of testing.SQL Server handles this the following way:
Understanding Row Versioning-Based Isolation Levels
http://msdn.microsoft.com/en-us/library/ms189050.aspx
"...
Transactions running under snapshot isolation take an optimistic approach to data modification by acquiring locks on data before performing the modification only to enforce constraints. Otherwise, locks are not acquired on data until the data is to be modified. When a data row meets the update criteria, the snapshot transaction verifies that the data row has not been modified by a concurrent transaction that committed after the snapshot transaction began. If the data row has been modified outside of the snapshot transaction, an update conflict occurs and the snapshot transaction is terminated. The update conflict is handled by the Database Engine and there is no way to disable the update conflict detection.
..."
So let's see if I got this straight. If I do read-only work in the conflicting tables. So I don't have to worry about 'write skew anomalies'?
I only have access to insert/update data into 3 tables dedicated to my application. Everything else is read-only and or dealt straight in MS Dynamics Nav.
October 28, 2010 at 10:25 am
Ninja's_RGR'us (10/28/2010)
Michael Valentine Jones (10/28/2010)
Ken McKelvey (10/28/2010)
While snapshot isolation can be very useful, you should google 'write skew anomolies', check your code and do a lot of testing.SQL Server handles this the following way:
Understanding Row Versioning-Based Isolation Levels
http://msdn.microsoft.com/en-us/library/ms189050.aspx
"...
Transactions running under snapshot isolation take an optimistic approach to data modification by acquiring locks on data before performing the modification only to enforce constraints. Otherwise, locks are not acquired on data until the data is to be modified. When a data row meets the update criteria, the snapshot transaction verifies that the data row has not been modified by a concurrent transaction that committed after the snapshot transaction began. If the data row has been modified outside of the snapshot transaction, an update conflict occurs and the snapshot transaction is terminated. The update conflict is handled by the Database Engine and there is no way to disable the update conflict detection.
..."
So let's see if I got this straight. If I do read-only work in the conflicting tables. So I don't have to worry about 'write skew anomalies'?
I only have access to insert/update data into 3 tables dedicated to my application. Everything else is read-only and or dealt straight in MS Dynamics Nav.
'write skew anomalies' are only associated with update operations where two different transactions attempt to update the same row at the same time.
October 28, 2010 at 10:31 am
The following gives an example of the sort of code you need to be careful with:
http://en.wikipedia.org/wiki/Snapshot_isolation
As a concrete example, imagine V1 and V2 are two balances held by a single person, Phil. The bank will allow either V1 or V2 to run a deficit, provided the total held in both is never negative (i.e. V1 + V2 = 0). Both balances are currently $100. Phil initiates two transactions concurrently, T1 withdrawing $200 from V1, and T2 withdrawing $200 from V2.
If the database guaranteed serializable transactions, the simplest way of coding T1 is to deduct $200 from V1, and then verify that V1 + V2 = 0 still holds, aborting if not. T2 similarly deducts $200 from V2 and then verifies V1 + V2 = 0. Since the transactions must serialize, either T1 happens first, leaving V1 = -$100, V2 = $100, and preventing T2 from succeeding (since V1 + (V2 - $200) is now -$200), or T2 happens first and similarly prevents T1 from committing.
Under snapshot isolation, however, T1 and T2 operate on private snapshots of the database: each deducts $200 from an account, and then verifies that the new total is zero, using the other account value that held when the snapshot was taken. Since neither update conflicts, both commit successfully, leaving V1 = V2 = -$100, and V1 + V2 = -$200.
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply