Re: Logical locking beyond pg_advisory - Mailing list pgsql-general
Would be nice if you explain more about what kind of problem you want to solve.
There are two main questions "to solve"
a) generally speaking, I like to get some form of "exclusive access" to the row before updating or deleting. None of the optimistic / pesimistic automatic variants of concurrency management glad me. Nor, of course, the "versioning" variants.
b) some of the tables I´m working on have a "number" column (not the PK) which sometimes come from material, external sources, but sometimes must be assigned by the system. This could be solved two main ways
b.1) Use a trigger to get the famous "max(n) + 1". At least in one of the cases, the number automatically generated must fall into different ranges conditioned by the value of another column, and the ranges vary between versions of the database.
b.2) "Lock the entire table", get programmatically the next number for the correct range, assign it and free the table lock.
Of course, and beforehand, all database manipulations are done thru applications.
Till yesterday, I was working with the "advisory" family. Having a bigint as the only "lock identifier" I was working with a hash of the table name XORed with the id of the row or zero for the entire table. (All my tables have an autosequential integer id as PK).
Even if I found a very robust hash algorithm for the table name, I cannot discard some collision once the id was xored. I tested five or six table names, along 20000 ids every one, without collision. But...
Of course, I need the "full table lock" for inserts. So, it´s a very separated concern with updates and deletions. But...
TIA
There are two main questions "to solve"
a) generally speaking, I like to get some form of "exclusive access" to the row before updating or deleting. None of the optimistic / pesimistic automatic variants of concurrency management glad me. Nor, of course, the "versioning" variants.
b) some of the tables I´m working on have a "number" column (not the PK) which sometimes come from material, external sources, but sometimes must be assigned by the system. This could be solved two main ways
b.1) Use a trigger to get the famous "max(n) + 1". At least in one of the cases, the number automatically generated must fall into different ranges conditioned by the value of another column, and the ranges vary between versions of the database.
b.2) "Lock the entire table", get programmatically the next number for the correct range, assign it and free the table lock.
Of course, and beforehand, all database manipulations are done thru applications.
Till yesterday, I was working with the "advisory" family. Having a bigint as the only "lock identifier" I was working with a hash of the table name XORed with the id of the row or zero for the entire table. (All my tables have an autosequential integer id as PK).
Even if I found a very robust hash algorithm for the table name, I cannot discard some collision once the id was xored. I tested five or six table names, along 20000 ids every one, without collision. But...
Of course, I need the "full table lock" for inserts. So, it´s a very separated concern with updates and deletions. But...
TIA
On 17/09/2018 03:19 , Fabrízio de Royes Mello wrote:
Em dom, 16 de set de 2018 às 17:53, marcelo <marcelo.nicolet@gmail.com> escreveu:
>
> I need a mechanism of "logical locking" more ductile than the pg_advisory family.
> I'm thinking of a table ("lock_table") that would be part of the database, with columns
> * tablename varchar - name of the table "locked"
> * rowid integer, - id of the row "locked"
> * ownerid varchar, - identifier of the "user" who acquired the lock
> * acquired timestamp - to be able to release "abandoned" locks after a certain time
>
> and a group of functions
> 1) lock_table (tablename varchar, ownerid varchar) bool - get to lock over the entire table, setting rowid to zero
> 2) unlock_table (tablename varchar, ownerid varchar) bool - unlock the table, if the owner is the recorded one
> 3) locked_table (tablename varchar, ownerid varchar) bool - ask if the table is locked by some user other than the ownerid argument
> 4) lock_row (tablename varchar, rowid integer, ownerid varchar) bool - similar to pg_try_advisory_lock
> 5) unlock_row (tablename varchar, rowid integer, ownerid varchar) bool - similar to pg_advisory_unlock
> 6) unlock_all (ownerid varchar) bool - unlock all locks owned by ownerid
>
> The timeout (default, maybe 15 minutes) is implicitly applied if the lock is taken by another user (there will be no notification).
> Redundant locks are not queued, they simply return true, may be after an update of the acquired column.
> Successful locks insert a new row, except the rare case of a timeout, which becomes an update (ownerid and acquired)
> Unlock operations deletes the corresponding row
>
> My question is double
> a) What is the opinion on the project?
Would be nice if you explain more about what kind of problem you want to solve.> b) What are the consequences of the large number of inserts and deletionsThe first thing came to my mind with this approach is table bloat.> c) Performance. In fact, pg_advisory* implies a network roundtrip, but (I think) no table operations.
Yeap... no table operations.
Regards,
--
Fabrízio de Royes Mello Timbira - http://www.timbira.com.br/
PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento
pgsql-general by date: