Re: row level locking

On 7/20/07, Mark Steben <msteben(at)autorevenue(dot)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