Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Timothy Claason » Is Your Database Application DeadLock and...
Add to briefcase

Is Your Database Application DeadLock and Timeout Resistent? Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
timclaason
Posted Tuesday, March 09, 2010 10:45 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, April 18, 2013 8:56 AM
Points: 22, Visits: 140
Comments posted to this topic are about the item Is Your Database Application DeadLock and Timeout Resistent?
Post #879936
Craig@Work
Posted Wednesday, March 10, 2010 2:38 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, January 29, 2013 4:08 AM
Points: 1,140, Visits: 306
Any cheap and cheerful mechanisms for testing this issue in a non-production environment are most welcome. Brute force will reveal issues that really need sorting.
Post #880022
Abhijit More
Posted Wednesday, March 10, 2010 3:13 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, May 16, 2013 8:06 AM
Points: 900, Visits: 654
nice article can you also give us the solutions to make it resistent

Abhijit - http://abhijitmore.wordpress.com
Post #880042
timothyawiseman
Posted Wednesday, March 10, 2010 12:02 PM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Thursday, February 14, 2013 12:01 PM
Points: 743, Visits: 900
A very interesting and thought provoking article. Thank you!

On actually making your application more resistant to locking, the careful use of the "with (nolock)" query hint can improve performance on a server with a lot of locking tremendously.

Of course, I cannot overemphasize the fact that developers should be careful and thoughtful in using it since it risks dirty reads. But there are times when a dirty read is acceptable, such as when you can known with great confidence that the records you are selecting are not being modified at that moment or when all you really care about is rough approximations of aggregate values. In those cases, it can be tremendously helpful.

---
Timothy A Wiseman
SQL Blog: http://timothyawiseman.wordpress.com/
Post #880476
timclaason
Posted Wednesday, March 10, 2010 12:25 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, April 18, 2013 8:56 AM
Points: 22, Visits: 140
timothyawiseman (3/10/2010)
Of course, I cannot overemphasize the fact that developers should be careful and thoughtful in using it since it risks dirty reads.


Agreed. It's easy for the scope of an article like this to expand exponentially. Each of the ways that I mentioned to deal with resource contention, etc, could have been their own article, and it would be easy to spend hours (or at least lots of minutes) talking about each one.

Every one of the things I mentioned carries some level of risk (taking database interaction off the UI thread, playing with isolation levels, query hints, etc), but then again, we live in a pretty risky world
Post #880491
SQLRNNR
Posted Wednesday, March 10, 2010 12:49 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: 2 days ago @ 1:07 PM
Points: 18,733, Visits: 12,332
Thanks Tim. I will add this to my tool-set.


Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server 2008

SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #880499
AndrewJacksonZA
Posted Wednesday, March 10, 2010 11:36 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, May 07, 2013 5:52 AM
Points: 165, Visits: 1,022
Perhaps you should note in your article that the first script that you posted, the one that shows "TimesAccessed", only shows the data since the last restart of the SQL Server instance.
Post #880732
timclaason
Posted Thursday, March 11, 2010 7:13 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, April 18, 2013 8:56 AM
Points: 22, Visits: 140
GDI Lord (3/10/2010)
Perhaps you should note in your article that the first script that you posted, the one that shows "TimesAccessed", only shows the data since the last restart of the SQL Server instance.


Indeed. Good point GDI Lord.
Post #880971
Ray Herring
Posted Tuesday, March 16, 2010 10:48 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, May 16, 2013 8:17 AM
Points: 334, Visits: 280
Hi Tim,
I am pretty sure that this statement is not accomplishing what you intended.

exec('USE ' + @YourDatabaseName)

When Exec is executed the database context is changed, but only for the statement(s) in the parenthsis. When Exex completes the database context returns to its previous setting.
You can see that behavior with the following test script.
Use AdventureWorks
Declare @myDatabase varChar(255)
Set @myDatabase = 'Northwind'
Exec ('Use ' + @myDatabase + '; Select db_Name(); Select [name] from sys.objects where type = ''U'' ')
Select db_Name()
Select [name] from sys.objects where type = 'U'

You will see that the database context returns to AdventureWorks after the conclusion of the Exec script.

I am still looking for a reasonable methog to dynamically change the database context so if you find one please post it :)
Post #883988
Karl Kieninger
Posted Thursday, March 18, 2010 8:16 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, April 09, 2013 11:45 AM
Points: 26, Visits: 156
WHERE
ius.database_id = DB_ID()
AND DB_NAME(ius.database_id)=@YourDatabaseName

The first condition is sufficient right? ius.database_id = DB_ID() should uniquely identify the correct database without the need for checking the db name as well.

Or am I missing something?

Thanks!
Post #885542
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.