Postgres Pro
Downloads
Home   >   mailing lists

Thread: ZEOS or PGDAC - How to lock a resource?

ZEOS or PGDAC - How to lock a resource?

From
durumdara
Date:
Hi!

We will porting an application to PGSQL from some table based app (BDE
like).

The older application used a special technic of the driver: if a record
edited, some exclusive (over transaction), "forever living" lock put on it.
On exit, cancel, or post this lock removed.

We used this to lock the main resource from concurrent edits.

For example:
A product (a Car) have many properties and sublists (details), like
color, transport date, elements (what we need to build into car: wheel,
etc), bill informations, subtransport times, etc.
Because ALL of them is the product, we must protect it with "Edit lock"
on open.
The subinformations are easily editable, and postable (there is autocommit).

Now I search for some technics in PGSQL.
As I read, the locks are transaction depended, because they are vanishes
on rollback/commit.

But we want to save the subelements on editing (one by one), not on
saving the main.
This meaning that we break the transaction with commit - ergo the lock
vanish.

For example:
Car Edit:
- Lock This car
- Edit color
- Open product elements tab
- Add two new elements
- Save them (ApplyUpdates, Commit)
- Add a bill date
- Save it (Apply, Commit)
- Post car record (Apply, Commit)
- Release resource
- Close Form

I read the help, but I saw only transaction-dependent locks.

Zeos or PGDAC is not like IBX/IBO (Firebird), so they don't have
Transaction Component, I can use only one transaction by connection.

How can I do a lock mechanism that:
- Session based
- No limit on how many I used
- Linked to a Row, or a Resource Name
- I can test to is it exists or not

Like Mutex in Windows, but in PGSQL...

Thanks for your help:
    dd

Re: ZEOS or PGDAC - How to lock a resource?

From
Merlin Moncure
Date:
2011/5/4 durumdara <durumdara@gmail.com>:
> Hi!
>
> We will porting an application to PGSQL from some table based app (BDE
> like).
>
> The older application used a special technic of the driver: if a record
> edited, some exclusive (over transaction), "forever living" lock put on it.
> On exit, cancel, or post this lock removed.
>
> We used this to lock the main resource from concurrent edits.
>
> For example:
> A product (a Car) have many properties and sublists (details), like color,
> transport date, elements (what we need to build into car: wheel, etc), bill
> informations, subtransport times, etc.
> Because ALL of them is the product, we must protect it with "Edit lock" on
> open.
> The subinformations are easily editable, and postable (there is autocommit).
>
> Now I search for some technics in PGSQL.
> As I read, the locks are transaction depended, because they are vanishes on
> rollback/commit.
>
> But we want to save the subelements on editing (one by one), not on saving
> the main.
> This meaning that we break the transaction with commit - ergo the lock
> vanish.
>
> For example:
> Car Edit:
> - Lock This car
> - Edit color
> - Open product elements tab
> - Add two new elements
> - Save them (ApplyUpdates, Commit)
> - Add a bill date
> - Save it (Apply, Commit)
> - Post car record (Apply, Commit)
> - Release resource
> - Close Form
>
> I read the help, but I saw only transaction-dependent locks.
>
> Zeos or PGDAC is not like IBX/IBO (Firebird), so they don't have Transaction
> Component, I can use only one transaction by connection.
>
> How can I do a lock mechanism that:
> - Session based
> - No limit on how many I used
> - Linked to a Row, or a Resource Name
> - I can test to is it exists or not
>
> Like Mutex in Windows, but in PGSQL...

advisory locks
http://www.postgresql.org/docs/current/interactive/functions-admin.html#FUNCTIONS-ADVISORY-LOCKS

(aside: borland delphi is increasingly obsolete in the scheme of
things, but zeos is one of the best postgres drivers ever written!)


merlin

Re: ZEOS or PGDAC - How to lock a resource?

From
Mark Morgan Lloyd
Date:
Merlin Moncure wrote:
> 2011/5/4 durumdara <durumdara@gmail.com>:
>> Hi!
>>
>> We will porting an application to PGSQL from some table based app (BDE
>> like).
>>
>> The older application used a special technic of the driver: if a record
>> edited, some exclusive (over transaction), "forever living" lock put on it.
>> On exit, cancel, or post this lock removed.
>>
>> We used this to lock the main resource from concurrent edits.

> advisory locks
> http://www.postgresql.org/docs/current/interactive/functions-admin.html#FUNCTIONS-ADVISORY-LOCKS
>
> (aside: borland delphi is increasingly obsolete in the scheme of
> things, but zeos is one of the best postgres drivers ever written!)

I don't use Zeos, but a few months ago I was using listen/notify via
FPC's standard classes to good effect so I'd be surprised if there were
any problems.

--
Mark Morgan Lloyd
markMLl .AT. telemetry.co .DOT. uk

[Opinions above are the author's, not those of his employers or colleagues]