Re: Why is AccessShareLock held until end of transaction?

-----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(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> Joe Conway <mail(at)joeconway(dot)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-----