How to know if sql applies lock on linked server table

  • qaz123 90964

    SSC Enthusiast

    Points: 107


    I have a select statement in sql 2000, with the select on a linked server (oracle) table.

    For example

    [font="Courier New"] select * from linkserver..dbname.my_table [/font]

    in SQL 2000 reportedly caused locking issues in oracle server where the actual table is located. I guess this is probably imposed by the linkedserver driver used.

    Thought this can be solved by having a view in oracle for the select and calling the view in SQL. While that works, I need to verify if calling the view has indeed prevented the lock from being imposed on the table. I mean, like knowing if lock has been acquired on a table? Any solution ?

    Also, how can i verify that the initial lock on a select on oracle table was imposed by the driver?

    Pls help

  • Jeff Moden

    SSC Guru

    Points: 997205

    I'm not sure but I believe you'd need to look at locking/blocking on the Oracle side. I also don't believe that the use of a view is going to solve any locking/blocking issues. Last but not least, a SELECT shouldn't provide any blocking unless it's a part of a multi-query transaction.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

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

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