Postgres Pro
Downloads
Home   >   mailing lists

Thread: Locking timeouts

Locking timeouts

From
"Robinson, Chad"
Date:
Hello all,

Sorry for what may be a "newbie" question, but I haven't used PostgreSQL
since the ODBC driver was called PostODBC, not pgsqlodbc, and I can't find
this answer in the list archives.

I have locking working in an ODBC application, written in VB using
Connection/Recordset objects, "select ... for update", BeginTrans, and
Update/CommitTrans to save the data). This correctly blocks other users from
editing (but not reading) the record being edited. That's what I want.

However, other users attempting to edit the record are blocked until the
record is released. Normally, a database will release the user with an error
noting that they were blocked by a record lock. PostgreSQL is not doing
this.

Can it be made to do so? This is a data entry application, so blocking other
users until the record is released by the editor is not acceptable.

Thanks!
Chad

Re: Locking timeouts

From
Hiroshi Inoue
Date:
"Robinson, Chad" wrote:
>
> Hello all,
>
> Sorry for what may be a "newbie" question, but I haven't used PostgreSQL
> since the ODBC driver was called PostODBC, not pgsqlodbc, and I can't find
> this answer in the list archives.
>
> I have locking working in an ODBC application, written in VB using
> Connection/Recordset objects, "select ... for update", BeginTrans, and
> Update/CommitTrans to save the data). This correctly blocks other users from
> editing (but not reading) the record being edited. That's what I want.
>
> However, other users attempting to edit the record are blocked until the
> record is released. Normally, a database will release the user with an error
> noting that they were blocked by a record lock. PostgreSQL is not doing
> this.

Yes but it's not an ODBC's problem.

> Can it be made to do so? This is a data entry application, so blocking other
> users until the record is released by the editor is not acceptable.

There seems no easy solution.
How about an optiomistic concurrency control not a pessimistic
one ?

regards,
Hiroshi Inoue

Re: Locking timeouts

From
"Robinson, Chad"
Date:
> > However, other users attempting to edit the record are blocked until the
> > record is released. Normally, a database will release the user with an
error
> > noting that they were blocked by a record lock. PostgreSQL is not doing
> > this.
>
> Yes but it's not an ODBC's problem.

Is there any way to make the ODBC driver give up if a query doesn't execute
in N seconds? Right now it just blocks. I can live with an utter failure,
but completely blocking the application is not a good way of doing things.
If there was a way to execute the query asynchronously and cancel it if it's
running too long, I would, but there isn't.

>
> > Can it be made to do so? This is a data entry application, so blocking
other
> > users until the record is released by the editor is not acceptable.
>
> There seems no easy solution.
> How about an optiomistic concurrency control not a pessimistic
> one ?

Optimistic concurrency won't work here; this is a screen data entry
application, not a script updating a large number of records or running in
batch mode. The goal is to prevent two users from editing the same record at
the same time. With optimistic locking the user could spend 10 minutes
entering data on a record, only to lose it all if the update fails because
somebody else changed the record. (Or that user's edits could overwrite the
other person's, but that's bad, too.)

Thus, pessimistic locking is very important for data entry apps, because
users know right away if they are trying to edit a locked record. Looks like
PostgreSQL+Psqlodbc is not suitable for this (yet). Shame. MySQL has its own
problems. DB2 is a pain, and Oracle is huge. That leaves SQL Server, which
does this well, but only runs under Windows. (grrrrrrrrrrrrrr)

Regards,
Chad