Обсуждение: Misunderstanding transactions and locks
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
Вложения
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.
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
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
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