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

	DBHandler.Transaction.Begin();
	Sequencer invoiceSeq = logic.GetSequencerByName("InvoiceSequencer");
	string myNumber = invoiceSeq.GetNextNumber();
	DBHandler.Transaction.Commit();

view raw Sequencer.cs hosted with ❤ by GitHub

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:

	DBHandler.Transaction.Begin();
	bool isFunctionLocked = logic.CheckIfSequencerIsLocked("InvoiceSequencer");
	while (isFunctionLocked)
	{
	//It is locked, we wait and hope we never die here
	Thread.Sleep(500);
	}
	//First we lock the sequencer so no other can use it
	logic.LockSequencer("InvoiceSequencer");
	Sequencer invoiceSeq = logic.GetSequencerByName("InvoiceSequencer");
	string myNumber = invoiceSeq.GetNextNumber();
	DBHandler.Transaction.Commit();
	//Gotta release if or it will be locked forever!
	logic.UnlockSequencer("InvoiceSequencer");

view raw SequencerWithWrongConcurrency.cs hosted with ❤ by GitHub

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:

	DBHandler.Transaction.Begin();
	Connection newConn = logic.OpenNewConnection(params.connString);
	//The check must be performed on the by-hand
	//created new connection, not on the
	//framework managed one!
	bool isFunctionLocked = logic.CheckIfSequencerIsLocked(newConn, "InvoiceSequencer");
	while (isFunctionLocked)
	{
	//It is locked, we wait and hope we never die here
	Thread.Sleep(500);
	}
	//First we lock the sequencer so no other can use it
	//Again we do this on the separate connection
	logic.LockSequencer(newConn, "InvoiceSequencer");
	Sequencer invoiceSeq = logic.GetSequencerByName("InvoiceSequencer");
	string myNumber = invoiceSeq.GetNextNumber();
	DBHandler.Transaction.Commit();
	//Gotta release if or it will be locked forever!
	//Time to say goodbye to our connection
	logic.UnlockSequencer(newConn, "InvoiceSequencer");
	newConn.Dispose();

view raw SequencerWithSeparateConnectionc.cs hosted with ❤ by GitHub

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.

Linq with the MVC pattern: a personal point of view (aka a lot of bullshit)

(play this track while reading)


man I love Linq

really, it saves me tons of time and I love it. I use it for everything, in both its syntax: (the sql-like and the lambda-like one).

plus, since when I found custom providers (info here), I am an happy user. rainbows and honey and unicorns everywhere.

until I needed to develop my first serious web app and I choose to model my data layer with linq. 


figure out how to do the simplest things has really been a pain in the ass. but now I'm gonna tell you what beware and how avoid pitfalls, so hey, unicorns and rainbow for you.

the most important thing you must understand is about the datacontext and your instantiated object: in order to have the datacontext understand the changes you made to an object it must be obtained through the same datacontext instance you're calling the SubmitChanges() method.


what does this mean?

simple:

	DataContext dc = new DataContext();
	Player kobe = dc.Players.SingleOrDefault(x=>x.Id = kobeId);
	kobe.LastGame = DateTime.Today;
	dc.SubmitChanges();

view raw EditWithAttachedEntity.cs hosted with ❤ by GitHub

will work like a charm but

	DataContext dc = new DataContext();
	//this method retrieves the same object but not
	//through the current scope DataContext
	Player kobe = businessLogic.GetPlayerById(kobeId);
	kobe.LastGame = DateTime.Today;
	dc.SubmitChanges();

view raw EditWithANotAttachedEntiry.cs hosted with ❤ by GitHub

will result in no changes!

once you understood the basic concept "DataContext uber alles" everything is perfectly logic and linear: if the object you're manipulating has not been originated by the datacontext you're submitting changes there's no way that the system can track them (and as result no changes will be propagated to the database or whatever your linq provier is targeting).

the second main concept you must bear in mind: Attach an entity is a plague you must avoid. it will cause you more problems than ever, trust me.

I used to rely on the Attach method to update entities: there's a particular Attach() overload that allows you to pass as argument the modified instance and the original one so the datacontext can infere the changes and rainbows and unicorns and ... well you get that.

something like this:

	public Player UpdatePlayer(Player p)
	{
	DataContext dc = new DataContext();
	Player originalPlayer = dc.Players.SingleOrDefault(x=>x.Id == p.Id);
	if (originalPlayer != null)
	{
	dc.Player.Attach(p, originalPlayer);
	dc.SubmitChanges();
	}

view raw UpdateWithOverload.cs hosted with ❤ by GitHub

it works!..... until you call the method UpdatePlayer twice in the same scope.. then it's all about complaining that the Entity is already in use and cannot be modified and shit.

well.. screw you, I went with the old school manual assignment and it worked since then like a foxconn underpaid worker:

	public Player UpdatePlayer(Player p)
	{
	DataContext dc = new DataContext();
	Player originalPlayer = dc.Players.SingleOrDefault(x=>x.Id == p.Id);
	if (originalPlayer != null)
	{
	originalPlayer.Name = p.Name;
	originalPlayer.Surname = p.Surname;
	originalPlayer.Height = p.Height;
	//all the other fields...
	[...]
	dc.SubmitChanges();
	}

view raw UpdateWithManualAssignment.cs hosted with ❤ by GitHub

well, thank you linq for all the good lies on how life is beautiful with the automation: old school sometimes wins.

My (dev) Mistakes

(play this track while reading)


too many times in my life as a developer (and not only as a developer, actually) I wished I spent just a little more time to get better information on the internet before taking a decision. I'm talking about any damn decision, from the "use a tinyint or a normal int for this field" to "which component should I use for this project": the time spent in research is well worth it. but you learn this later.

too many times the fate rightly kicked me back in the balls because I didn't do it.

in this blog I will post the mistakes I made, I make and I'll make until the Great Developer will call me to the Matrix, hoping that someone else, somewhere in the world can avoid them.

if you read any of the mistake topics and have an opinion about that, please feel free to debate it (below-belt are encouraged), so that we all can learn from the Universe knowledge.

but we know we'll always make the same mistakes...