Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Is Your Database Application DeadLock and Timeout Resistent? Expand / Collapse
Author
Message
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
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: Thursday, April 03, 2014 10:26 AM
Points: 1,140, Visits: 325
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
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: Today @ 1:55 AM
Points: 911, Visits: 682
nice article can you also give us the solutions to make it resistent

Abhijit - http://abhijitmore.wordpress.com
Post #880042
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: Friday, April 04, 2014 4:40 PM
Points: 751, Visits: 917
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
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
Posted Wednesday, March 10, 2010 12:49 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 11:47 PM
Points: 20,458, Visits: 14,082
Thanks Tim. I will add this to my tool-set.



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


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
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: 2 days ago @ 7:24 AM
Points: 166, Visits: 1,048
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
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
Posted Tuesday, March 16, 2010 10:48 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Monday, April 14, 2014 12:55 PM
Points: 441, Visits: 343
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
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 15, 2014 10:31 AM
Points: 30, Visits: 189
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