none
Lock in one single table

    Question

  • Hi Experts!

     

    I'm working in a DB and there are several tables. My work consists to develop in test tables in this DB; but everytime that start a transaction with BEGIN TRANSACTION I lock the whole DB.

     

    There are some form to lock only the table that I'm working? How can I do this?

     

    Thx!

    Thursday, November 24, 2011 1:08 PM

Answers

  • Hi Iniciante em SQL,

    What is the version of the SQL Server instance? What is the isolation level of your database? Which type of lock do you applied within the transaction? Could you please post the stored procedure you worked with?

    Do you mean that if you start the stored procedure, the other DML operations on these tables are blocked? It seems that there are table level locks, such as the exclusive lock, which is incompatible with IX lock, are held on these tables until the transaction completes. For a DML (UPDATE/DELETE/INSERT) operation, it might require an X lock on the target records, and an IX lock on both corresponding page and table. In this case, DML operations on these tables cannot be allowed since IX lock cannot be allowed in the table level. Overloading locks can lead to negative effects. If the SELECT opration is performed frequently, you can make use of snapshot isolation level.

    If there are partitioned tables in SQL Server 2008, just leave the LOCK_Escalation to AUTO to enable the partition level lock escalation. For more information about locking behavior in partitioned table, please refer to this blog: Enabling Partition Level Locking in SQL Server 2008.

    Best Regards,
    Stephanie Lv


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    • Proposed as answer by Stephanie Lv Monday, December 05, 2011 11:10 AM
    • Marked as answer by Stephanie Lv Wednesday, December 07, 2011 8:08 AM
    Tuesday, November 29, 2011 7:29 AM

All replies

  • >>>>There are some form to lock only the table that I'm working?

    SELECT  top 0 * FROM qq (TABLOCK, REPEATABLEREAD)

     

    If you are operatting on all  your tables and those DML(update/delete/insert) is wrapted into BEGIN TRAN that means till the is no COMMIT or ROLLBACK the rows will be locked..... If you issue single DML there is NO need BEGIN TRAN or you are not concern that if there is an error then all batch should be rolled back... Can you show us an example of your development?


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Thursday, November 24, 2011 1:15 PM
    Answerer
  • The problem that Production and Development are the same; and if I make some lock, I'll lock the whole operation; but I cannot make any DML on the DB without this security procedure.

    This way, I work in this structure:

    ProductionTable1

    ProductionTable2

    ProductionTable3

    DevelopmentTable1

    DevelopmentTable2

     

    I do my job only in these Dev tables, but if a create a lock (BEGIN TRANSACTION) I stop the whole database.

     

    In these case, can I do exclusive lock in the table that I'm working?

     

    Thanks! 

    Thursday, November 24, 2011 1:32 PM
  • Wow, why do you need to mess them up? In case you need to do all job Development server and not on prod.

    Try getting all the data than prod. tables have into a temporary table and work against them....


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Thursday, November 24, 2011 1:38 PM
    Answerer
  • Uri,

    the reason that these two environments are mess is financial reasons. In the moment we don't have a separeted server yet.

    I would like to follow your tip,but I can't  'cause we have more than 4TB of data.

    Thanks for your time.
    Thursday, November 24, 2011 11:52 PM
  • Hi Iniciante em SQL,

    What is the version of the SQL Server instance? What is the isolation level of your database? Which type of lock do you applied within the transaction? Could you please post the stored procedure you worked with?

    Do you mean that if you start the stored procedure, the other DML operations on these tables are blocked? It seems that there are table level locks, such as the exclusive lock, which is incompatible with IX lock, are held on these tables until the transaction completes. For a DML (UPDATE/DELETE/INSERT) operation, it might require an X lock on the target records, and an IX lock on both corresponding page and table. In this case, DML operations on these tables cannot be allowed since IX lock cannot be allowed in the table level. Overloading locks can lead to negative effects. If the SELECT opration is performed frequently, you can make use of snapshot isolation level.

    If there are partitioned tables in SQL Server 2008, just leave the LOCK_Escalation to AUTO to enable the partition level lock escalation. For more information about locking behavior in partitioned table, please refer to this blog: Enabling Partition Level Locking in SQL Server 2008.

    Best Regards,
    Stephanie Lv


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    • Proposed as answer by Stephanie Lv Monday, December 05, 2011 11:10 AM
    • Marked as answer by Stephanie Lv Wednesday, December 07, 2011 8:08 AM
    Tuesday, November 29, 2011 7:29 AM