Re: max number of locks

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 mentioned in 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 controls the average number of object locks allocated for each transaction; individual transactions can lock more objects as 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
>>
>>
>
>