Notes on Nested Transactions

Last week I was approached by one of our developers, with a troublesome piece of code.  Essentially, the T-SQL he was attempting to run was inside a transaction, and using an INTO clause to put the result set into a target table.  Unfortunately, it wasn't working as anticipated - he was able to open the transaction and run the code, but when it came to checking the row count output he noted 0 rows were inserted into the table before he committed - and he was curious why.

My immediate suspicion was that because the transaction wasn't committed, the rows weren't being inserted, so an alternative method of getting the row count would be to print the value of the @@ROWCOUNT variable at the appropriate time.  However, in the course of testing this, I made a bit of a mistake - I decided to try a nested transaction, to get a better understanding of the problem.

So here's some example code of a straightforward use of the INTO clause.

Working as expected.  So now let's open up a transaction and do the same.  

We have ten rows reported.  Let's query the table and see if the rows actually exist.

Querying the table returns the columns.  Yet the transaction remains open.  This behaviour is not what I was seeing from the developer's code, so I decided to play with it a little and see if we could force this expected behaviour by using a nested transaction - that is, to introduce ANOTHER transaction that deals explicitly with this insert, then leave the outer transaction open so all other behaviours could be rolled back.  So here's what I did.

So far, so good.  The COMMIT was hit, and the transaction committed, and using DBCC OPENTRANS confirms this (not shown).  So now I needed to test that this behaviour would work if the ROLLBACK was hit instead of the COMMIT.  So I modified the code as follows:

This has worked, right?  The inner transaction would have rolled back, leaving one open transaction?  Right?  Wrong.

In fact BOTH transactions were rolled back.  It didn't selectively roll back one transaction, even though the COMMIT only commits one transaction.  And here's the lesson.  When using ROLLBACK, all transactions are rolled back.  This is actually documented in BOL, and I kicked myself for not checking before messing with it.  Here's the quote from BOL:

"When nesting transactions, this [ROLLBACK] statement rolls back all inner transactions to the outermost BEGIN TRANSACTION statement." 

Here's an interesting thing.  I could have attempted to avoid this by using a transaction name, so e.g. BEGIN TRAN b and ROLLBACK TRAN b.  It would have errored though - it appears (though playing with this) that transaction b is subsumed by transaction a - @@TRANCOUNT is 2 as expected, but I cannot roll back b and DBCC OPENTRAN only shows the oldest active transaction, a:

So why was this such a bad thing?  Well, in the code above, -- some other stuff here was actually a couple of DELETE statements - that weren't supposed to be run without an explicit COMMIT from the developer.  Because the nested transaction did NOT commit and a ROLLBACK was issued, the rollback rolled back BOTH the open transactions, and the batch proceeded as normal and executed the DELETE statements, outside of a transaction context - leaving us no way of getting them back, bar recovery from a backup.

Luckily this wasn't a disaster in this case as the data wasn't of huge importance.  But it was a lesson for me on using nested transactions.  Many other sources on the web call them 'evil' and 'not to be trusted' - and now I know why.

The Symbiosis of Managers and Engineers

I was struck by a blog entry recently.  A senior executive from a company in the same field as a recent client of mine (am I being suitably anonymous yet?) wrote about his recent experiences trying to find more information on becoming an effective product manager.  Having first self-identified his deficiencies in this area, he was frustrated when, upon Googling the term, visiting a website about becoming a better product manager and downloading the recommended e-book, that the text in question was around 200 pages long with over 40 chapters.  In the remaining couple of paragraphs, he vented his disgust that there wasn't a five-minute summary or a short synopsis (beyond the blurb), or a summary chapter which he could read, so he could distil the essence and perhaps return to the finer detail of the chapters at a later date, as and when required.  He wrote that he wasn't 'the kind of person' to whom that format would appeal, and went further in slating the publishers for failing to consider the end-user (him) - cleverly relating this back to the very principles of good product management that the publishers failed to meet.

Now I got a little suspicious at this point - I tend to now, having gotten that bit older.  I'm a lot more suspicious of unsubstantiated claims.  So I tracked through, found the link, found the book.  In three short clicks I found the table of contents.  And the final chapter in the contents?  A summary of the key points from the entire book.  Three pages.  And it took me under a minute to find.  Why, oh why I opined, could this manager not take the trouble to read just SOME of the detail?  He would have discovered, had he scrolled to the end of the contents pages, this summary chapter.  Alternatively, from Googling the title and author I was able to find detailed Scribd notes with bullet-point synopses of each chapter.  Additionally, customer reviews also gave subjective opinions from readers who had already bought the book on Amazon.  None of what I did was very technical, just a case of looking for details.  As you might have gathered by the evident negative bias I'm already placing on this account, I'm a technician, or an engineer, and this kind of attention-deficit behaviour I find incomprehensible.  Alas, this lack of focus on detail is symptomatic of the management class, the big-picture people.

Now before you think, 'here we go, another rant against managers', I'd like to explain why I'm writing this -  I want to lay out some of the fundamental differences between the management and engineering camps as I see them, and provide an argument about why co-operation is vital between these two warring factions.  Feel free to substitute your own terms as you read - manager can be replaced with leader or non-technician, and the terms engineer and technician are used interchangeably anyway.  I'm interested in exploring my own thoughts on the dynamic between these two groups in a semi-structured way, yet I won't apologise for any tangents or rambling; although I'll try and keep the grammatical errors to a minimum. If you're still reading, hopefully you'll come along to the end.  

Sometimes, the worlds of management and the worlds of engineers seem miles apart.  Indeed, if you'll permit my generalisations, almost every trait varies between the two groups.  Take workplace fashion as a surface example; with managers dressed for business, smart shirts, ties and trousers, and engineers (or technicians, if you prefer the term), typically dressed for an evening in front of the XBox. It's not just physical characteristics that vary - the manager tends to be an extrovert, comfortable in front of large audiences, excited and energetic in the face of crisis, and the driving force behind many a team.  Whereas in contrast the typical engineer will be quiet, introverted, even withdrawn; some may say sullen, opinionated, comfortable in their own intellect but a poor team player and a worse leader.

Now before I get a hundred comments flaming me, accusing me of making crass generalisations, let me say that I'm working with just the stereotypes of the manager and engineer here - I fully recognise that each type has edge cases, and the condition of 'manager' or 'engineer' actually encompasses two broad spectra, on which can be observed many of the characteristics of people.  However for the sake of argument (and because you all probably know at least one manager and one engineer that conform to my definitions) let's accept these definitions and roll on. 

So let's look at the differences in time management between the manager and the engineer.  The manager fills their day rushing from meeting to meeting, with occasional stops at their desk to read their emails and dash off brief replies.  In transit, they stride along broken-necked, staring at their iThings.  They may carry around pieces of paper with fragments of project plans, or keep a notebook with minutes of meetings.  These meetings can happen in dedicated rooms, at desks, in corridors or on the phone.  These are people that 'do lunch'.  To an outsider observing management behaviour, it may seem that no real work is being done, that the manager is simply an outmoded and inefficient paradigm left over from the days of Filofaxes, Rolodexes and dinosaurs, rushing around talking to people about nothing at all, when everyone knows the REAL work these days is in technical roles, don't they?  Don't they?

Let's try and describe the job of a manager.  Managers have a very specific role, and the clue's in the title.  They manage (well, duh).  So what does this mean?  They communicate, empathise, associate, organise, motivate, prioritise.  They drive others, assess others, recruit others and sometimes fire others.  These are qualitative verbs, 'doing' words that don't immediately conjure tangible results.  What I mean by this is that organisation, communication, understanding - these don't seem 'real' in many ways, they don't present you with a finished product, a piece of code, a software module, a completed project - they simply seem too intangible to matter.  What IS it that managers actually DO?  Why do they have to spend the day communicating with others instead of producing quantitative, measurable output?  Compare these verbs with some engineering verbs.  Program.  Test.  Build.  Measure.  Analyse.  Maintain.  Diagnose.  The engineering verbs immediately suggest measurable, tangible, quantifiable output.  

But this isn't entirely fair.  This argument implies that the work of managers is somehow worth less than the work of engineers.  That the manager is there to make the bustle and the engineer is there in the background to deliver the product.  But counter to this line of argument, there is arguably a case that without the business bullshit, there'd be no work for engineers at all.

The skills of, for example, a project manager must include the ability to communicate complex ideas to different groups with different skill sets.  The PM must learn to 'talk tech' with the developers, the DBAs, the architects.  They need to understand the business data too, able to converse with the analysts and understand their needs.  The successful project manager must be able to take a thousand pieces of information and coalesce the ideas into meaningful, aggregated communications for different audiences, from the boardroom to the development team.  Can you imagine the terror of a new manager standing in front of his new technical team, their average IQ 148, with 3 PhDs in the room, introducing himself and subliminally trying to convince them how his addition to the team is a net benefit?  Then switching focus to the boardroom and doing it all over again?  Not many engineers could deal with the stress.  Can any management-resenting developer or engineer honestly claim to possess these skills?  

