Is Your Database Application DeadLock and Timeout Resistent?

Question

Is Your Database Application DeadLock and Timeout Resistent?

timclaason

SSC-Addicted

Points: 486
More actions
March 9, 2010 at 10:45 pm

#217514

Comments posted to this topic are about the item Is Your Database Application DeadLock and Timeout Resistent?

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Craig@Work SSCrazy Points: 2346 More actions · Answer 1

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.

Abhijit More SSCertifiable Points: 6897 More actions · Answer 2

nice article can you also give us the solutions to make it resistent

Abhijit - http://abhijitmore.wordpress.com

TimothyAWiseman SSCrazy Eights Points: 8819 More actions · Answer 3

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/

timclaason SSC-Addicted Points: 486 More actions · Answer 4

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 🙂

SQLRNNR SSC Guru Points: 281334 More actions · Answer 5

Thanks Tim. I will add this to my tool-set.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

AndrewJacksonZA SSCarpal Tunnel Points: 4143 More actions · Answer 6

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.

timclaason SSC-Addicted Points: 486 More actions · Answer 7

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.

Ray Herring SSCertifiable Points: 5533 More actions · Answer 8

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 🙂

Karl Kieninger SSC Eights! Points: 853 More actions · Answer 9

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!