FAQ
Hello

Is it possible to use transactions with 'per sub table' locks? What I
mean,if I partition a table and use transaction on that table with
constraint,will the database lock the master table (and all subtables),or
only sub table where the data is ?

Sincerely

Dragan Zubac

Search Discussions

  • Simon Riggs at Jul 3, 2006 at 4:44 pm

    On Mon, 2006-07-03 at 14:59 +0200, Dragan Zubac wrote:

    Is it possible to use transactions with 'per sub table' locks? What I
    mean,if I partition a table and use transaction on that table with
    constraint,will the database lock the master table (and all
    subtables),or only sub table where the data is ?
    If you reference only the sub-table then there will be no locks on the
    master or other partition tables, assuming you mean to use just DML
    rather than DDL. The tables are more loosely coupled than they are in
    other RDBMS implementations.

    --
    Simon Riggs
    EnterpriseDB http://www.enterprisedb.com
  • Bruno Wolff III at Jul 3, 2006 at 4:59 pm

    On Mon, Jul 03, 2006 at 14:59:49 +0200, Dragan Zubac wrote:

    Is it possible to use transactions with 'per sub table' locks? What I
    mean,if I partition a table and use transaction on that table with
    constraint,will the database lock the master table (and all subtables),or
    only sub table where the data is ?
    Are you sure locking will be a problem? MVCC avoids a lot of typical
    conflicts.
    If you are doing something that is locking the tables in a way that causes
    problems, I think you will need to be more specific about what you are
    doing to get useful suggestions.
  • Greg Stark at Jul 4, 2006 at 1:23 am

    "Dragan Zubac" <zubacdragan@gmail.com> writes:

    Hello

    Is it possible to use transactions with 'per sub table' locks? What I
    mean,if I partition a table and use transaction on that table with
    constraint,will the database lock the master table (and all subtables),or
    only sub table where the data is ?
    Unless you're doing ALTER TABLE or something like that Postgres never locks
    tables. Two backends can insert into the same table at the same time and
    neither has to wait until the other is done.

    If you are updating the same record then of course one has to wait but then
    partitioning isn't going to help in that case.

    --
    greg

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-hackers @
categoriespostgresql
postedJul 3, '06 at 12:59p
activeJul 4, '06 at 1:23a
posts4
users4
websitepostgresql.org...
irc#postgresql

People

Translate

site design / logo © 2021 Grokbase