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?