Indeed, I'll go further and argue that the stereotypical engineer is a self-confessed sociopath; anti-social and misanthropic, self-absorbed and arrogant.  The typical engineer isn't remotely equipped to use qualitative skills.  If you're a non-technician, stand up right now, find your nearest software developer and ask them to explain why TRUE isn't equal to NULL in simple terms.  Watch them squirm as they try and fail to find non-technical terms, to analogise, to explain.  They likely know the answer.  But they may laugh, or stutter, or struggle with embarrassment.  This works especially well if you're female and attractive.  Now repeat the experiment, but find a project manager and ask them to explain S.M.A.R.T. objectives.  They'll likely deliver a comprehensible, smooth, balanced answer and engage you in conversation about it afterwards.  Now do you see the difference between the two camps, and the benefits that management can bring to facilitating communication?  Given the right preparation, a non-technical manager is also capable of delivering complex TECHNICAL ideas too,  thus acting as an effective translation medium between the board and the shop floor. All he needs are the right facts.

Let's lighten the mood.  Here's one of my favourite quotes from the film Armageddon.  Billy Bob Thornton plays the hardcore military leader looking for answers from his geeky technical team on the approaching asteroid.  The technician is bearded, pale, unhealthily plump and terrified.  He is summoned to the war room and everyone's looking at him.  The dialogue goes something like this:

<General>
So give me a summary. 
<Technician><splutters - is shaking>
Uh.. well... (panics) ... We've been..uh.. looking at... uh...
<General><purposefully, not unkind>
Okay, I need someone who's had a little less caffeine this morning.

This is flippant, I know, but in this fictional example, the technician crumbles under pressure - lots of valuable technical information crammed into his head but without the ability to summarise and express it when it matters.  Billy Bob is playing the leader, the manager, the organiser.  There's nothing he personally, practically, using his hands, can do about the asteroid except marshal and drive the team that WILL do something about it, but he's confident in his actions and clear on his objectives.  And conversely, without his guidance, his orders, the drilling team who do the work would never get the chance to go to space, to get up there and sort out the problem in the first place as they don't have the necessary qualitative skills to arrange it.  Look at the misfits sent to space in Armageddon - of one, 'our toxicology results revealed ketamine'.  Co-operation between technicians and managers has to be a symbiotic relationship to work.

Without management providing other qualities such as the goals, the motivation, the team spirit and the drive, technicians like to think they would be coding to save the world, working on cutting-edge projects and developing new and exciting technologies, creating the new Facebook or inventing the hoverboard.  In reality, engineers would sit around on Reddit all day, or head home for an afternoon's nap.  Devoid of the ability to work in larger teams with disparate groups of people, devoid of the ability to plan for an event longer than the next guild raid, devoid of the ability to organise even their own wardrobe, let alone a complex juggernaut of a critical business project, technicians quickly lose business value.  Without external motivation, an engineer quickly becomes bored, restless, even depressed.  Anecdotally, I've seen a lot more developers quit their jobs than management.  Managers tend to stick it out, fix what's broken.  Engineers will cut and run when they reach some threshold of alienation from the business, from the environment around them.  I know, I've been one of them myself.  Because we lack longer-term focus, and get can frustrated by complex social situations (such as endless, endless meetings), we're more inclined to rage-quit, to get our coats on, shout fuck it and go home to bed.  Managers have a longer-term focus, and are better at juggling these longer-term priorities and goals.

Both camps have a lot to learn about each other in order to maximise the benefit and minimise the cost of association.  Management must realise that engineers normally enjoy their job, often to the point of obsession.  They enjoy wandering into work in an Atari t-shirt and trainers clutching a coffee-stained mug.  They enjoy the complexity of long, difficult problems to solve.  They will often work 12-hour days, then go home and spend the evening behind the computer.  They get job satisfaction from untangling knotty problems, rewriting software, designing hardware, creating TANGIBLES.  There's nothing less appealing for an engineer than having the impression that his whole day has been for nought.  And if you as a manager strive to create the ideal conditions for engineers to work (with yourself as a supporting actor and a large quantity of free coffee available), you'll get far more productivity, lower staff turnover and a higher level of morale from these teams than otherwise.

And us technicians, in turn, must realise that the job of management isn't to code up our data import module, nor advise us on code reuse or answer whether a function or a procedure is preferred.  Their job is to guide, provide an escalation point, help and support us into achieving the goals that we've been assigned.  It's to translate instructions between different groups, facilitate open communication, observe business procedures, obtain approvals and a hundred other intangibles that assist us in doing our jobs.