Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601

    Unanswered: Can I lock a group of tables across several SQL statements?

    I have an application which contains a half-dozen tables with data hierarchically related from the first through the last table.

    In other words,
    the top level data table is table A
    Then table B is related to table A via table A's primary key identity.
    Then table C is related to table B via table B's primary key identity.
    Then table D is related to table C via table C's primary key identity.
    Then table E is related to table D via table D's primary key identity.
    And finally, table F is related to table E via table E's primary key identity.

    The data in these tables is static and the tables are used for "master file lookups."

    Occassionally the tables need to be refreshed with new data and to do this I have to wipe-out and rebuild all six tables. Because of the hierarchical relationship between the tables, they are rebuilt sequentially, first A, then B, then C, etc.

    While the tables are being rebuilt, the data is not valid. To insure that the user does not get invalid data, I first wipe-out the contents of all the tables and then rebuild them sequentially.

    The whole rebuild only takes five seconds, but during that five seconds it is possible that a user might attempt to read data from one of the empty tables.

    How do I put a lock on all five tables until the rebuild on all the tables is complete?

    Here is a mockup of my code and of what I would like to do:

    LOCK TABLE A
    LOCK TABLE B
    LOCK TABLE C
    LOCK TABLE D
    LOCK TABLE E
    LOCK TABLE F

    DELETE FROM TABLE A
    DELETE FROM TABLE B
    DELETE FROM TABLE C
    DELETE FROM TABLE D
    DELETE FROM TABLE E
    DELETE FROM TABLE F

    INSERT INTO TABLE A SELECT * FROM TBLDATA
    INSERT INTO TABLE B SELECT * FROM TBLDATA
    INSERT INTO TABLE C SELECT * FROM TBLDATA
    INSERT INTO TABLE D SELECT * FROM TBLDATA
    INSERT INTO TABLE E SELECT * FROM TBLDATA
    INSERT INTO TABLE F SELECT * FROM TBLDATA

    UNLOCK TABLE A
    UNLOCK TABLE B
    UNLOCK TABLE C
    UNLOCK TABLE D
    UNLOCK TABLE E
    UNLOCK TABLE F
    Ken

    Maverick Software Design

    (847) 864-3600 x2

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Like many problems that seem hard, the difficulty is often because there is a better way to get what you want. If you use a transaction, you can monkey with the tables to your heart's content and have all of your changes appear in one swell foop, like a flashbulb. You need to do something like:
    Code:
    BEGIN TRANSACTION
    
    DELETE FROM TABLE A
    DELETE FROM TABLE B
    DELETE FROM TABLE C
    DELETE FROM TABLE D
    DELETE FROM TABLE E
    DELETE FROM TABLE F
    
    INSERT INTO TABLE A SELECT * FROM TBLDATA
    INSERT INTO TABLE B SELECT * FROM TBLDATA
    INSERT INTO TABLE C SELECT * FROM TBLDATA
    INSERT INTO TABLE D SELECT * FROM TBLDATA
    INSERT INTO TABLE E SELECT * FROM TBLDATA
    INSERT INTO TABLE F SELECT * FROM TBLDATA
    
    COMMIT TRANSACTION -- here is where other users will see everything
    -- that you've done since the BEGIN TRANSACTION
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Posting Permissions

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