Thread: Transaction and table partitioning

Hello<br /><br /> Is it possible to use transactions with 'per sub table' locks? What I mean,if I partition a table and
usetransaction on that table with constraint,will the database lock the master table (and all subtables),or only sub
tablewhere the data is ? <br /><br /> Sincerely<br /><br /> Dragan Zubac<br /> 

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

On Mon, Jul 03, 2006 at 14:59:49 +0200, Dragan Zubac <zubacdragan@gmail.com> 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.

"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