Postgres Pro
Downloads
Home   >   mailing lists

Thread: Misunderstanding transactions and locks

Misunderstanding transactions and locks

From
"Michael P. Soulier"
Date:
Hi,

I'm using Django with Postgres 8.3.9 on CentOS Linux.

I'm trying to figure out why locking isn't working as I'm expecting. I have a
an operation wrapped in a transaction where I explicitely grab an exclusive
lock on my table. When another process concurrently runs to do the same, it
should block on attempting to acquire the exclusive lock, no?

That is not what I am seeing. I see two processes both doing this

2010-06-16 12:14:31.909608500 LOG:  connection received: host=[local]
2010-06-16 12:14:31.909748500 DEBUG:  forked new backend, pid=30726 socket=6
2010-06-16 12:14:31.909784500 LOG:  connection authorized: user=tugdbuser
database=tugd
b
2010-06-16 12:14:31.911516500 LOG:  statement: SET DATESTYLE TO 'ISO'
2010-06-16 12:14:31.911630500 LOG:  statement: SHOW client_encoding
2010-06-16 12:14:31.911768500 LOG:  statement: SHOW
default_transaction_isolation
2010-06-16 12:14:31.911849500 LOG:  statement: SET client_encoding = 'UTF8'
2010-06-16 12:14:31.911972500 LOG:  statement: BEGIN; SET TRANSACTION
ISOLATION LEVEL R
EAD COMMITTED
2010-06-16 12:14:31.912028500 LOG:  statement: SET TIME ZONE
E'America/New_York'
2010-06-16 12:14:31.912373500 LOG:  statement: SELECT version()
...skipping...
2010-06-16 12:14:31.913008500 LOG:  statement: LOCK TABLE instances IN ROW
EXCLUSIVE MODE

The first lock does not seem to prevent the second from completing.

Am I misunderstanding how locks work in postgres?

Thanks,
Mike
--
Michael P. Soulier <michael_soulier@mitel.com>, 613-592-2122 x2522
"Any intelligent fool can make things bigger and more complex... It takes a
touch of genius - and a lot of courage to move in the opposite direction."
--Albert Einstein
Attachment

Re: Misunderstanding transactions and locks

From
Scott Marlowe
Date:
On Wed, Jun 16, 2010 at 10:21 AM, Michael P. Soulier
<michael_soulier@mitel.com> wrote:
> Hi,
>
> I'm using Django with Postgres 8.3.9 on CentOS Linux.
>
> I'm trying to figure out why locking isn't working as I'm expecting. I have a
> an operation wrapped in a transaction where I explicitely grab an exclusive
> lock on my table. When another process concurrently runs to do the same, it
> should block on attempting to acquire the exclusive lock, no?
>
> That is not what I am seeing. I see two processes both doing this
>
> 2010-06-16 12:14:31.909608500 LOG:  connection received: host=[local]
> 2010-06-16 12:14:31.909748500 DEBUG:  forked new backend, pid=30726 socket=6
> 2010-06-16 12:14:31.909784500 LOG:  connection authorized: user=tugdbuser
> database=tugd
> b
> 2010-06-16 12:14:31.911516500 LOG:  statement: SET DATESTYLE TO 'ISO'
> 2010-06-16 12:14:31.911630500 LOG:  statement: SHOW client_encoding
> 2010-06-16 12:14:31.911768500 LOG:  statement: SHOW
> default_transaction_isolation
> 2010-06-16 12:14:31.911849500 LOG:  statement: SET client_encoding = 'UTF8'
> 2010-06-16 12:14:31.911972500 LOG:  statement: BEGIN; SET TRANSACTION
> ISOLATION LEVEL R
> EAD COMMITTED
> 2010-06-16 12:14:31.912028500 LOG:  statement: SET TIME ZONE
> E'America/New_York'
> 2010-06-16 12:14:31.912373500 LOG:  statement: SELECT version()
> ...skipping...
> 2010-06-16 12:14:31.913008500 LOG:  statement: LOCK TABLE instances IN ROW
> EXCLUSIVE MODE
>
> The first lock does not seem to prevent the second from completing.

I only see one lock here.

Re: Misunderstanding transactions and locks

From
Alvaro Herrera
Date:
Excerpts from Michael P. Soulier's message of mié jun 16 12:21:16 -0400 2010:
> Hi,
>
> I'm using Django with Postgres 8.3.9 on CentOS Linux.
>
> I'm trying to figure out why locking isn't working as I'm expecting. I have a
> an operation wrapped in a transaction where I explicitely grab an exclusive
> lock on my table. When another process concurrently runs to do the same, it
> should block on attempting to acquire the exclusive lock, no?

> 2010-06-16 12:14:31.913008500 LOG:  statement: LOCK TABLE instances IN ROW
> EXCLUSIVE MODE

"row exclusive mode" does not block itself.

--
Álvaro Herrera <alvherre@commandprompt.com>
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

Re: Misunderstanding transactions and locks

From
Tom Lane
Date:
Alvaro Herrera <alvherre@commandprompt.com> writes:
> Excerpts from Michael P. Soulier's message of mié jun 16 12:21:16 -0400 2010:
>> I'm trying to figure out why locking isn't working as I'm expecting. I have a
>> an operation wrapped in a transaction where I explicitely grab an exclusive
>> lock on my table. When another process concurrently runs to do the same, it
>> should block on attempting to acquire the exclusive lock, no?

>> 2010-06-16 12:14:31.913008500 LOG:  statement: LOCK TABLE instances IN ROW
>> EXCLUSIVE MODE

> "row exclusive mode" does not block itself.

Specifically, see the table in
http://www.postgresql.org/docs/8.3/static/explicit-locking.html#LOCKING-TABLES

Exclusive lock modes are those that conflict with themselves; to wit,
SHARE ROW EXCLUSIVE mode or higher.  The mode names are, um, historical.

            regards, tom lane

Re: Misunderstanding transactions and locks

From
"Michael P. Soulier"
Date:
On 16/06/10 Tom Lane did say:

> > "row exclusive mode" does not block itself.
>
> Specifically, see the table in
> http://www.postgresql.org/docs/8.3/static/explicit-locking.html#LOCKING-TABLES
>
> Exclusive lock modes are those that conflict with themselves; to wit,
> SHARE ROW EXCLUSIVE mode or higher.  The mode names are, um, historical.

That explains much.

Thanks,
Mike
--
Michael P. Soulier <michael_soulier@mitel.com>, 613-592-2122 x2522
"Any intelligent fool can make things bigger and more complex... It takes a
touch of genius - and a lot of courage to move in the opposite direction."
--Albert Einstein