Professional Web Applications Themes

Which (table) lock mode to use - PostgreSQL / PGSQL

Hi I have this scenario and would like to use lock table for this but I don't know which mode I should use. When I delete/update/insert a record from/to a table, I need to update some fields of all the records in this table. During this process I don't want anyone to insert, update or delete the data but allow them to select the data at the original state. I am currently using: LOCK TABLE tbl_name IN SHARE ROW EXCLUSIVE MODE Is this the correct mode to use? Thanks, Ben ---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster...

  1. #1

    Default Which (table) lock mode to use

    Hi

    I have this scenario and would like to use lock table for this but I
    don't know which mode I should use.

    When I delete/update/insert a record from/to a table, I need to update
    some fields of all the records in this table. During this process I
    don't want anyone to insert, update or delete the data but allow them
    to select the data at the original state.

    I am currently using:

    LOCK TABLE tbl_name IN SHARE ROW EXCLUSIVE MODE

    Is this the correct mode to use?

    Thanks,
    Ben

    ---------------------------(end of broadcast)---------------------------
    TIP 4: Don't 'kill -9' the postmaster

    Ben Guest

  2. #2

    Default Re: Which (table) lock mode to use

    On Wed, Dec 15, 2004 at 11:55:34AM +1100, Ben wrote:
    > I have this scenario and would like to use lock table for this but I
    > don't know which mode I should use.
    >
    > When I delete/update/insert a record from/to a table, I need to update
    > some fields of all the records in this table.
    What's the purpose of the updates? If we knew more about what
    you're trying to do then maybe we could suggest alternate solutions.
    > During this process I don't want anyone to insert, update or delete
    > the data but allow them to select the data at the original state.
    >
    > I am currently using:
    >
    > LOCK TABLE tbl_name IN SHARE ROW EXCLUSIVE MODE
    >
    > Is this the correct mode to use?
    The "Explicit Locking" section of the "Concurrency Control" chapter
    in the doentation describes the available lock modes and their
    conflicts. SELECT acquires ACCESS SHARE; UPDATE, DELETE, and INSERT
    acquire ROW EXCLUSIVE, so it sounds like you want a lock that
    conflicts with ROW EXCLUSIVE but not with ACCESS SHARE. That gives
    the following possibilities:

    SHARE
    SHARE ROW EXCLUSIVE
    EXCLUSIVE

    See the doentation for these modes' conflicts and decide which
    is most appropriate. But again, what problem are you trying to
    solve? Maybe there's a better way than locking an entire table.

    --
    Michael Fuhr
    [url]http://www.fuhr.org/~mfuhr/[/url]

    ---------------------------(end of broadcast)---------------------------
    TIP 2: you can get off all lists at once with the unregister command
    (send "unregister YourEmailAddressHere" to [email]majordomopostgresql.org[/email])

    Michael Fuhr Guest

  3. #3

    Default Re: Which (table) lock mode to use

    > What's the purpose of the updates? If we knew more about what
    > you're trying to do then maybe we could suggest alternate solutions.
    There are two fields (of all the records) I need to update when I
    insert/update/delete a record. These two fields contain numbers and
    they act as a link between the records within the table. If I don't
    update these numbers the link will be broken.

    Thanks,
    Ben


    On Wed, 15 Dec 2004 16:47:53 +1100, Ben <newreaders> wrote:
    > > What's the purpose of the updates? If we knew more about what
    > > you're trying to do then maybe we could suggest alternate solutions.
    >
    > There are two fields (of all the records) I need to update when I
    > insert/update/delete a record. These two fields contain numbers and
    > they act as a link between the records within the table. If I don't
    > update these numbers the link will be broken.
    >
    > > The "Explicit Locking" section of the "Concurrency Control" chapter
    > > in the doentation describes the available lock modes and their
    > > conflicts. SELECT acquires ACCESS SHARE; UPDATE, DELETE, and INSERT
    > > acquire ROW EXCLUSIVE, so it sounds like you want a lock that
    > > conflicts with ROW EXCLUSIVE but not with ACCESS SHARE. That gives
    > > the following possibilities:
    > >
    > > SHARE
    > > SHARE ROW EXCLUSIVE
    > > EXCLUSIVE
    >
    > Thank you, I will have another look at the doentation.
    >
    > Cheers,
    > Ben
    >
    > On Tue, 14 Dec 2004 20:36:08 -0700, Michael Fuhr <mikefuhr.org> wrote:
    > > On Wed, Dec 15, 2004 at 11:55:34AM +1100, Ben wrote:
    > >
    > > > I have this scenario and would like to use lock table for this but I
    > > > don't know which mode I should use.
    > > >
    > > > When I delete/update/insert a record from/to a table, I need to update
    > > > some fields of all the records in this table.
    > >
    > > What's the purpose of the updates? If we knew more about what
    > > you're trying to do then maybe we could suggest alternate solutions.
    > >
    > > > During this process I don't want anyone to insert, update or delete
    > > > the data but allow them to select the data at the original state.
    > > >
    > > > I am currently using:
    > > >
    > > > LOCK TABLE tbl_name IN SHARE ROW EXCLUSIVE MODE
    > > >
    > > > Is this the correct mode to use?
    > >
    > > The "Explicit Locking" section of the "Concurrency Control" chapter
    > > in the doentation describes the available lock modes and their
    > > conflicts. SELECT acquires ACCESS SHARE; UPDATE, DELETE, and INSERT
    > > acquire ROW EXCLUSIVE, so it sounds like you want a lock that
    > > conflicts with ROW EXCLUSIVE but not with ACCESS SHARE. That gives
    > > the following possibilities:
    > >
    > > SHARE
    > > SHARE ROW EXCLUSIVE
    > > EXCLUSIVE
    > >
    > > See the doentation for these modes' conflicts and decide which
    > > is most appropriate. But again, what problem are you trying to
    > > solve? Maybe there's a better way than locking an entire table.
    > >
    > > --
    > > Michael Fuhr
    > > [url]http://www.fuhr.org/~mfuhr/[/url]
    > >
    >
    ---------------------------(end of broadcast)---------------------------
    TIP 4: Don't 'kill -9' the postmaster

    Ben Guest

Similar Threads

  1. Access + Asp + multiuser + table lock
    By Patrick in forum ASP Database
    Replies: 18
    Last Post: April 17th, 05:16 PM
  2. LOCK MODE Question
    By David Snyder in forum Informix
    Replies: 2
    Last Post: September 9th, 01:00 PM
  3. lock table overflow
    By Bill Hamilton in forum Informix
    Replies: 12
    Last Post: August 21st, 09:00 PM
  4. Transactions and Table Lock
    By Andreas Bretl in forum Microsoft SQL / MS SQL Server
    Replies: 0
    Last Post: July 4th, 01:02 PM
  5. Lock a table
    By Panos Stavroulis in forum Microsoft SQL / MS SQL Server
    Replies: 1
    Last Post: July 1st, 01:47 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