Professional Web Applications Themes

Lock a table - Microsoft SQL / MS SQL Server

I have the following situation create proc test truncate table A IF C=B BEGIN insert into table #B .... .... .... more calculations etc... insert into table A from #B END Now the problem is I have another process which reads from table A but because the above stored procedure takes long that means because the table is truncated and takes a while to be re-populated the second process reads a blank table sometimes!! Is anyway I can specify a lock so if a table is being updated in a stored proc it will wait for it? I don't think ...

  1. #1

    Default Lock a table

    I have the following situation

    create proc test
    truncate table A

    IF C=B
    BEGIN
    insert into table #B
    ....
    ....
    ....
    more calculations etc...

    insert into table A
    from #B
    END



    Now the problem is I have another process which reads from
    table A but because the above stored procedure takes long
    that means because the table is truncated and takes a
    while to be re-populated the second process reads a blank
    table sometimes!!

    Is anyway I can specify a lock so if a table is being
    updated in a stored proc it will wait for it? I don't
    think I can use begin and commit tran to lock the table
    and cannot move the truncate just before the data insert
    cause there are more blocks of if statements.

    Any other ideas?

    Thanks,

    Panos.

    Panos Stavroulis Guest

  2. #2

    Default Lock a table

    I had a look at the locks (lockx etc) I think this will
    not guarantee me the exclusivety so will have to move my
    truncate and insert statements together to make a proper
    begin commit.

    Thanks everybody for their replies.

    Panos, London
    >-----Original Message-----
    >There is locking hint which you can specify for select
    >insert update and delete statements in transaction which
    >will override the current isolation level.
    >
    >Note: use it with care try not to use it in a deployment
    >environment because you may affect the isolation levels
    >for on going transaction
    >
    >Go to SQL server books on line and check for information
    >on locking
    >
    >Regards Arun
    >
    >
    >>-----Original Message-----
    >>I have the following situation
    >>
    >>create proc test
    >>truncate table A
    >>
    >>IF C=B
    >>BEGIN
    >>insert into table #B
    >>....
    >>....
    >>....
    >>more calculations etc...
    >>
    >>insert into table A
    >>from #B
    >>END
    >>
    >>
    >>
    >>Now the problem is I have another process which reads
    >from
    >>table A but because the above stored procedure takes
    long
    >>that means because the table is truncated and takes a
    >>while to be re-populated the second process reads a
    blank
    >>table sometimes!!
    >>
    >>Is anyway I can specify a lock so if a table is being
    >>updated in a stored proc it will wait for it? I don't
    >>think I can use begin and commit tran to lock the table
    >>and cannot move the truncate just before the data insert
    >>cause there are more blocks of if statements.
    >>
    >>Any other ideas?
    >>
    >>Thanks,
    >>
    >>Panos.
    >>
    >>.
    >>
    >.
    >
    panos Guest

Similar Threads

  1. Which (table) lock mode to use
    By Ben in forum PostgreSQL / PGSQL
    Replies: 2
    Last Post: December 15th, 05:49 AM
  2. Access + Asp + multiuser + table lock
    By Patrick in forum ASP Database
    Replies: 18
    Last Post: April 17th, 05:16 PM
  3. lock table overflow
    By Bill Hamilton in forum Informix
    Replies: 12
    Last Post: August 21st, 09:00 PM
  4. Replies: 2
    Last Post: August 12th, 07:55 AM
  5. Transactions and Table Lock
    By Andreas Bretl in forum Microsoft SQL / MS SQL Server
    Replies: 0
    Last Post: July 4th, 01:02 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139