Search Postgresql Archives

Re: max number of locks

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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
>>
>>
> 
> 


-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general



[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux