Re: max number of locks

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(dot)klaver(at)aklaver(dot)com