Re: max number of locks - Mailing list pgsql-general
| From | Fabio Pardi |
|---|---|
| Subject | Re: max number of locks |
| Date | |
| Msg-id | 559A5371.8010107@portavita.eu Whole thread Raw |
| In response to | Re: max number of locks (Adrian Klaver <adrian.klaver@aklaver.com>) |
| List | pgsql-general |
Hi Adrian, thanks for your reply, On 07/03/2015 02:52 PM, Adrian Klaver wrote: > On 07/03/2015 03:14 AM, Fabio Pardi wrote: >> Hi, >> >> >> while experimenting with number of locks, i found something I cannot >> understand. >> >> From what i can read in the documentation, at any one given time, a >> query can obtain a max number of locks given by >> >> max_locks_per_transaction * (max_connections + max_prepared_transactions) >> >> I then changed my db to use this settings: >> >> mydb=# show max_locks_per_transaction ; >> max_locks_per_transaction >> --------------------------- >> 20 >> (1 row) >> >> mydb=# show max_connections ; >> max_connections >> ----------------- >> 2 >> (1 row) >> >> mydb=# show max_prepared_transactions ; >> max_prepared_transactions >> --------------------------- >> 0 >> (1 row) >> >> so i expected to be able to acquire a maximum of 40 locks. > > On tables. from what i see, the locks are of type 'AccessShareLock' which should be on tables, if i understand well from what mentionedin the link: http://www.postgresql.org/docs/9.2/static/explicit-locking.html In that case, there should be no more than 40 locks in total, any time in total into pg_locks table. [ Else: how to find out the number of locked objects kept in the 'shared lock table', which follows the logic of: max_locks_per_transaction* (max_connections + max_prepared_transactions) ? is there a different query for that? ] I understand that a single query can held more locks than what is in max_locks_per_transaction (because there is the multiplier'max_connections'), that's why during my tests I m using max_connections=2. I paste here below the result of the query, which might help to clarify the situation: # select * from pg_locks ; locktype | database | relation | page | tuple | virtualxid | transactionid | classid | objid | objsubid | virtualtransaction| pid | mode | granted | fastpath ------------+----------+----------+------+-------+------------+---------------+---------+-------+----------+--------------------+------+-----------------+---------+---------- relation | 224236 | 229160 | | | | | | | | 2/9100 | 6282 | AccessShareLock | t | t relation | 224236 | 228957 | | | | | | | | 2/9100 | 6282 | AccessShareLock | t | t relation | 224236 | 227513 | | | | | | | | 2/9100 | 6282 | AccessShareLock | t | t relation | 224236 | 227298 | | | | | | | | 2/9100 | 6282 | AccessShareLock | t | t relation | 224236 | 227090 | | | | | | | | 2/9100 | 6282 | AccessShareLock | t | t relation | 224236 | 226013 | | | | | | | | 2/9100 | 6282 | AccessShareLock | t | t relation | 224236 | 226385 | | | | | | | | 2/9100 | 6282 | AccessShareLock | t | t relation | 224236 | 226296 | | | | | | | | 2/9100 | 6282 | AccessShareLock | t | t relation | 224236 | 229719 | | | | | | | | 2/9100 | 6282 | AccessShareLock | t | t relation | 224236 | 229646 | | | | | | | | 2/9100 | 6282 | AccessShareLock | t | t relation | 224236 | 229594 | | | | | | | | 2/9100 | 6282 | AccessShareLock | t | t relation | 224236 | 225815 | | | | | | | | 2/9100 | 6282 | AccessShareLock | t | t relation | 224236 | 225783 | | | | | | | | 2/9100 | 6282 | AccessShareLock | t | t relation | 224236 | 225756 | | | | | | | | 2/9100 | 6282 | AccessShareLock | t | t relation | 224236 | 225730 | | | | | | | | 2/9100 | 6282 | AccessShareLock | t | t relation | 224236 | 11069 | | | | | | | | 2/9100 | 6282 | AccessShareLock | t | t virtualxid | | | | | 2/9100 | | | | | 2/9100 | 6282 | ExclusiveLock | t | t relation | 224236 | 292464 | | | | | | | | 2/9100 | 6282 | AccessShareLock | t | f relation | 224236 | 291793 | | | | | | | | 2/9100 | 6282 | AccessShareLock | t | f relation | 224236 | 291797 | | | | | | | | 2/9100 | 6282 | AccessShareLock | t | f relation | 224236 | 292166 | | | | | | | | 2/9100 | 6282 | AccessShareLock | t | f relation | 224236 | 292382 | | | | | | | | 2/9100 | 6282 | AccessShareLock | t | f relation | 224236 | 229365 | | | | | | | | 2/9100 | 6282 | AccessShareLock | t | f relation | 224236 | 292078 | | | | | | | | 2/9100 | 6282 | AccessShareLock | t | f relation | 224236 | 292405 | | | | | | | | 2/9100 | 6282 | AccessShareLock | t | f relation | 224236 | 292067 | | | | | | | | 2/9100 | 6282 | AccessShareLock | t | f relation | 224236 | 292196 | | | | | | | | 2/9100 | 6282 | AccessShareLock | t | f relation | 224236 | 292427 | | | | | | | | 2/9100 | 6282 | AccessShareLock | t | f relation | 224236 | 292448 | | | | | | | | 2/9100 | 6282 | AccessShareLock | t | f relation | 224236 | 292456 | | | | | | | | 2/9100 | 6282 | AccessShareLock | t | f relation | 224236 | 291869 | | | | | | | | 2/9100 | 6282 | AccessShareLock | t | f relation | 224236 | 292393 | | | | | | | | 2/9100 | 6282 | AccessShareLock | t | f relation | 224236 | 292201 | | | | | | | | 2/9100 | 6282 | AccessShareLock | t | f relation | 224236 | 291792 | | | | | | | | 2/9100 | 6282 | AccessShareLock | t | f relation | 224236 | 292154 | | | | | | | | 2/9100 | 6282 | AccessShareLock | t | f relation | 224236 | 292442 | | | | | | | | 2/9100 | 6282 | AccessShareLock | t | f relation | 224236 | 292077 | | | | | | | | 2/9100 | 6282 | AccessShareLock | t | f relation | 224236 | 292215 | | | | | | | | 2/9100 | 6282 | AccessShareLock | t | f relation | 224236 | 292216 | | | | | | | | 2/9100 | 6282 | AccessShareLock | t | f relation | 224236 | 292218 | | | | | | | | 2/9100 | 6282 | AccessShareLock | t | f relation | 224236 | 292378 | | | | | | | | 2/9100 | 6282 | AccessShareLock | t | f relation | 224236 | 292423 | | | | | | | | 2/9100 | 6282 | AccessShareLock | t | f relation | 224236 | 292244 | | | | | | | | 2/9100 | 6282 | AccessShareLock | t | f relation | 224236 | 292194 | | | | | | | | 2/9100 | 6282 | AccessShareLock | t | f relation | 224236 | 292409 | | | | | | | | 2/9100 | 6282 | AccessShareLock | t | f relation | 224236 | 292145 | | | | | | | | 2/9100 | 6282 | AccessShareLock | t | f relation | 224236 | 292440 | | | | | | | | 2/9100 | 6282 | AccessShareLock | t | f relation | 224236 | 292444 | | | | | | | | 2/9100 | 6282 | AccessShareLock | t | f relation | 224236 | 292452 | | | | | | | | 2/9100 | 6282 | AccessShareLock | t | f relation | 224236 | 291791 | | | | | | | | 2/9100 | 6282 | AccessShareLock | t | f relation | 224236 | 291796 | | | | | | | | 2/9100 | 6282 | AccessShareLock | t | f relation | 224236 | 291872 | | | | | | | | 2/9100 | 6282 | AccessShareLock | t | f relation | 224236 | 292085 | | | | | | | | 2/9100 | 6282 | AccessShareLock | t | f relation | 224236 | 292175 | | | | | | | | 2/9100 | 6282 | AccessShareLock | t | f relation | 224236 | 292407 | | | | | | | | 2/9100 | 6282 | AccessShareLock | t | f relation | 224236 | 291875 | | | | | | | | 2/9100 | 6282 | AccessShareLock | t | f relation | 224236 | 292243 | | | | | | | | 2/9100 | 6282 | AccessShareLock | t | f relation | 224236 | 292432 | | | | | | | | 2/9100 | 6282 | AccessShareLock | t | f relation | 224236 | 292210 | | | | | | | | 2/9100 | 6282 | AccessShareLock | t | f relation | 224236 | 292425 | | | | | | | | 2/9100 | 6282 | AccessShareLock | t | f relation | 224236 | 292468 | | | | | | | | 2/9100 | 6282 | AccessShareLock | t | f relation | 224236 | 292324 | | | | | | | | 2/9100 | 6282 | AccessShareLock | t | f relation | 224236 | 291874 | | | | | | | | 2/9100 | 6282 | AccessShareLock | t | f relation | 224236 | 292220 | | | | | | | | 2/9100 | 6282 | AccessShareLock | t | f relation | 224236 | 292339 | | | | | | | | 2/9100 | 6282 | AccessShareLock | t | f relation | 224236 | 292337 | | | | | | | | 2/9100 | 6282 | AccessShareLock | t | f relation | 224236 | 292419 | | | | | | | | 2/9100 | 6282 | AccessShareLock | t | f relation | 224236 | 292162 | | | | | | | | 2/9100 | 6282 | AccessShareLock | t | f relation | 224236 | 292381 | | | | | | | | 2/9100 | 6282 | AccessShareLock | t | f relation | 224236 | 292232 | | | | | | | | 2/9100 | 6282 | AccessShareLock | t | f relation | 224236 | 292385 | | | | | | | | 2/9100 | 6282 | AccessShareLock | t | f relation | 224236 | 292058 | | | | | | | | 2/9100 | 6282 | AccessShareLock | t | f relation | 224236 | 292150 | | | | | | | | 2/9100 | 6282 | AccessShareLock | t | f relation | 224236 | 292203 | | | | | | | | 2/9100 | 6282 | AccessShareLock | t | f relation | 224236 | 292404 | | | | | | | | 2/9100 | 6282 | AccessShareLock | t | f relation | 224236 | 292471 | | | | | | | | 2/9100 | 6282 | AccessShareLock | t | f relation | 224236 | 292410 | | | | | | | | 2/9100 | 6282 | AccessShareLock | t | f relation | 224236 | 292142 | | | | | | | | 2/9100 | 6282 | AccessShareLock | t | f relation | 224236 | 291870 | | | | | | | | 2/9100 | 6282 | AccessShareLock | t | f relation | 224236 | 292164 | | | | | | | | 2/9100 | 6282 | AccessShareLock | t | f relation | 224236 | 292248 | | | | | | | | 2/9100 | 6282 | AccessShareLock | t | f relation | 224236 | 292335 | | | | | | | | 2/9100 | 6282 | AccessShareLock | t | f relation | 224236 | 292338 | | | | | | | | 2/9100 | 6282 | AccessShareLock | t | f relation | 224236 | 292334 | | | | | | | | 2/9100 | 6282 | AccessShareLock | t | f relation | 224236 | 292151 | | | | | | | | 2/9100 | 6282 | AccessShareLock | t | f relation | 224236 | 291873 | | | | | | | | 2/9100 | 6282 | AccessShareLock | t | f relation | 224236 | 292159 | | | | | | | | 2/9100 | 6282 | AccessShareLock | t | f relation | 224236 | 292185 | | | | | | | | 2/9100 | 6282 | AccessShareLock | t | f relation | 224236 | 292146 | | | | | | | | 2/9100 | 6282 | AccessShareLock | t | f relation | 224236 | 292430 | | | | | | | | 2/9100 | 6282 | AccessShareLock | t | f relation | 224236 | 292149 | | | | | | | | 2/9100 | 6282 | AccessShareLock | t | f relation | 224236 | 292165 | | | | | | | | 2/9100 | 6282 | AccessShareLock | t | f relation | 224236 | 292072 | | | | | | | | 2/9100 | 6282 | AccessShareLock | t | f relation | 224236 | 292370 | | | | | | | | 2/9100 | 6282 | AccessShareLock | t | f relation | 224236 | 292386 | | | | | | | | 2/9100 | 6282 | AccessShareLock | t | f relation | 224236 | 292069 | | | | | | | | 2/9100 | 6282 | AccessShareLock | t | f relation | 224236 | 292422 | | | | | | | | 2/9100 | 6282 | AccessShareLock | t | f relation | 224236 | 292399 | | | | | | | | 2/9100 | 6282 | AccessShareLock | t | f relation | 224236 | 292079 | | | | | | | | 2/9100 | 6282 | AccessShareLock | t | f relation | 224236 | 292241 | | | | | | | | 2/9100 | 6282 | AccessShareLock | t | f relation | 224236 | 292199 | | | | | | | | 2/9100 | 6282 | AccessShareLock | t | f relation | 224236 | 292450 | | | | | | | | 2/9100 | 6282 | AccessShareLock | t | f relation | 224236 | 291784 | | | | | | | | 2/9100 | 6282 | AccessShareLock | t | f relation | 224236 | 291798 | | | | | | | | 2/9100 | 6282 | AccessShareLock | t | f relation | 224236 | 292065 | | | | | | | | 2/9100 | 6282 | AccessShareLock | t | f relation | 224236 | 292155 | | | | | | | | 2/9100 | 6282 | AccessShareLock | t | f relation | 224236 | 291861 | | | | | | | | 2/9100 | 6282 | AccessShareLock | t | f relation | 224236 | 292173 | | | | | | | | 2/9100 | 6282 | AccessShareLock | t | f relation | 224236 | 292075 | | | | | | | | 2/9100 | 6282 | AccessShareLock | t | f relation | 224236 | 292461 | | | | | | | | 2/9100 | 6282 | AccessShareLock | t | f relation | 224236 | 292384 | | | | | | | | 2/9100 | 6282 | AccessShareLock | t | f relation | 224236 | 292413 | | | | | | | | 2/9100 | 6282 | AccessShareLock | t | f relation | 224236 | 292170 | | | | | | | | 2/9100 | 6282 | AccessShareLock | t | f relation | 224236 | 292212 | | | | | | | | 2/9100 | 6282 | AccessShareLock | t | f relation | 224236 | 292236 | | | | | | | | 2/9100 | 6282 | AccessShareLock | t | f relation | 224236 | 292156 | | | | | | | | 2/9100 | 6282 | AccessShareLock | t | f relation | 224236 | 292247 | | | | | | | | 2/9100 | 6282 | AccessShareLock | t | f relation | 224236 | 292453 | | | | | | | | 2/9100 | 6282 | AccessShareLock | t | f relation | 224236 | 292068 | | | | | | | | 2/9100 | 6282 | AccessShareLock | t | f relation | 224236 | 292070 | | | | | | | | 2/9100 | 6282 | AccessShareLock | t | f relation | 224236 | 292178 | | | | | | | | 2/9100 | 6282 | AccessShareLock | t | f relation | 224236 | 292189 | | | | | | | | 2/9100 | 6282 | AccessShareLock | t | f relation | 224236 | 292467 | | | | | | | | 2/9100 | 6282 | AccessShareLock | t | f relation | 224236 | 292475 | | | | | | | | 2/9100 | 6282 | AccessShareLock | t | f relation | 224236 | 292152 | | | | | | | | 2/9100 | 6282 | AccessShareLock | t | f relation | 224236 | 292071 | | | | | | | | 2/9100 | 6282 | AccessShareLock | t | f relation | 224236 | 292160 | | | | | | | | 2/9100 | 6282 | AccessShareLock | t | f relation | 224236 | 292169 | | | | | | | | 2/9100 | 6282 | AccessShareLock | t | f relation | 224236 | 292332 | | | | | | | | 2/9100 | 6282 | AccessShareLock | t | f relation | 224236 | 292153 | | | | | | | | 2/9100 | 6282 | AccessShareLock | t | f relation | 224236 | 292157 | | | | | | | | 2/9100 | 6282 | AccessShareLock | t | f relation | 224236 | 292074 | | | | | | | | 2/9100 | 6282 | AccessShareLock | t | f Regards, Fabio To continue the docs from where you left off above: > > http://www.postgresql.org/docs/9.4/static/runtime-config-locks.html > > "The shared lock table tracks locks on max_locks_per_transaction * (max_connections + max_prepared_transactions) objects(e.g., tables); hence, no more than this many distinct objects can be locked at any one time. This parameter controlsthe average number of object locks allocated for each transaction; individual transactions can lock more objectsas long as the locks of all transactions fit in the lock table. This is not the number of rows that can be locked;that value is unlimited. ..." > > >> >> >> Then: >> >> >> mydb=# begin transaction ; >> BEGIN >> portavita=# SELECT 1 FROM root.ac; >> ?column? >> ---------- >> (0 rows) >> >> mydb=# select count(*) from pg_locks ; >> count >> ------- >> 132 >> (1 row) >> >> >> Why can I acquire 132 locks while the expected number is 40? What am I >> doing wrong? > > Take a look here: > > http://www.postgresql.org/docs/9.4/interactive/view-pg-locks.html > > and see whet the locks are actually being held on. > >> >> I m running Postgres 9.2.6 >> >> >> >> Thanks for your time, >> >> >> >> Fabio >> >> > >
pgsql-general by date: