Postgres Pro
Downloads
Home   >   mailing lists

Thread: row level locking

row level locking

From
"Mark Steben"
Date:

Hi listers,

I have a table that is accessed by only one transaction.  It has 1500 rows each time that it processes

This transaction.  As soon as this transaction ends, it waits 10 seconds and starts again. 

It has another maximum 1500 rows loaded. . 

 

My question is this:  I believe a RowExclusive level lock is acquired 1500 times during each transaction.

Is this true?  Is there a way to disable RowExclusive locks and reduce overhead since only this transaction

Accesses this table.   I know there are ways to specify locking levels in other DBMSs. To page or table levels.

We are currently at version 7.4.5.

 

Thank you for your time,

 

 

Mark Steben

Senior Database Administrator
@utoRevenue™
A Dominion Enterprises Company
480 Pleasant Street
Suite B200
Lee, MA 01238
413-243-4800 Home Office 
413-243-4809 Corporate Fax

msteben@autorevenue.com

Visit our new website at
www.autorevenue.com

 

IMPORTANT: The information contained in this e-mail message is confidential and is intended only for the named addressee(s). If the reader of this e-mail message is not the intended recipient (or the individual responsible for the delivery of this e-mail message to the intended recipient), please be advised that any re-use, dissemination, distribution or copying of this e-mail message is prohibited.  If you have received this e-mail message in error, please reply to the sender that you have received this e-mail message in error and then delete it.  Thank you.

 

Re: row level locking

From
"Sibte Abbas"
Date:
On 7/20/07, Mark Steben <msteben@autorevenue.com> wrote:
>
> Hi listers,
>
> I have a table that is accessed by only one transaction.  It has 1500 rows
> each time that it processes
>
> This transaction.  As soon as this transaction ends, it waits 10 seconds and
> starts again.
>
> It has another maximum 1500 rows loaded. .
>
>
>
> My question is this:  I believe a RowExclusive level lock is acquired 1500
> times during each transaction.

What type operations does the transaction performs on the table?

An exclusive row level lock is acquired only:

i) when a row is being updated, deleted or marked for deletion (Implicit lock)
ii) when SELECT FOR UPDATE query is executed on the table (to acquire
a share lock you can use SELECT FOR SHARE) (Explicit lock)

>
> Is this true?  Is there a way to disable RowExclusive locks and reduce
> overhead since only this transaction

Consider running postgresql in single user mode. This would eradicate
all the Interprocess communication overheads.

>
> Accesses this table.   I know there are ways to specify locking levels in
> other DBMSs. To page or table levels.
>

Postgresql handles page level locks implicitly, users do not have any
control over them.

regards,
--
Sibte Abbas
EnterpriseDB http://www.enterprisedb.com

Re: row level locking

From
"Simon Riggs"
Date:
On Fri, 2007-07-20 at 14:19 -0400, Mark Steben wrote:

> We are currently at version 7.4.5.

You should upgrade if you want better performance and scalability.

Or maybe you should just use COPY?

--
  Simon Riggs
  EnterpriseDB  http://www.enterprisedb.com