| Author |
Topic |
|
Gemeen_Aapje
Yak Posting Veteran
54 Posts |
 Posted - 2010-05-19 : 03:17:34
|
| Hi guys,I have a new problem I'm hoping you can help with.On my website I'm getting a few deadlock warnings everyday now. My website has 30,000 members and is usually being used by around 3,000 people at any one time.One of the deadlocks, as an example, is on a table that has all the member information in. Every 5 minutes while a user is logged in, the table updates with their IP and the date/time of the visit. Unfortunately someone may be accessing the person's profile at the time which tries to load the user's data....At this point I see that people are shown the deadlock victim warning.I don't know how to solve this anymore. The tables need to be updates, and they also need to be read by other people - or the site wont work!Any idea what I can do to figure this out? Is there anything I can add to the TSQL that will prevent this (I tried the "With (NO LOCK)" but that didn't help at all.FYI it's SQL 2005, the updates are using SPs, website is running on IIS6, Win2k3, SQL+IIS on the same box, site is programmed in ASP (Classic mainly).The website if you want to see is www.bikermatch.co.ukMany thanks for any advice and support you guys can offer me.CheersMatt |
|
|
rocknpop
Posting Yak Master
201 Posts |
Posted - 2010-05-19 : 08:25:09
|
| Hi Matt..Can you try UPDLOCK?--------------------Rock n Roll with SQL |
 |
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2010-05-19 : 08:44:44
|
| how efficient is your update code?how well indexed are your tables? |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2010-05-19 : 09:35:11
|
There is a good chance that using a row versioning-based isolation level will eliminate deadlocks of the kind you are describing.You should read these links before you implement it to make sure you understand all the implications:http://msdn.microsoft.com/en-us/library/ms179599.aspxhttp://msdn.microsoft.com/en-us/library/tcbchxcb(VS.80).aspxCommand to set database to use read_committed_snapshot.ALTER DATABASE MyDatabase SET READ_COMMITTED_SNAPSHOT ON; CODO ERGO SUM |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-05-19 : 11:14:24
|
| Yeah, I'd go with READ_COMMITTED_SNAPSHOT too. Apparently it was a key reason for the larger MSSQL-shops to upgrade from SQL 2000 to SQL 2005. |
|
|
|
Gemeen_Aapje
Yak Posting Veteran
54 Posts |
Posted - 2010-05-19 : 11:23:44
|
| thanks guys for the mega-prompt responses, I'm gonna get through them docs and get back to you |
|
|
|
Gemeen_Aapje
Yak Posting Veteran
54 Posts |
Posted - 2010-05-19 : 11:29:13
|
| FYI, the SP looks like this...=======================ALTER PROCEDURE [SPNAME](@User_ID INT, @IP nvarchar (16))AS UPDATE UsersTable WITH (ROWLOCK)SET last_accessed = GETDATE(), IP = @IPWHERE (user_ID = @User_ID)=======================This was just one of the recent locks. The deadlock victim was a "Select * from Users where username = 'Name'".There's an index on the username and last_accessed field which were being locked.I've no idea if my indexes are optimised correctly. I try my best but I'm totally self-taught so I'm no DBA by any standards.The same thing happens with forum posts when others are loading the forum topic (posting updates the topic table with some additional info, no views, no posts, etc). |
|
|
|
Gemeen_Aapje
Yak Posting Veteran
54 Posts |
Posted - 2010-05-19 : 11:36:05
|
| Can I confirm then, if I enable "READ_COMMITTED_SNAPSHOT" then I'll need to put "WITH (READCOMMITED)" into all my scripts?Is the change reversable if it goes belly-up?One last thing... why doesn't SQL just queue the request??? The update takes a fraction of a millisecond, as does the read... so why the hell are they conflicting? |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2010-05-19 : 12:21:56
|
quote:
Originally posted by Gemeen_Aapje
Can I confirm then, if I enable "READ_COMMITTED_SNAPSHOT" then I'll need to put "WITH (READCOMMITED)" into all my scripts?Is the change reversable if it goes belly-up?One last thing... why doesn't SQL just queue the request??? The update takes a fraction of a millisecond, as does the read... so why the hell are they conflicting?
There are no code changes required to implement READ_COMMITTED_SNAPSHOT, unless you are already specifying some isolation level other than READ_COMMITTTED.The change is reversable, but I have never heard of a problem with it.Why do you have a ROWLOCK hint? It's unnecessary and could be causing problems.As for why is it is happening, you didn't post any deadlock details so there is no way for us begin to say.CODO ERGO SUM |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-05-19 : 12:25:45
|
No, you only need to set READ_COMMITTED_SNAPSHOT, nothing else.If it works OK then you may want to take some locking hints out (particularly NOLOCK if you have that anywhere)My understanding why they can't be queued is that they are different types of locks, and have different Exclusivity / non-exclusivity.You might be having the problem, in part, because you are updating last_accessed which is in an index - so when the record is updated the index has to be changed to match. If you query by "last_accessed BETWEEN @Start and @End" then the index will be useful, so best to keep it.The index on Username will work well on "where username = 'Name'"If presume you also have an index on user_ID? That will optimise "WHERE (user_ID = @User_ID)" in the UPDATE above.So, within the context of what you've mentioned, sounds like you have things about right.Do you have maintenance jobs to Rebuild idnexes and Update statsistics? If not then that may be hurting you - after lots of inserts / changes the indexes will not be in optimum "shape", and the statistics (about "How many records are predicted for "last_accessed BETWEEN @Start and @End") will become stale, and the performance will fall off and query plans may not be optimal - that may mean that unnecessary pages are being read and that in turn may impact locking somewhat.Anyways, try READ_COMMITTED_SNAPSHOT; and yes, its reversableI don't think you can be IN the database when you set it, and I think SQL may need exclusive access, so you may need to do:USE masterGOALTER DATABASE MyDatabase SET SINGLE_USER WITH ROLLBACK IMMEDIATEGOALTER DATABASE MyDatabase SET READ_COMMITTED_SNAPSHOT ONGOALTER DATABASE MyDatabase SET MULTI_USER, READ_WRITE WITH ROLLBACK IMMEDIATEGOUSE MyDatabaseGO |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2010-05-19 : 12:35:35
|
| That's correct that SQL Server needs exclusive access when changing the isolation level. So this change does require downtime, although it's only about 30 seconds. If you are using database mirroring, then it has to be dropped in order to make the isolation level change. I'm not sure why that is the case, but it will error if mirroring is enabled. We now change the model database so that its isolation level is READ_COMMITTED_SNAPSHOT so that if we forget to make the change, it'll get this setting by default for new databases. Switching the isolation level a couple of years ago for our most critical databases significantly improved performance. It also mostly eliminated deadlocks (deadlocks will still happen between a write and another write). The performance improvement was dramatic. On one of my older systems, we have transactional replication setup where we replicate one database to another database on the same instance. This was done back in SQL Server 2000 for the application's reporting needs, so that shared locks from reports wouldn't hinder performance of the OLTP database. When we upgraded it to 2005, we kept this same architecture. I've been considering going back to the project team to tell them we need to eliminate transactional replication and instead just use the OLTP database with the READ_COMMITTED_SNAPSHOT. It only makes sense in 2005+ to use transactional replication for reports when the databases are on separate instances (and preferably separate hardware). I've been too busy to discuss it with the project team so far though, but it should work.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
Gemeen_Aapje
Yak Posting Veteran
54 Posts |
Posted - 2010-05-19 : 13:38:40
|
| Michael, I think I added the rowlock when i started getting deadlocks ages ago (when my site got a lot, lot busier). It didn't work. Then I tried "NOLOCK" on the reads, that didn't work either. Nothing seems to work. What specifics do you need from my deadlock information and how best can I post it here? Thanks for the help.Kristen, thanks also. Yes the "Last Accessed" is used to select those who've been online recently for example (to help construct the "Online Users" page). The site is FULL of "where username=xxx" queries too, so that must stay I'm afraid. The user_Id is the key and is in a clustered index if I remember rightly. I've just realised that the SQL optimisation thing with 2005 has created loads of other indexes now too, there's loads everywhere and I've no idea if they're causing trouble.Stats are updated automatically and indexes are rebuilt every few hours I think (or defragmented hourly and rebuilt at night). There's not a lot of NEW data being added really, like a few hundred forum posts each day for example, but there's a lot of updating on existing rows (updating a user's IP, last_accessed time, forum topic post count, last post date, private messages read/deleted, etc).I'm gonna try the snapshot thing on my test server, but it's not got any load so it's hard to simulate what it'll be like when live. Though if it's reversible then I'm not worried about.By the way, what IS a snapshot? Is it something I need to make every so often or is it all automatic? I'm assuming it'll just pull a "snapshot" of the individual record before it was locked (which is totally fine). |
|
|
|
Gemeen_Aapje
Yak Posting Veteran
54 Posts |
Posted - 2010-05-19 : 13:49:41
|
| I jumped in with both feet and applied it to the live database... and it's working great (with about 30 seconds downtime as you said which isn't a problem).Shall I go through all my code removing the "NOLOCK" and "ROWLOCK" statements? Or shall I change it to READCOMMITED and UPDLOCK?I'll wait for your confirmation before doing it this time, maybe I made the problem worse last time I investigated this deadlock issue myself. Doh.Thanks so much once again, you guys here are always spot-on. |
|
|
|
Gemeen_Aapje
Yak Posting Veteran
54 Posts |
Posted - 2010-05-19 : 13:56:55
|
| oh crap, now I can't run a full-text search on my forums. It says..."Microsoft OLE DB Provider for SQL Server error '80040e14' The execution of a full-text query failed. "The content index is corrupt." " |
|
|
|
Gemeen_Aapje
Yak Posting Veteran
54 Posts |
Posted - 2010-05-19 : 14:01:24
|
| scratch that, I've deleted and rebuilt it - works fine now. |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2010-05-19 : 14:21:16
|
quote:
Originally posted by Gemeen_Aapje
I jumped in with both feet and applied it to the live database... and it's working great (with about 30 seconds downtime as you said which isn't a problem).Shall I go through all my code removing the "NOLOCK" and "ROWLOCK" statements? Or shall I change it to READCOMMITED and UPDLOCK?I'll wait for your confirmation before doing it this time, maybe I made the problem worse last time I investigated this deadlock issue myself. Doh.Thanks so much once again, you guys here are always spot-on.
As a general rule, you should not use any index hints, so just get rid of the NOLOCK and ROWLOCK hints.CODO ERGO SUM |
|
|
|
Gemeen_Aapje
Yak Posting Veteran
54 Posts |
Posted - 2010-05-19 : 14:54:36
|
| Big problem - Deadlocks have increased, but I think only while my indexes were being dropped/recreated.Some are totally random! Like an index on TableA was being dropped/recreated while someone was posting into TableB (simple insert). I've just had 4 like this, and it's randomly choosing one or the other as the deadlock victim... even though they're not even the same table!Example to follow... |
|
|
|
Gemeen_Aapje
Yak Posting Veteran
54 Posts |
Posted - 2010-05-19 : 15:06:05
|
| So I use this SP to reindex my DB... http://weblogs.sqlteam.com/tarad/archive/2007/11/27/60415.aspxIt's just been run on a scheudule with these parameters... EXEC isp_ALTER_INDEX @dbName = '----', @statsMode = 'SAMPLED', @defragType = 'REORGANIZE', @minFragPercent = 5, @maxFragPercent = 100, @minRowCount = 100An example deadlock was (excluding some info for security reasons)...Success: INSERT INTO [PRIVATE_MESSAGE_TABLE]([from_id],[to_id],[IP],[subject],[message]) values(xxxxx)Deadlock victim: drop index [dbo].[Forum_TOPICS].[IndexName]Why on earth would 2 totally different tables clash?In another example, the same index drop caused someone to not be able to open a forum topic (and she specifically reported it to me, so I know it was for real).I'm totally lost now. Am I re-indexing wrong? Is that script (Which I thought was popular) no good? |
|
|
|
Gemeen_Aapje
Yak Posting Veteran
54 Posts |
Posted - 2010-05-19 : 15:07:59
|
| It also stopped someone updating their USER record in the users table! WTF? |
|
|
|
Gemeen_Aapje
Yak Posting Veteran
54 Posts |
Posted - 2010-05-19 : 16:55:42
|
| these deadlocks are flooding in now, I've no idea why. Here's another where the "last_accessed" index was locked...SP 1 (Update the IP and date/time)... UPDATE dbo.tblUsers SET last_accessed = GETDATE(), IP = @IP WHERE (user_ID = @User_ID)SP 2 (Count how many admins are logged in)... SELECT Count(ChatUsers.User_ID) AS ChatRoomAdminCount From tblUsers RIGHT OUTER JOIN tblChatUsers ON tblUsers.user_ID = tblChatUsers.user_id WHERE tblUsers.Chat_Admin = 1 OR tblUsers.mod = 1 OR tblUsers.Admin = 1;The index locked was the Last_accessed date one (again). Here's what it looks like.... CREATE NONCLUSTERED INDEX [_dta_index_INDEXNAME] ON [dbo].[USER_TABLE] ( [user_ID] ASC, [last_accessed] ASC ) INCLUDE ( [username], [DoB], [Sex], [enabled], [admin], [mod]) WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]Any ideas why this is happening now? I've removed ALL of the NOLOCK and ROWLOCKs from my scripts and enabled the snapshot thing. I'm pretty worried now, it should be fine (no?)... |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
Next Page
|
SQL Deadlocks