Proper handling of a manually created lock database table for concurrency issues while using a framework for the db connection (aka how in the hell did this idea come to your tiny mind?)

(play this track while reading)



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 if when two or three request are simultaneous? HINT: you learn a lot of new curses from customers that got the same number on two different fiscal documents (that is against the law, at least in my country).

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.