Re: [HACKERS] 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 wrote: >> Joe Conway 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
Re: [HACKERS] Why is AccessShareLock held until end of transaction?
On Tue, Mar 11, 2014 at 11:07 PM, Simon Riggs wrote: > On 11 March 2014 17:29, Atri Sharma wrote: > > > > > > > > On Tue, Mar 11, 2014 at 10:56 PM, Simon Riggs > wrote: > >> > >> On 11 March 2014 03:41, Tom Lane wrote: > >> > Joe Conway 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*
Re: [HACKERS] Why is AccessShareLock held until end of transaction?
On 11 March 2014 17:29, Atri Sharma wrote: > > > > On Tue, Mar 11, 2014 at 10:56 PM, Simon Riggs wrote: >> >> On 11 March 2014 03:41, Tom Lane wrote: >> > Joe Conway 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
Re: [HACKERS] Why is AccessShareLock held until end of transaction?
On Tue, Mar 11, 2014 at 10:56 PM, Simon Riggs wrote: > On 11 March 2014 03:41, Tom Lane wrote: > > Joe Conway 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*
Re: [HACKERS] Why is AccessShareLock held until end of transaction?
On 11 March 2014 03:41, Tom Lane wrote: > Joe Conway 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
Re: [HACKERS] Why is AccessShareLock held until end of transaction?
Joe Conway 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
[HACKERS] 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; - -- in session 2 create table t3 ( id int, t2id int references t2(id), t1id int references t1(id) ); - -- in session 1 select * from t2; 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
