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?