MySQL transactions: reads while writing -

i'm implementing paypal payments standard in website i'm working on. question not related paypal, want present question through real problem.

paypal can notify server payment in 2 ways:

1. paypal ipn - after each payment paypal sends (server-to-server) notification url (choose you) transaction details.
2. paypal pdt - after payment (if set in pp account) paypal redirect user site, passing transaction id in url, can query paypal transaction, details.

the problem is, can't sure 1 happens first:

• will server notified ipn
• will user redirected site

whichever happening first, want sure i'm not processing transaction twice. so, in both cases, query db against transaction id coming paypal (and payment status actually..but doesn't matter now) see if saved , processed transaction. if not, process it, , save transaction id other transaction details database.

question

what happens if start processing first request (let pdt..so user redirected site, server wasn't notified ipn yet), before save transaction database, second (the ipn) request arrives , try process transaction too, because doesn't find in db.

i love make sure while i'm writing transaction database, no other queries can read table, looking given transaction id.

i'm using innodb, , don't want lock whole table, time of write. can solved transactions, have lock "manually" row? i'm confused, , hope more experienced mysql developers can making clear me , solving problem.

native database locks useless in web context, particularly in situations this. mysql connections not done in persistent way - when script shuts down, mysql connection , locks released , in-flight transactions rolled back.

e.g.

situation 1: direct user paypal's site complete purchase

when head off paypal, script sent on http redirect terminate , shuts down. locks/transactions released/rolled back, , come "virgin" status far db concerned. record no longer locked.

situation 2: paypal server-to-server response. done via separate http connection, utterly distinct connection established user server. means locks establish in yourserver<->user connection distinct paypal<->yourserver session, , paypal response encounter locked tables. , of course, there's no way of predicting when paypal response comes in. if network gods smile upon , paypal's not swamped, response , possibly while user<->you connection still open. if things slow , response delayed, response may encounter unlocked tables/rows because user<->server session has completed.

you could use persistent mysql connections, open whole other world of pain. e.g. consider case script has bug gets triggered halfway through processing. connection, transaction work, set locks... , script dies. because mysql connection persistent, mysql not see client script has died, , keep transactions/locks in-flight. connection still sitting there, in shared pool waiting session pick up. when invariably is, new script has no idea it's gotten old "stale" connection. it'll step middle of mess of locks , transactions has no idea exists. can deadlock situation this, because buggy scripts have dumped garbage on system , other scripts cannot cope garbage.

basically, unless implement own locking mechanism on top of system, e.g. update users set locked=1 id=xxx, cannot use native db locking mechanisms in web context except in 1-shot-per-script contexts. locks should never attempted on multiple independent requests.