Is Your Database Application DeadLock and Timeout Resistent?

  • timclaason

    SSC-Addicted

    Points: 486

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

  • Craig@Work

    SSCrazy

    Points: 2266

    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

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

    Abhijit - http://abhijitmore.wordpress.com

  • TimothyAWiseman

    SSCrazy Eights

    Points: 8819

    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

    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: 281243

    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

    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

    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: 5419

    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

    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!

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

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