Avoiding Deadlocks in ASP/VBScript

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Avoiding Deadlocks in ASP/VBScript

Author

Topic

Gemeen_Aapje
Yak Posting Veteran

54 Posts

Posted - 2009-02-03 : 06:59:16

Hi everyone,

I'm really in need of some assistance with a deadlock issue on my medium sized website.

I've found one example cause of the many deadlock issues I'm getting and the 2 queries are as follows...

Query 1) Updating a user's "Last Accessed" time and "Last IP Used" fields in the database. (Stored procedure)
Query 2) Trying to select the user's table and another table in a join. (Direct T-SQL query)

My questions are...

1) Why does SQL not figure out that query 1 is making an update and to wait a few seconds until running query 2?
2) How can I avoid these in ASP/VBScript? Is there a Try>Catch statement I can write? Is there some additional parameters I can add to the T-SQL or SP that will make the SQL server wait if the table is locked?

1 server is running both MSSQL 2005 and IIS 6. Windows 2003.

Many thanks in advance for any help you can give.
~Matt

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2009-02-03 : 07:49:26

Make sure that the columns in your WHERE statements are indexed...preferably in a clustered index. I'm guessing they aren't...

- Lumbago

Gemeen_Aapje
Yak Posting Veteran

54 Posts

Posted - 2009-02-03 : 16:25:27

Hi Lumbago,

I've narrowed it down to a simple update SP that's in every deadlock situation.

Please can you tell me how to change the following SP into a Try/Catch to avoid deadlocking?

ALTER PROCEDURE [dbo].[SP_UpdateUserIPandTime](@User_ID INT,@IP nvarchar (16))
AS UPDATE dbo.TABLENAME
SET COLUMNNAME1 = GETDATE(), COLUMNNAME2 = @IP
WHERE (COLUMNNAME3 = @User_ID)

I don't mind loosing the data from this transaction to be honest, but it never seems to be chosen as the deadlock victim (It's always the other query, which is more important).

Can I lower the priority? Rerun the SP if it fails?

Thanks so much in advance

~Matt

tonymorell10
Yak Posting Veteran

90 Posts

Posted - 2009-02-03 : 18:56:18

Sounds like this is more of a blocking issue than a deadlock. If you're other query is a SELECT and you can live with dirty reads, use the NOLOCK hint on the SELECT statement.

Otherwise, please post more details (DDL for tables, triggers, etc. and both queries).

Gemeen_Aapje
Yak Posting Veteran

54 Posts

Posted - 2009-02-04 : 02:14:13

ok theres a table called tblUsers. It has columns for the last used IP address of the user and the last accessed time on the site. The SP that always wins in the deadlock is...

ALTER PROCEDURE [dbo].[SP_UpdateUserIPandTime](@User_ID INT,@IP nvarchar (16))
AS UPDATE dbo.tblUsers
SET last_accessed = GETDATE(), IP = @IP
WHERE (user_ID = @User_ID)

