Deadlocks with read
-
Wow, nice to know. Thanx for the info.
-
Hi Remi,
Its nothing. In the past you have also contibuted a lot the this site.
By its "nature" lock escalation increase the possibilty to get deadlocks.
Carl
-
I learned 75% of what I know here... gotta give something back someday.
-
"...In fact the only thing I am doing is sub-queries within the main query
i.e. select x, (select y from table2) from table1..."
I'm thinking that a Select from more than one table can get involved in a deadlock. Using your statement as an example lets say you are able to Select from table1 but unable to Select from table2 because another process has the row locked for update. Your shared lock on table1 would now be held until the end of the transaction (i.e. the Select). If the other process is also trying to modify the row in table1 that you are holding a shared lock on then it looks like there is a deadlock.
I never thought about this with Select statements but I think I now see how this type of deadlock could occur.
Edit: ---
I should make a slight correction to my statement. According to BOL
...Shared (S) locks on a resource are released as soon as the data has been read, unless the transaction isolation level is set to repeatable read or higher, or a locking hint is used to retain the shared (S) locks for the duration of the transaction. ...
So in the hypothetical I stated, does the Shared lock on table1 get released as soon as it is read or since the statement is unable to complete does that shared lock get held and thereby add to the risk of a deadlock??
-
Very very true... excellent reasoning... now I just have to work out why the same row is being locked from both processes - because it very uncommon with this system for 2 users to be accessing the same record. Maybe lock esculation?
-
Are you setting the transaction isolation level to something other than read committed or read uncommited? The default is read committed, which means that the SH is released when it's done reading the page. I'm running into the same problem. However, I don't know where to check to see which transaction isolation level it's using, because I don't have the customer's code.
REPEATABLE READ
Locks are placed on all data that is used in a query, preventing other users from updating the data, but new phantom rows can be inserted into the data set by another user and are included in later reads in the current transaction. Because concurrency is lower than the default isolation level, use this option only when necessary.
SERIALIZABLE
Places a range lock on the data set, preventing other users from updating or inserting rows into the data set until the transaction is complete. This is the most restrictive of the four isolation levels. Because concurrency is lower, use this option only when necessary. This option has the same effect as setting HOLDLOCK on all tables in all SELECT statements in a transaction.
-
I am not sure if this helps, I have had some problems with deadlocks in a database that I am working on and I got all the information I needed to figure out the problem from this article:
http://www.sqlservercentral.com/columnists/skumar/tracingdeadlocks_printversion.asp
Basically I just turned the DBCC traces on as suggested in the article and then I was able to identify from the error log,the DBCC page command and OBJECT() function exactly what was happening.
Hope this helps,
Tony
-
I was getting many dealock error's myself and I finally found and old Trigger that was running. Drove me nuts but that was the problem. DO NOT USE Triggers unless you absolutly have to.
-
I think thats probably overkill - there are many many situations when triggers provide a very good solution - one does need to be careful with trigger design however. Also this won't cause a deadlock on read either.
-
It is possible to get a deadlock just with a SELECT, even at the default READ COMMITTED isolation level. IMHO this is a design error in SQL Server. SELECTs never deadlock in Oracle !
Here is how it can happen. Assume we have an SQL 2000 database with a table and an index:
create table Job (JobID int identity not null,
constraint [Job_PK] primary key clustered (JobID),
Status varchar(9) not null)
create index Status on Job(Status)
Assume we have some rows in the table, and that the row with JobID 1234 has status 'OK'.
Transaction A executes:
SELECT * from Job where Status = 'OK'
Transaction B executes:
UPDATE Job set Status = 'Done' where JobID = 1234
Transaction A requests Shared locks on keys in the index, followed by Shared locks on the corresponding keys in the table.
Transaction B requests an Exclusive lock on the key in the table, followed by an exclusive lock on the index.
Result: deadlock !
This only actually happens if you are unlucky, because the shared locks are transient. But it definitely can occur on a busy system.
I don't believe there is any workaround to this - other than using READ UNCOMMITTED or not using indexes. The very useful guidelines in http://www.sql-server-performance.com/deadlocks.asp don't cover this case.
-
Hello David,
Thank's, important to know...
But where it is stated that update statements will take their locks in that order (table followed by index) and select statements will take their locks in the opposite order?
Also, in your example, why in Transaction B Exclusive lock on the key in the table is given since the select satement has already acquired Shared locks on those keys?
Regards,
Carl
-
This (very long) article explains the new isolation levels in sql 2005. Using SNAPSHOT should eliminate the problem.
http://www.microsoft.com/technet/prodtechnol/sql/2005/SQL05B.mspx
ron
-
I'd suggest you enable trace flags to write the deadlock info to the errolog so you can examine and capture the offending code.
Reads within a transaction will create locks and deadlocks, I have an app that sadly insists in doing all its reads within transactions, excellent when it does a select * from ..... !!!
The reads tend to win over the writes in a contentious situation. improving queries to minimise i/o and speed things up will improve matters ( faster transactions ) I had lots of deadlock problems but improved indexing and getting reports to use no lock hints where data is not absolutely critical ( my transaction levels aren't that high anyway ) will always help. Watch for effective indexing on updates within transactions where more than 1 row is affected.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/ -
In reply to Carl B: I don't think I've seen this behaviour in any documentation. I discovered it the hard way - first from the logging info given by Trace Flag 1204, and then by running SQL Profiler and capturing all the lock activity.
To be more specific about the deadlock sequence, one possible sequence causing a deadlock is:
Transaction B gets an Exclusive lock on the primary key
Transaction A gets a Shared lock on the index key
Transaction A tries to get a Shared lock on the primary key - it can't so it waits.
Transaction B tries to get an exclusive lock on the index key - it can't so it waits.
You can't get a neater deadlock than that.
Of course, the deadlock can only occur if there is a context switch between the first 2 steps in this sequence - which is unlikely but not impossible.
-
Thank's David,
Really apreciated.
I will now make my own research based in this "new" info.
Best regards,
Carl
Viewing 15 posts - 16 through 29 (of 29 total)
You must be logged in to reply to this topic. Login to reply