MySQL Forums
Forum List  »  Newbie

Re: Help with locks
Posted by: Rick James
Date: March 19, 2009 11:40PM

Plan 4: Use an auto_increment as part of the unique id. The auto_increment can be the key; the rest of the stuff (date, acct num, etc) can be there for warm fuzzies.

Plan 5: Your plan 2, but simply deal with the occasional non-completed transactions. So what if you lose a few numbers? OK, maybe it is a big deal -- if someone audits your records for holes. In that case build a record that is like "This page intentionally left blank".

Are you using InnoDB? I hope so. If so, you probably don't need to LOCK TABLE for any of the approaches. Do not let BEGIN...COMMIT last longer than the default lock timeout of 50 seconds. Preferably don't let it be more than 1 second. And be ready to handle errors on all statements, including COMMIT, in your transactions.

And remember, you can have a network or power failure at any moment. If you design your transactions correctly, InnoDB can recover from power failures. Network failures need care to record things before and after a transmission. Then, at worst, you know that something is in limbo -- either aborted or competed. (As opposed to having no record of it.)

Options: ReplyQuote


Subject
Written By
Posted
March 19, 2009 09:29AM
March 19, 2009 11:53AM
Re: Help with locks
March 19, 2009 11:40PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.