Why is AccessShareLock held until end of transaction?
-----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-----
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Joe Conway <mail@joeconway.com> writes:
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?
*Any* lock acquired by user command is held till end of transaction;
AccessShareLock isn't special.
In general, releasing early would increase the risk of undesirable
behaviors such as tables changing definition mid-transaction.
regards, tom lane
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 11 March 2014 03:41, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Joe Conway <mail@joeconway.com> writes:
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?*Any* lock acquired by user command is held till end of transaction;
AccessShareLock isn't special.In general, releasing early would increase the risk of undesirable
behaviors such as tables changing definition mid-transaction.
I thought "good question" at first, but the workaround is simple...
just don't use multi-step transactions, submit each request as a
separate transaction.
--
Simon Riggs http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Tue, Mar 11, 2014 at 10:56 PM, Simon Riggs <simon@2ndquadrant.com> wrote:
On 11 March 2014 03:41, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Joe Conway <mail@joeconway.com> writes:
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?*Any* lock acquired by user command is held till end of transaction;
AccessShareLock isn't special.In general, releasing early would increase the risk of undesirable
behaviors such as tables changing definition mid-transaction.I thought "good question" at first, but the workaround is simple...
just don't use multi-step transactions, submit each request as a
separate transaction.Wouldnt that tend to get inefficient?
Regards,
Atri
--
Regards,
Atri
*l'apprenant*
On 11 March 2014 17:29, Atri Sharma <atri.jiit@gmail.com> wrote:
On Tue, Mar 11, 2014 at 10:56 PM, Simon Riggs <simon@2ndquadrant.com> wrote:
On 11 March 2014 03:41, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Joe Conway <mail@joeconway.com> writes:
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?*Any* lock acquired by user command is held till end of transaction;
AccessShareLock isn't special.In general, releasing early would increase the risk of undesirable
behaviors such as tables changing definition mid-transaction.I thought "good question" at first, but the workaround is simple...
just don't use multi-step transactions, submit each request as a
separate transaction.Wouldnt that tend to get inefficient?
Please outline your alternate proposal so we can judge the comparative
efficiency.
--
Simon Riggs http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Tue, Mar 11, 2014 at 11:07 PM, Simon Riggs <simon@2ndquadrant.com> wrote:
On 11 March 2014 17:29, Atri Sharma <atri.jiit@gmail.com> wrote:
On Tue, Mar 11, 2014 at 10:56 PM, Simon Riggs <simon@2ndquadrant.com>
wrote:
On 11 March 2014 03:41, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Joe Conway <mail@joeconway.com> writes:
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?*Any* lock acquired by user command is held till end of transaction;
AccessShareLock isn't special.In general, releasing early would increase the risk of undesirable
behaviors such as tables changing definition mid-transaction.I thought "good question" at first, but the workaround is simple...
just don't use multi-step transactions, submit each request as a
separate transaction.Wouldnt that tend to get inefficient?
Please outline your alternate proposal so we can judge the comparative
efficiency.
I dont have an alternate proposal yet. I was just wondering if per step
transactions could lead to a drop in performance.
If that is the best way to go, I am all for it.
Regards,
Atri
--
Regards,
Atri
*l'apprenant*
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
On 03/11/2014 12:26 PM, Simon Riggs wrote:
On 11 March 2014 03:41, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Joe Conway <mail@joeconway.com> writes:
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?*Any* lock acquired by user command is held till end of
transaction; AccessShareLock isn't special.In general, releasing early would increase the risk of
undesirable behaviors such as tables changing definition
mid-transaction.I thought "good question" at first, but the workaround is
simple... just don't use multi-step transactions, submit each
request as a separate transaction.
Yeah, I told them that already. Unfortunately in this environment it
is not an option. It isn't a huge problem, but I did find it
surprising (as did the client) that a purely read-only transaction
could cause a deadlock with a concurrent CREATE TABLE.
It would seem that once the SELECT statement has finished we could
drop the AccessShareLock, but I guess that would open a can of works
that we don't want to contemplate.
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/
iQIcBAEBAgAGBQJTH0tlAAoJEDfy90M199hlxtUP/isxPT8ZRPf8X/vM3+vS4XR2
CTwNB292c9TLADSfi4lHFCXu8kqOpx29/9PJHUHrhTrCQE10USdC5uBN04u9si0a
SL5cmwtSeSn3YacgksNpPz0u9spGVdO4XqcMq9oh5gcsSeRf14NXIPAvUk7yRPTA
leVo7CArOfyld0QdRNw3JP50tAoHYJQynomkClg/9U+jYtk/aBpCSe/KL++d5esl
xt8iGZQ/wdZu+vWSdeaJMvGUYNOu4ts7wgtrqvLv9qLXDAiftfIC6NuakKY3WHY6
2OYz64Xd+wH0ZWEhYnSjkQR354RXSm0JQNos02nAjviDON6r6OJk3ny7Rw/mKbAw
ZR2Ze3EFYcnMeV9Rrg1DccDzqWK9lq7tHD++IfbQ/36xvOcxh4pQuZQt9erTJ4q1
l9MrHE8PA4mVDgcGlhcdzDl+/po/0ghy/HWgH72NjGpEX+fChh7Pad9ZCO5r33Du
V3EZXfdLwnokx/VRi0N61ZeBJCCKWSST3SrZKJk5ao7y8dQPIICryLJlM9sTxlXf
2wiQlybElpaqWxy+Ou3M7EYdPvGNOLHMCt8yUK5n+RFTEtljKNwy1E9NvJWWiVl9
SfA/6GXXsGlO0rQ723R1vPAFHtTo82ibQaiCNujVPu/2yecKl4MsdtaZApkilLqx
EPoWWGrs3cURvar6gmju
=DOcV
-----END PGP SIGNATURE-----
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers