Results 1 to 5 of 5

Thread: Locking the whole table

  1. 05-24-2002, 09:03 AM #1
    Efthymios Kalyviotis Guest

    Locking the whole table

    Hello,
    I am working on the implementation of a database for my company and I have
    a simple (I hope) question to ask.
    I have the following stored procedure that will be running when I want
    (actually when I want to delete a customer).
    Do not mind if you do not understand what this procedure does... ;-)
    Actually the Level8View is a VIEW of a nested table
    (CustomerData->CustomerData).

    CREATE Procedure DeleteCustomer
    @ClientID INT
    AS
    UPDATE Level8View
    SET UpCode = Level2, Level1=Level2, Level2=Level3, Level3=Level4, Level4=Level5, Level5=Level6, Level6=Level7, Level7=Level8
    WHERE Level1=@ClientID
    UPDATE Level8View
    SET Level2=Level3, Level3=Level4, Level4=Level5, Level5=Level6, Level6=Level7, Level7=Level8
    WHERE Level2=@ClientID
    UPDATE Level8View
    SET Level3=Level4, Level4=Level5, Level5=Level6, Level6=Level7, Level7=Level8
    WHERE Level3=@ClientID
    UPDATE Level8View
    SET Level4=Level5, Level5=Level6, Level6=Level7, Level7=Level8
    WHERE Level4=@ClientID
    UPDATE Level8View
    SET Level5=Level6, Level6=Level7, Level7=Level8
    WHERE Level5=@ClientID
    UPDATE Level8View
    SET Level6=Level7, Level7=Level8
    WHERE Level6=@ClientID
    UPDATE Level8View
    SET Level7=Level8
    WHERE Level7=@ClientID
    DELETE FROM Customers
    WHERE ClientID=@ClientID
    GO

    I checked this procedure and works perfectly.
    What I want to do is to somehow lock the WHOLE TABLE (CustomerData) or the
    view (Level8View) before the Update statements and unlock it after the
    delete statements.
    I do not want to do a Row by Row lock, or Page by Page lock since the updates
    in this table occur in the whole table and during that operation I do not
    want other threads to issue SELECT, INSERT or UPDATE statements.

    Can someone answer me how I can lock and unlock the whole view or table
    please?

    I will appreciate it for your answer. Thanks.

    Yours, sincerely
    Efthymios Kalyviotis
    ekalyviotis@comerclub.gr
    Reply With Quote Reply With Quote
  2. 05-24-2002, 11:35 AM #2
    Bill Guest

    Locking the whole table (reply)

    Surround the body of your stored procedure with BEGIN TRANSACTION and COMMIT TRANSACTION. Next, change your first UPDATE statement by adding an exclusive table lock hint - see below. The lock will stay in place until the end of the transaction.

    UPDATE Level8View WITH (TABLOCKX)
    SET UpCode = Level2, Level1=Level2, Level2=Level3, Level3=Level4, Level4=Level5, Level5=Level6, Level6=Level7, Level7=Level8
    WHERE Level1=@ClientID




    ------------
    Efthymios Kalyviotis at 5/24/2002 10:03:11 AM

    Hello,
    I am working on the implementation of a database for my company and I have
    a simple (I hope) question to ask.
    I have the following stored procedure that will be running when I want
    (actually when I want to delete a customer).
    Do not mind if you do not understand what this procedure does... ;-)
    Actually the Level8View is a VIEW of a nested table
    (CustomerData->CustomerData).

    CREATE Procedure DeleteCustomer
    @ClientID INT
    AS
    UPDATE Level8View
    SET UpCode = Level2, Level1=Level2, Level2=Level3, Level3=Level4, Level4=Level5, Level5=Level6, Level6=Level7, Level7=Level8
    WHERE Level1=@ClientID
    UPDATE Level8View
    SET Level2=Level3, Level3=Level4, Level4=Level5, Level5=Level6, Level6=Level7, Level7=Level8
    WHERE Level2=@ClientID
    UPDATE Level8View
    SET Level3=Level4, Level4=Level5, Level5=Level6, Level6=Level7, Level7=Level8
    WHERE Level3=@ClientID
    UPDATE Level8View
    SET Level4=Level5, Level5=Level6, Level6=Level7, Level7=Level8
    WHERE Level4=@ClientID
    UPDATE Level8View
    SET Level5=Level6, Level6=Level7, Level7=Level8
    WHERE Level5=@ClientID
    UPDATE Level8View
    SET Level6=Level7, Level7=Level8
    WHERE Level6=@ClientID
    UPDATE Level8View
    SET Level7=Level8
    WHERE Level7=@ClientID
    DELETE FROM Customers
    WHERE ClientID=@ClientID
    GO

    I checked this procedure and works perfectly.
    What I want to do is to somehow lock the WHOLE TABLE (CustomerData) or the
    view (Level8View) before the Update statements and unlock it after the
    delete statements.
    I do not want to do a Row by Row lock, or Page by Page lock since the updates
    in this table occur in the whole table and during that operation I do not
    want other threads to issue SELECT, INSERT or UPDATE statements.

    Can someone answer me how I can lock and unlock the whole view or table
    please?

    I will appreciate it for your answer. Thanks.

    Yours, sincerely
    Efthymios Kalyviotis
    ekalyviotis@comerclub.gr
    Reply With Quote Reply With Quote
  3. 05-24-2002, 11:45 AM #3
    Bill Guest

    Locking the whole table (reply)

    One problem with my solution is that you can only use a view lock hint (not table lock hint in this case) in a SELECT statement. So, if you can, re-write your UPDATE statements using the actual table(s).


    ------------
    Bill at 5/24/2002 12:35:29 PM

    Surround the body of your stored procedure with BEGIN TRANSACTION and COMMIT TRANSACTION. Next, change your first UPDATE statement by adding an exclusive table lock hint - see below. The lock will stay in place until the end of the transaction.

    UPDATE Level8View WITH (TABLOCKX)
    SET UpCode = Level2, Level1=Level2, Level2=Level3, Level3=Level4, Level4=Level5, Level5=Level6, Level6=Level7, Level7=Level8
    WHERE Level1=@ClientID




    ------------
    Efthymios Kalyviotis at 5/24/2002 10:03:11 AM

    Hello,
    I am working on the implementation of a database for my company and I have
    a simple (I hope) question to ask.
    I have the following stored procedure that will be running when I want
    (actually when I want to delete a customer).
    Do not mind if you do not understand what this procedure does... ;-)
    Actually the Level8View is a VIEW of a nested table
    (CustomerData->CustomerData).

    CREATE Procedure DeleteCustomer
    @ClientID INT
    AS
    UPDATE Level8View
    SET UpCode = Level2, Level1=Level2, Level2=Level3, Level3=Level4, Level4=Level5, Level5=Level6, Level6=Level7, Level7=Level8
    WHERE Level1=@ClientID
    UPDATE Level8View
    SET Level2=Level3, Level3=Level4, Level4=Level5, Level5=Level6, Level6=Level7, Level7=Level8
    WHERE Level2=@ClientID
    UPDATE Level8View
    SET Level3=Level4, Level4=Level5, Level5=Level6, Level6=Level7, Level7=Level8
    WHERE Level3=@ClientID
    UPDATE Level8View
    SET Level4=Level5, Level5=Level6, Level6=Level7, Level7=Level8
    WHERE Level4=@ClientID
    UPDATE Level8View
    SET Level5=Level6, Level6=Level7, Level7=Level8
    WHERE Level5=@ClientID
    UPDATE Level8View
    SET Level6=Level7, Level7=Level8
    WHERE Level6=@ClientID
    UPDATE Level8View
    SET Level7=Level8
    WHERE Level7=@ClientID
    DELETE FROM Customers
    WHERE ClientID=@ClientID
    GO

    I checked this procedure and works perfectly.
    What I want to do is to somehow lock the WHOLE TABLE (CustomerData) or the
    view (Level8View) before the Update statements and unlock it after the
    delete statements.
    I do not want to do a Row by Row lock, or Page by Page lock since the updates
    in this table occur in the whole table and during that operation I do not
    want other threads to issue SELECT, INSERT or UPDATE statements.

    Can someone answer me how I can lock and unlock the whole view or table
    please?

    I will appreciate it for your answer. Thanks.

    Yours, sincerely
    Efthymios Kalyviotis
    ekalyviotis@comerclub.gr
    Reply With Quote Reply With Quote
  4. 05-24-2002, 01:19 PM #4
    Efthymios Kalyviotis Guest

    Locking the whole table (reply)

    Thank you everyone for your replies. I thing that my problem is almost solved.
    Do you mean that if I lock a view the tables of this view aren't actually
    going to be locked?


    ------------
    Bill at 5/24/2002 12:45:49 PM

    One problem with my solution is that you can only use a view lock hint (not table lock hint in this case) in a SELECT statement. So, if you can, re-write your UPDATE statements using the actual table(s).


    ------------
    Bill at 5/24/2002 12:35:29 PM

    Surround the body of your stored procedure with BEGIN TRANSACTION and COMMIT TRANSACTION. Next, change your first UPDATE statement by adding an exclusive table lock hint - see below. The lock will stay in place until the end of the transaction.

    UPDATE Level8View WITH (TABLOCKX)
    SET UpCode = Level2, Level1=Level2, Level2=Level3, Level3=Level4, Level4=Level5, Level5=Level6, Level6=Level7, Level7=Level8
    WHERE Level1=@ClientID




    ------------
    Efthymios Kalyviotis at 5/24/2002 10:03:11 AM

    Hello,
    I am working on the implementation of a database for my company and I have
    a simple (I hope) question to ask.
    I have the following stored procedure that will be running when I want
    (actually when I want to delete a customer).
    Do not mind if you do not understand what this procedure does... ;-)
    Actually the Level8View is a VIEW of a nested table
    (CustomerData->CustomerData).

    CREATE Procedure DeleteCustomer
    @ClientID INT
    AS
    UPDATE Level8View
    SET UpCode = Level2, Level1=Level2, Level2=Level3, Level3=Level4, Level4=Level5, Level5=Level6, Level6=Level7, Level7=Level8
    WHERE Level1=@ClientID
    UPDATE Level8View
    SET Level2=Level3, Level3=Level4, Level4=Level5, Level5=Level6, Level6=Level7, Level7=Level8
    WHERE Level2=@ClientID
    UPDATE Level8View
    SET Level3=Level4, Level4=Level5, Level5=Level6, Level6=Level7, Level7=Level8
    WHERE Level3=@ClientID
    UPDATE Level8View
    SET Level4=Level5, Level5=Level6, Level6=Level7, Level7=Level8
    WHERE Level4=@ClientID
    UPDATE Level8View
    SET Level5=Level6, Level6=Level7, Level7=Level8
    WHERE Level5=@ClientID
    UPDATE Level8View
    SET Level6=Level7, Level7=Level8
    WHERE Level6=@ClientID
    UPDATE Level8View
    SET Level7=Level8
    WHERE Level7=@ClientID
    DELETE FROM Customers
    WHERE ClientID=@ClientID
    GO

    I checked this procedure and works perfectly.
    What I want to do is to somehow lock the WHOLE TABLE (CustomerData) or the
    view (Level8View) before the Update statements and unlock it after the
    delete statements.
    I do not want to do a Row by Row lock, or Page by Page lock since the updates
    in this table occur in the whole table and during that operation I do not
    want other threads to issue SELECT, INSERT or UPDATE statements.

    Can someone answer me how I can lock and unlock the whole view or table
    please?

    I will appreciate it for your answer. Thanks.

    Yours, sincerely
    Efthymios Kalyviotis
    ekalyviotis@comerclub.gr
    Reply With Quote Reply With Quote
  5. 05-24-2002, 01:28 PM #5
    Bill Guest

    Locking the whole table (reply)

    No, actually you aren't able to even use lock hints in an UPDATE statement on a view. However, if you write your first UPDATE statement with the underlying tables instead of the view and use the TABLOCKX lock hint, the lock will be held on that table until the transaction is committed at the end.


    ------------
    Efthymios Kalyviotis at 5/24/2002 2:19:36 PM

    Thank you everyone for your replies. I thing that my problem is almost solved.
    Do you mean that if I lock a view the tables of this view aren't actually
    going to be locked?


    ------------
    Bill at 5/24/2002 12:45:49 PM

    One problem with my solution is that you can only use a view lock hint (not table lock hint in this case) in a SELECT statement. So, if you can, re-write your UPDATE statements using the actual table(s).


    ------------
    Bill at 5/24/2002 12:35:29 PM

    Surround the body of your stored procedure with BEGIN TRANSACTION and COMMIT TRANSACTION. Next, change your first UPDATE statement by adding an exclusive table lock hint - see below. The lock will stay in place until the end of the transaction.

    UPDATE Level8View WITH (TABLOCKX)
    SET UpCode = Level2, Level1=Level2, Level2=Level3, Level3=Level4, Level4=Level5, Level5=Level6, Level6=Level7, Level7=Level8
    WHERE Level1=@ClientID




    ------------
    Efthymios Kalyviotis at 5/24/2002 10:03:11 AM

    Hello,
    I am working on the implementation of a database for my company and I have
    a simple (I hope) question to ask.
    I have the following stored procedure that will be running when I want
    (actually when I want to delete a customer).
    Do not mind if you do not understand what this procedure does... ;-)
    Actually the Level8View is a VIEW of a nested table
    (CustomerData->CustomerData).

    CREATE Procedure DeleteCustomer
    @ClientID INT
    AS
    UPDATE Level8View
    SET UpCode = Level2, Level1=Level2, Level2=Level3, Level3=Level4, Level4=Level5, Level5=Level6, Level6=Level7, Level7=Level8
    WHERE Level1=@ClientID
    UPDATE Level8View
    SET Level2=Level3, Level3=Level4, Level4=Level5, Level5=Level6, Level6=Level7, Level7=Level8
    WHERE Level2=@ClientID
    UPDATE Level8View
    SET Level3=Level4, Level4=Level5, Level5=Level6, Level6=Level7, Level7=Level8
    WHERE Level3=@ClientID
    UPDATE Level8View
    SET Level4=Level5, Level5=Level6, Level6=Level7, Level7=Level8
    WHERE Level4=@ClientID
    UPDATE Level8View
    SET Level5=Level6, Level6=Level7, Level7=Level8
    WHERE Level5=@ClientID
    UPDATE Level8View
    SET Level6=Level7, Level7=Level8
    WHERE Level6=@ClientID
    UPDATE Level8View
    SET Level7=Level8
    WHERE Level7=@ClientID
    DELETE FROM Customers
    WHERE ClientID=@ClientID
    GO

    I checked this procedure and works perfectly.
    What I want to do is to somehow lock the WHOLE TABLE (CustomerData) or the
    view (Level8View) before the Update statements and unlock it after the
    delete statements.
    I do not want to do a Row by Row lock, or Page by Page lock since the updates
    in this table occur in the whole table and during that operation I do not
    want other threads to issue SELECT, INSERT or UPDATE statements.

    Can someone answer me how I can lock and unlock the whole view or table
    please?

    I will appreciate it for your answer. Thanks.

    Yours, sincerely
    Efthymios Kalyviotis
    ekalyviotis@comerclub.gr
    Reply With Quote Reply With Quote
« Previous Thread | Next Thread »

Posting Permissions

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

Forum Rules