Does a Query Lock to the Table_Name or Table_ID

  • SQL 2017 ENT

    SCENARIO:

    Table_1 has a Sch-M lock on during an intended updating process.

    A QUERY is executed against Table_1 and is queued up, waiting for the Sch-M lock to release.

    While the QUERY is queued up on Table_1, Table_1 is renamed to Table_1_OLD

    A newly designed Table_2 is then renamed to Table_1

    QUESTION:

    Does the queued up QUERY identifying Table_1 by its NAME or by its TABLE_ID number ?

    Or, in other words, will that queued up QUERY execute against Table_1_OLD because it followed the TABLE_ID of that renamed table, or does it execute against the newer recently renamed NAME of "Table_1" (that used to be Table_2) ?

  • I could be mistaken, but I am 99% sure that your schema lock is going to prevent the table rename. The rename will only commit to disk after the query completes.

    If I am reading that right, you are saying query 1 has the schema lock on the table, query 2 is waiting on the schema lock to release, then query 3 is trying to rename the table. If this is correct, the rename will require the schema lock to be released as well and will be waiting on query 1. If query 1 is what is renaming the table, query 2 SHOULD use the original table it was asking for (ie table_1_old) from my understanding as the execution plan would have been generated against that table.

    The easy way to know though is to test it. Set up your scenario and give it a shot and see what happens and let us know. I suspect that query 2 will either pull from Table_1_OLD OR will be a deadlock victim and be rolled back. I highly doubt that it will use table_2 which was renamed to Table_1.

    The above is all just my opinion on what you should do. 
    As with all advice you find on a random internet forum - you shouldn't blindly follow it.  Always test on a test server to see if there is negative side effects before making changes to live!
    I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

  • Great insight, B.G.

    Query/Transaction 1 is meant to RENAME table_1 to table_1_old. If another query is executed-and-pending against table_1 before the renaming process, then (from what I'm reading) should follow the table_ID of table_1 (not the user friendly name of that table), even after it's been renamed to table_1_old.

    Is that the correct logic to follow in determining how the PENDING query will behave ?

    Thank you,

    • This reply was modified 1 month, 1 week ago by  ESP.
  • References in SQL are to object names.  The code will resolve based on the name.

    A rename can't happen while the table is being queried, because a schema lock is in place.  Once the object is free from locks and renamed, all the references are to the new names, not the old ones.

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

  • heh, so Scott confirmed what I was assuming, but also clarified which way things would go (read from old table, not be a deadlock victim).

    But, my advice when questions like this come up, try it out in your test environment and see what happens. Create a new DB in the test environment and write scripts to test your scenario. I mean, I fully trust ScottPletcher's reply (he's a smart guy), but I also don't trust the reply from random strangers on the internet without testing and proving it to myself. I'd also be a bit leery of any AI generated answers. I've seen them give some really weird results to simple questions (like that strawberry has 2 r's in it and when told that is wrong, it corrects itself to say you are right... there is only 1 r in strawberry) or giving really bad suggestions (like putting non toxic glue on your pizza to help the cheese stick better), so I'd be hesitant to trust AI with anything I haven't verified myself OR got from a verified trusted source.

    I think my distrust of strangers on the internet came around the same time that advice for a lot of things in windows was to press alt+F4 and in linux it was to save space by "remove the French language pack installed by default" by running "rm -rf /". Both of these are bad advice. Heck, windows forums also had a few people posting that to "test" if your drive was valid to format in a specific format, you could use the "/autotest" flag on your format command. For those unaware, putting "/autotest" on the end of a format will format the disk without any user prompts or warnings. It does retest all bad blocks during the format, but your drive will be blank when it completes.

    So I always test anything myself if possible or find a trusted source for the information rather than just assuming or trusting random people online.

    The above is all just my opinion on what you should do. 
    As with all advice you find on a random internet forum - you shouldn't blindly follow it.  Always test on a test server to see if there is negative side effects before making changes to live!
    I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

  • Brian and Scott.

    Excellent insight and logic provided. Thank you both !!!

    P.S. AI = Autonomous Ignoramus

Viewing 6 posts - 1 through 5 (of 5 total)

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