I remember I screwed this one really bad (I feel I'm good at it)
it was like 5-6 years ago, I've been studying/part-time working for a year and I was just starting to get into the game: I mean.. I was able to do almost everything my boss asked me, and deliver it on time (no payrise ever seen, in case you're wondering).
one day I was working on fiscal document sequencers: you call the object to get a new valid number for that specific sequence (e.g. invoices) and the business logic handles all the "fiscal" problems (no holes in the sequence, proper ordering), giving you a nice ready-to-use string while you get that coffee you were dreaming of.
something like
the problem is: what happens
calm down I got this: we will do a basic lock table so we can [insert explanation of what a lock table is or google it, damn it].
Hooray! You're awesome! You're da man!
have you noticed in the code snippet that little class I calld DBHandler? well now I even can't remember the name but it was a fantastic internally developed framework that managed (among other stuff, all badly) the db connection and stuff, including transactions.
so I changed the code this way:
it was obviously a little more complex than that (I locked a datetime and not a bool, so I could decide if proceed anyway if the lock record was too old) but the splendid result was the same: the very same behavior as before with duplicate numbering and shit
I even went to the other room where helpdesk people was and i let tem push the button in the app at the same time after my countdown (I swear I did this for real).
i spent a full day thinking where the problem was, and it was really really really easy
if you create a lock table into a transaction and keep using the same connection (because you've a framework) well..
1. you(me)'re really dumb because...
2. transactions don't write any fucking thing before you commit them! and...
3. if you are in the middle of a transactional method (like my example) within a framework that manages connections (generally they keep only one open) the easiest, dirtier and so more satisfying way to accomplish the job is to create a second connection by hand and query by it
like this:
then i walked again in the hotline people room and triumphantly made all of them push on the button: this time the lock table was working properly.
another mission accomplished, another life saved. but no payrise this time either, of course.