Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2003
    Posts
    1

    Unanswered: How to lock a set of tables???

    Hi all,

    Let's image I have a set of tables (for ex. 3 tables) which are logically connected with each other (i.e. every time I'm changing something in one table I necessarily will make some changes to other 2 tables).
    Well, now I have two application - one is changing these tables (synchronising tables' content with application's environment) and another one which time by time reading these tables and makes some operations base on tables' content.
    The problem is - if the first application started changes but not commited transaction yet and the second application running SELECT clause, then I could get a "wrong" data in the second app, i.e. content of some tables will be updated but content of other tables are old.
    The ways to solve this... I think that it's possible to lock these tables before making changes in the first application and unlock tables after changes are complete. Then the second application will be secured with up to date content. But I can't find out how to lock a set of tables. If anyone knows how to do it plase help! Any kind of help would be appreciated!

    thnx

  2. #2
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    Why not begin a transaction before changing the first table and then commit or rollback after changing the last table?
    Paul Young
    (Knowledge is power! Get some!)

  3. #3
    Join Date
    Mar 2003
    Posts
    1

    Re: How to lock a set of tables???

    You can do it by 2 ways

    1) SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
    2)By Giving Lock hints on target tables (HOLDLOCK,TABLOCKX) --which is equivalent to SERIALIZABLE

    more information on BOL for "lock hints"

    vish



    Originally posted by armen_gg
    Hi all,

    Let's image I have a set of tables (for ex. 3 tables) which are logically connected with each other (i.e. every time I'm changing something in one table I necessarily will make some changes to other 2 tables).
    Well, now I have two application - one is changing these tables (synchronising tables' content with application's environment) and another one which time by time reading these tables and makes some operations base on tables' content.
    The problem is - if the first application started changes but not commited transaction yet and the second application running SELECT clause, then I could get a "wrong" data in the second app, i.e. content of some tables will be updated but content of other tables are old.
    The ways to solve this... I think that it's possible to lock these tables before making changes in the first application and unlock tables after changes are complete. Then the second application will be secured with up to date content. But I can't find out how to lock a set of tables. If anyone knows how to do it plase help! Any kind of help would be appreciated!

    thnx

Posting Permissions

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