Thread: max number of locks

max number of locks

From
Fabio Pardi
Date:
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.


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?

I m running Postgres 9.2.6



Thanks for your time,



Fabio

 

Re: max number of locks

From
Adrian Klaver
Date:
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. 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
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com


Re: max number of locks

From
Fabio Pardi
Date:
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
>>
>>
>
>