Results 1 to 10 of 10
  1. #1
    Join Date
    Nov 2003
    Location
    SF Bay Area
    Posts
    18

    Unanswered: Locking a table for the duration of a transaction.

    We have a nightly script that drops and rebuilds a table. Problem is before the script has had a chance to complete the building of the primary key, it fails due to some other process gaining access to the table.

    My question is this: how do I lock a table for the duration of the transaction.

    Here is what I think would work:

    SET ISOLATION LEVEL SERIALIZABLE

    BEGIN TRANSACTION

    DROP TABLE <table>...
    CREATE TABLE <table>...

    INSERT INTO <table> WITH (TABLOCKX) ...

    ALTER TABLE <table> ADD CONSTRAINT PRIMARY KEY ...

    COMMIT

    Any insight as to whether or not I am correct, or if I am not, what would be the correct way.

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Does anyone else have access to the db? Do you have a maintenence window?

    What if you ALTER the database and set it to single user mode?

    My money however is that your blocking yourself...

    Can you do sp_lock when the process is running?

    Do you use xp_cmdshell anywhere?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Sep 2003
    Location
    Dallas, Texas
    Posts
    311

    Re: Locking a table for the duration of a transaction.

    Move the drop table out and execute it before the begin transaction. That way the table will no long be in the database. Even someone tries to access the table, it would be errroed out instead of locking it.

  4. #4
    Join Date
    Nov 2003
    Location
    SF Bay Area
    Posts
    18
    Originally posted by Brett Kaiser
    Does anyone else have access to the db? Do you have a maintenence window?

    What if you ALTER the database and set it to single user mode?

    My money however is that your blocking yourself...

    Can you do sp_lock when the process is running?

    Do you use xp_cmdshell anywhere?
    Unfortunately we can not have exclusive access to the database and can not enter into single user mode.

    I am more familiar with Informix and Oracle -- I know with those databases there are explicit LOCK statements.

  5. #5
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Why drop the table at all? Can truncate be used instead?

  6. #6
    Join Date
    Nov 2003
    Location
    SF Bay Area
    Posts
    18
    Originally posted by MCrowley
    Why drop the table at all? Can truncate be used instead?
    Still does not solve my problem. The problem is that from the time the statement starts (without being in a transation) to the time the primary key is being built, other processes are accessing the table which is causing the PK build to fail since it can not get exclusive access.

  7. #7
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    But if you only remove the data, the primary key remains on the table. How many rows of data are you talking about? Does the number/type of columns change between rebuilds?

  8. #8
    Join Date
    Nov 2003
    Location
    SF Bay Area
    Posts
    18
    Originally posted by MCrowley
    But if you only remove the data, the primary key remains on the table. How many rows of data are you talking about? Does the number/type of columns change between rebuilds?
    I see your point. The table has 30 million rows and growing. One of the main reasons for dropping and creating is to avoid the index maintenance that would result from the inserts.

    Unfortunately we can not alter the database into a BULK INSERT recovery mode either.

  9. #9
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Originally posted by bryanhughes
    I see your point. The table has 30 million rows and growing. One of the main reasons for dropping and creating is to avoid the index maintenance that would result from the inserts.

    Unfortunately we can not alter the database into a BULK INSERT recovery mode either.
    Wait...every night you drop a table that has 30 million rows, then recreate it?

    I gotta be missing something.....

    Does DBCC DBREINDEX Cost that much that a drop and reload, and the rebuild PK...not to mention any other indexes...is cheaper?

    Does that about sum it up?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  10. #10
    Join Date
    Nov 2003
    Location
    SF Bay Area
    Posts
    18
    Originally posted by Brett Kaiser
    Wait...every night you drop a table that has 30 million rows, then recreate it?

    I gotta be missing something.....

    Does DBCC DBREINDEX Cost that much that a drop and reload, and the rebuild PK...not to mention any other indexes...is cheaper?

    Does that about sum it up?
    The table is a derived table and is used during the business day. It only takes about 30 minutes to build.

Posting Permissions

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