Search Postgresql Archives

Re: implementing a read lock

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



snacktime <snacktime@xxxxxxxxx> writes:

> I have an application that processes credit card transactions,and
> contains a table called authorizations.  The authorizations table
> contains information returned by the bank necessary to capture the
> transaction.   Nothing should block the application from inserting new
> rows into the authorizations table.  When the authorizations are
> captured, one or more rows will be fetched, captured, and if
> successful the rows will be deleted.  No updates are done on the
> table, only inserts or deletes.   Naturally I want to prevent
> different instances of the same application  from trying to select the
> same rows to capture, resulting in duplicate charges.  I can't lock
> the whole table because new authorizations are constantly coming in.
> Is creating a separate table that I use just as a lock table the best
> approach?

I'm not quite sure why SELECT FOR UPDATE wouldn't work for you.  The
capturing process would SELECT candidate transactions (where the
status is 'NEW', say) FOR UPDATE, mark their status as 'PROCESSING',
then COMMIT and try to process each transaction.  Once the status is
known it can mark the row as 'DONE' or 'FAILED'.

SELECT FOR UPDATE doesn't block inserts of new rows.

-Doug


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux