Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2004
    Location
    Calgary, Alberta
    Posts
    106

    Unanswered: pre-emptive locking solution

    Hey all,

    I've got a very long stored proc that runs intensive updates on a particular table. The locks are always escalated from Intent eXclusive to eXclusive. After some reading online, I've decided to implement this . The idea is to start a transaction with another spid, and hold an incompatible lock on that table so the stored procedure that I'm running isn't able to escalate the lock. The solution works, but it unfortunately means that I've to lock this table with an update lock for the whole stored proc, which I would rather not do.

    Is it possible to spawn another stored proc/function/transaction under another spid from within my stored proc ? I'm hoping the answer to my question isn't here.

    Is it maybe possible to open another connection within my stored proc ? On a similar note, would it be possible to communicate somehow between connections without using a table ?

    Thanks,
    -Kilka

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    There are many ways to do this, but Transact-SQL is a bit limited in this area. It can be done, but it is brute force and ugly at best.

    Have you investigated DTS? At least in my experience, it handles this kind of processing much better than Transact-SQL can.

    -PatP

  3. #3
    Join Date
    Aug 2004
    Location
    Calgary, Alberta
    Posts
    106
    I've worked with DTS and I know the only way it could potentially help me was if I used scripting (activeX or something) to acheive the same thing.

    I'm trying to keep everything in a scheduled stored proc. If at all possible, I want to do everything in T-sql for performance reasons. This is something that takes hours to run, so any little performance hit has a big impact.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •