From: | Joe Conway <mail(at)joeconway(dot)com> |
---|---|
To: | pgsql-hackers(at)postgresql(dot)org |
Subject: | Why is AccessShareLock held until end of transaction? |
Date: | 2014-03-10 23:48:53 |
Message-ID: | 531E4F65.4070702@joeconway.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
I am probably missing something obvious, but why does the
AccessShareLock remain held on a table after a SELECT statement is
complete when in a transaction block? E.g.:
8<-------------------------
create table t1 ();
begin;
select * from t1;
select relation::regclass, locktype, mode
from pg_locks
where pid = pg_backend_pid();
relation | locktype | mode
- ----------+------------+-----------------
pg_locks | relation | AccessShareLock
t1 | relation | AccessShareLock
| virtualxid | ExclusiveLock
(3 rows)
8<-------------------------
The reason I ask is that I ran into a deadlock situation which was
caused by one session running two SELECT statements in a transaction,
while a second session attempted to create a new table with foreign
keys to two of the tables involved in the first session:
8<-------------------------
- -- at some earlier point
create table t1(id int primary key);
create table t2(id int primary key);
- -- in session 1
begin;
select * from t1;
<idle or race>
- -- in session 2
create table t3
(
id int,
t2id int references t2(id),
t1id int references t1(id)
);
<will block>
- -- in session 1
select * from t2;
<deadlock detected error>
8<-------------------------
Thoughts?
Thanks,
Joe
- --
Joe Conway
credativ LLC: http://www.credativ.us
Linux, PostgreSQL, and general Open Source
Training, Service, Consulting, & 24x7 Support
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.14 (GNU/Linux)
Comment: Using GnuPG with Thunderbird - http://www.enigmail.net/
iQIcBAEBAgAGBQJTHk9lAAoJEDfy90M199hlb2MP/1EtJwmsnsKvzhInXxKx1Jyb
uoKlq2a7v7GT79V7WstXRusuCdVN0f2C4HmvF9zIR108xUyxa7kK9IbRjEvfxVtd
oOZWRJrOzVKdUiBKqiA9xUwoKCxlNn2CuVbc3jzmyTB9fyzv59lGcDYcAjjwZoc0
rKboaeKVfoz3KRuKbhw+KfthtDWwdUeQ6pifttHm/vF4oAE1i9wyL4glV0x5Rmu+
ktkZItGpGjOh3lxJpCmON0rsx7K/SSSyZJ0pTpbjdDTKyl/3JkfgxLZXrF8AlOm0
L6XrMx4+yvjnN68NMTgy3talUU4hW5wTSebNihe6sw5YndkkLInjLwzfrTsYxtf0
cgYZ9g8PUI2MkePWJTgtkEqT3LE9PTMGXmD+NFL8E+rVbpzklXB8du0oKJRorC6x
0hzJSfZmOYCU8LDwagzPRXH9fncNT3oPxDcFMSUkWxQ3ha0TNMa9DKiPSxkJskSb
YVpIObda1b/JW9cT4LrvlNxVW0uk9TfiQpbXRcZTXEyCGYikHfm2Js1gwtcmL/LY
HiSXRadoT3n9890FzbRO3Mk3YRvz7VQyetOHtOjD8fRx5s7azoZHPNnNucgR5fVx
laAEBwY7wXppMbnmM7hAb6RYP/dV4yXoF4SVcnRMc2sm0sgOZkTT/2Muo6fHAW6E
SCEpW0nREbho3qaxPb+J
=io9e
-----END PGP SIGNATURE-----
From | Date | Subject | |
---|---|---|---|
Next Message | Andrew Dunstan | 2014-03-11 00:12:15 | Re: jsonb and nested hstore |
Previous Message | Josh Berkus | 2014-03-10 23:28:01 | Re: Auto-tuning work_mem and maintenance_work_mem |