The select query is normally when someone on the site is searching users (main part of the site, as it's a dating site). It willl look something like this...

SELECT * FROM tblUsers INNER JOIN tblProfiles ON tblUsers.user_ID = tblProfiles.user_ID INNER JOIN (SELECT pc FROM tblPostcodes WHERE pc IN('BB1','BB10','BB11','BB12',***LIST OF POSTCODES HERE***)) PCTABLE ON dbo.tblProfiles.postcode = PCTABLE.pc WHERE (tblProfiles.ENABLED=1 AND tblUsers.DoB between '1968-02-04' and '1991-02-04') ORDER BY tblUsers.last_accessed DESC;

The first is a SP and the select statement is just a T-SQL command run from ASP using an ADO connection (to local MSSQL database).

Thanks again.

Gemeen_Aapje
Yak Posting Veteran

54 Posts

Posted - 2009-02-04 : 02:16:59

what is a dirty read and what effect would the NOLOCK have?

I'd rather loose the latest IP and date/time from the user's update SP because it runs for every user every 5 minutes anyhow. Can I set a NOLOCK for an update??? The SELECT query is the most important as it means someone is searching the database from the website, and I don't want them getting deadlock errors as they currently are.

Sorry I don't know what the DDL for tables are or how to obtain them/trigger. I've had no formal SQL training, only what I've picked up myself over the years.

Gemeen_Aapje
Yak Posting Veteran

54 Posts

Posted - 2009-02-04 : 03:34:44

ok I've added ROWLOCK to the SP and NOLOCK to the Select now, gonna see how it works out over the next 24 hours. I'll be sure to report back

Thanks

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2009-02-04 : 03:41:47

Is the user_ID a primary key in the tblUsers?

- Lumbago

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2009-02-04 : 04:21:34

There is no need for eiter ROWLOCK or NOLOCK in this situation, proper indexing will make sure that everything runs smoothly. Using NOLOCK can be useful but never ever use it within a transaction! It can cause your data to be corrupted so you need to be careful... But if you need to read data that are manipulated by large transactions and it's not vital that the data is 100% accurate then NOLOCK can be useful.

Can you please run the following code in your database and post the results

SET SHOWPLAN_TEXT ON

SELECT * 
FROM tblUsers 
  INNER JOIN tblProfiles 
    ON tblUsers.user_ID = tblProfiles.user_ID 
  INNER JOIN (
      SELECT pc FROM tblPostcodes 
      WHERE pc IN('BB1','BB10','BB11','BB12',***LIST OF POSTCODES HERE***)
      ) PCTABLE 
    ON dbo.tblProfiles.postcode = PCTABLE.pc 
WHERE tblProfiles.ENABLED=1 
  AND tblUsers.DoB between '1968-02-04' and '1991-02-04'
ORDER BY tblUsers.last_accessed DESC;

- Lumbago

Gemeen_Aapje
Yak Posting Veteran

54 Posts

Posted - 2009-02-04 : 08:20:44

hi mate. user_id is indeed the PK. There is indexing available for the last_accessed column also.
Can you tell me why NOLOCK might corrupt data? I'm worried about this because I've just enabled it all over the place for SELECT statements :-S

The result from that query is as follows...
|--Nested Loops(Inner Join)
|--Clustered Index Seek(OBJECT:([DATABASENAME].[dbo].[tblPostcodes].[IX_tblPostcodes_PK]), SEEK:([DATABASENAME].[dbo].[tblPostcodes].[pc]=N'BB1') ORDERED FORWARD)
|--Sort(ORDER BY:([DATABASENAME].[dbo].[tblUsers].[last_accessed] DESC))
|--Nested Loops(Inner Join, OUTER REFERENCES:([Uniq1002], [DATABASENAME].[dbo].[tblUsers].[last_accessed]) OPTIMIZED)
|--Nested Loops(Inner Join, OUTER REFERENCES:([DATABASENAME].[dbo].[tblProfiles].[user_ID]) OPTIMIZED)
| |--Clustered Index Seek(OBJECT:([DATABASENAME].[dbo].[tblProfiles].[IX_postcode]), SEEK:([DATABASENAME].[dbo].[tblProfiles].[postcode]=N'BB1'), WHERE:([DATABASENAME].[dbo].[tblProfiles].[ENABLED]=(1)) ORDERED FORWARD)
| |--Index Seek(OBJECT:([DATABASENAME].[dbo].[tblUsers].[_dta_index_tblUsers_6_69575286__K1_K10_2_6_7_11_12_13_15_20]), SEEK:([DATABASENAME].[dbo].[tblUsers].[user_ID]=[DATABASENAME].[dbo].[tblProfiles].[user_ID]), WHERE:([DATABASENAME].[dbo].[tblUsers].[DoB]>='1968-02-04 00
|--Clustered Index Seek(OBJECT:([DATABASENAME].[dbo].[tblUsers].[IX_tblUsers_last_accessed]), SEEK:([DATABASENAME].[dbo].[tblUsers].[last_accessed]=[DATABASENAME].[dbo].[tblUsers].[last_accessed] AND [Uniq1002]=[Uniq1002]) LOOKUP ORDERED FORWARD)

I really appreciate your help, thanks

tonymorell10
Yak Posting Veteran

90 Posts

Posted - 2009-02-04 : 11:29:31

NOLOCK or READ UNCOMMITTED should only be used in SELECT statements. It's ignored for DML anyway (insert/update/delete). I have never seen a case where using NOLOCK in and of itself has corrupted data. The 'dirty-read' means that the select statement MAY read uncommitted data. This means the results from your SELECT statement may or may not have been committed to the database at the time the SELECT was run. If you are using the results of a SELECT to update existing data, do not use NOLOCK as you may introduce data inconsistencies. If your intent is to purely query from the database, using NOLOCK is fine.

You're query plan look fine - index seeks are desirable.

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2009-02-04 : 12:04:13

Are you sure that the query/procedure you posted are the ones that are causing blocking?? As Tony says the query plan looks just great.

And corrupted might not have been the right word about using NOLOCK, inconsistent might be better. Take this (bizarre) example and imaging that both procedures are running at about the same time:

Procedure 1:

BEGIN TRANSACTION
UPDATE AlegdedTerrorists SET Terrorist = 'False' WHERE Name = 'Gemeen_Aapje'
...do a lot of stuff that takes time
COMMIT

Then at the same time another procedure is running:

Procedure 2:

SELECT Name AS ToBeKilled FROM AlegdedTerrorists WITH (NOLOCK) WHERE Terrorist = 'Confirmed'

A silly example of course but using NOLOCK at the wrong time here would kill innocent people.

- Lumbago

Gemeen_Aapje
Yak Posting Veteran

54 Posts

Posted - 2009-02-04 : 13:55:16

It's working fine so far, and the site is running faster. Not a single deadlock all day! :-) The data in the Select statement's isn't that important, just profile searches etc.

What I posted was the blocked query for 100% of the deadlocks yesterday. It was the select statement chosen as the deadlock victim for every deadlock graph. The SP that updated the IP and date/time always won the battle of the locks.

So am I gonna be on someone's hitlist now? haha - i love the example though, brilliant! :-) Seriously though, I'm not too fussed about the select statement. Whether it gets the user's 'last accessed' time perfect or not doesn't matter at all, as long as they dont get a horrible deadlock victim error (It's been worrying some of my users haha).

Thanks so much, I will continue to report back as promised

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2009-02-04 : 14:09:33

quote:
Whether it gets the user's 'last accessed' time perfect or not doesn't matter at all

Agreed! And I don't see why it's not used more...as long as you are aware of the dangers I don't see a problem with it.

- Lumbago

Gemeen_Aapje
Yak Posting Veteran

54 Posts

Posted - 2009-02-04 : 14:20:39

Well I'm loving this, going through my entire site's scripts now and fixing them.

One question: What's the best to use for delete statements so that they don't lock the table for too long? I've been putting ROWLOCk on the update statements (is that even right?) but what about deletions?

The DB is very active to be honest thousands of transactions flying all over the place. My site is basically a facebook for bikers, with 18,000 members and an average of 3,000 visitors online at any one time. As soon as google starts indexing the site the DB goes crazy.

Thanks

Gemeen_Aapje
Yak Posting Veteran

54 Posts

Posted - 2009-02-04 : 14:23:15

... and insertions sorry. How can i mimimise their impact on locking?

tonymorell10
Yak Posting Veteran

90 Posts

Posted - 2009-02-04 : 16:03:23

Uing ROWLOCK should help reduce blocking with deletes as well. Just make sure you WHERE clause is as specific as possible and you don't try to delete too much data in one transaction.

Gemeen_Aapje
Yak Posting Veteran

54 Posts

Posted - 2009-02-05 : 02:45:41

ok thanks. An example would be when a user tries to delete all their private messages. It's along the lines of... DELETE FROM tblPMs where user_id = 12345
The number of PMs to delete may be 2,000+ out of a total of 300,000 records.

As an update... I've not received a single deadlock message in over 24 hours! Normally it's 50 per day.

I owe you guys some beer!!!

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2009-02-05 : 04:34:13

I just want to say that NOLOCK is not the solution to your every problem, you should spend more time optimizing your queries, indexes and data structures instead to get the maximum performance out of your database.

What kind of maintenance scripts are you running? For a heavily used database with frequent insert/update/deletes indexes get very fragmented and you should run index defrag/rebuild and also update statistics on a regular basis. Taras script for optimizing indexes is excellent: http://weblogs.sqlteam.com/tarad/archive/2008/09/03/Defragmenting-Indexes-in-SQL-Server-2005.aspx I run a reorganize on all my tables every night and then a full rebuild once a week.

- Lumbago

tonymorell10
Yak Posting Veteran

90 Posts

Posted - 2009-02-05 : 06:10:11

Lumbago is correct. However, I have found that locking hints in high volume SQL Server databases are sometimes necessary.

Take a look at the dm views in SQL 2005. They are very useful for finding all kinds of performance statistics (worst performing queries, missing indexes, index fragmentation, etc.). Also, make sure your index fill factors are set properly. This will help keep index fragmentation to a minimum and reduce the frequency you will have to perform index rebuilds and/or defrags.

Gemeen_Aapje
Yak Posting Veteran

54 Posts

Posted - 2009-02-05 : 08:15:35

i keep a low fill factor, about 60% i think. Each night the indexes are reindexed.

Gonna take a look at that index defrager now, thanks

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources