SQL Server Isolation Levels Notes (PASS 2013)

• Atomicity - Everything succeeds or nothing succeeds.  This is the idea with transactions.
• Consitency - Data cannot be left in an inconsistent state.
• Isolation - No transaction can interfere with any other transactions.
• Locking - Pessimistic
• Versioning - Optimistic, sql server snapshot

ANSI Isolation Levels

• Level 0 : Read Uncommitted (the least isolated) - WITH NO LOCK query hint uses this.  This is a dirty read.  Has been modified but not committed.  
• Level 1:  Read Committed - default behavior for SQL Server.  Only committed data is readable.  Locks are released as data is read.  Page level lock.
• Level 2:  Repeatable Read - holds shared locks on the pages until the transaction is committed.  Doesn't guarantee new data won't enter your set during the transaction. Causes many locks on the records.
• Level 3:  Serializable (the most isolated) - locks the entire range of keys to guarantee the data is accurate.  Often uses a table lock and should only be used in rare circumstances.  Can possibly control with partitions and filters.

Versioning

• Snapshot Isolation - the committed version that it last knew about
• Readers don't block writers
• Doesn't block
• No shared block,
• Types - ALTER DATABASE SET  READ_COMMITTED_SNAPSHOT WITH ROLLBACK IMMEDIATE.  This should be the default and turns on versioning.  Default will still use locking. This is the alternative to NO LOCK.
• version store is in TempDB

As a developer of over a decade, there is nothing more frustrating to me than having to provide a project sample to a third party vendor's support team in order to get an answer to what seems like an obvious question about coding with their product.  I am quite confident that this is a common stall tactic that is being suggested to the support team by management at some level because I see it from all of the big vendors more and more.

Next, let me say that I am also seeing just plain incompetence with support teams not reading the text you have given them to reproduce the issue.  This fact is easy to uncover based on the questions they ask me in response to my original inquiries.  Inevitably my simple question or bug report turns into a fiasco lasting through several support tickets and calendar weeks and in really bad scenarios months.  Whenever I challenge them on the code sample and say, "no, you provide me with a sample", I am usually punished by going to the bottom of the queue.

As I have almost exclusively been on the engineering side of the operations I have no real insight into these organizations, but my guess is they are underpaid and overworked and probably don't really want to be there at all, but none the less the problem remains regardless of excuse. There are exceptions to the above, but generally I find the exceptions rare and usually come from smaller organizations where the engineers work closely with support.

I heard an interesting topic this morning on the .Net Rocks! podcast in regard to process management and how 

one company deals with the issues.  So I thought I would just product a simple post of generally what has worked for me over the years.

First, let me say I have worked as a software developer and team lead for 14 years and I have worked in a variety of "shops" mostly in the manufacturing space, but also in banking. I didn't hear anything mentioned about developer creativity. Developers are by nature quite creative people. For some reason this never gets mentioned but a wise IT manager once told me that developers are some of the most creative people he had ever met. When a company has all this process, the individual creative spirit is often left out. Many developers, for example are into photography and music which are very split-brained activities. Too much left brain in software dev and it is suicide, but too much right brain and you never get anything done.

When a creative thoroughbred developer works for a company like this, it is like a death sentence, but most companies definitely need one or two of these types. Below is what i find to be the best combination for a development team and project resource allocation. It is so simple that the managers cringe because they know their jobs are in danger from the lack of process to manage. I have been on many successful projects that use these steps.

1. Use the best developer on the small to medium sized projects. That's right folks, one developer who knows his stuff from SQL to C# to Photoshop and back again.
2. Give him a business analyst to work closely with on a daily basis.
3. Give him access to a graphic artist.
4. Give him access to a DBA if the data is cumbersome.
5. Give him the freedom to create within the BA's specifications.
6. Give him source control and a bug tracking system.
7. Do not use daily continuous integration! Weekly builds are sufficient because the BA is right there approving as needed.
8. Only use ORM tools for CRUD operations. Use SQL stored procs for all "reporting like" pages in your web site or app.

A solid developer with 5+ years of development experience can knock out 3 medium sized projects a year by himself or about 5 to 7 small projects only working an 8 hour day.

Now for large projects. For the purposes of this discussion a large project might be somewhere around 30 or 40 web pages.

1. Use one thoroughbred developer and two newbs. The newbs will listen to he thoroughbred and will complement the team very well. NEVER and I mean NEVER but two really good developers together unless they are close friends! Unless you are writing an ERP system, do not have more than three devs on a team.
2. Use one or two BAs. Again be careful here because BAs can tick each other off also.
3. A full time DBA who works well with the above developers (hey here is where the manger comes in) 
4. Never do code reviews! It is just a competition to see who can write code the cleanest and most modern code. A complete waste of time. The newbs in this case will conform to what the lead dev is doing anyway.
5. Again give the developers creative freedom within the business requirements. Don't let the BA micromanage the devs. This is not their job (again management opportunity)!

The software development life cycle paradigm is less important than the above. I would just say to find one that fits your company but don't go overboard. A good book on the subject is "Rapid Development" by Steve McConnell. It is an oldie but a goodie. The bottom line is that in the end it is all about the thoroughbred developer. Remember when you said the guys upstairs wrote the mobile app as rouge programmers? Well that was a tenacious wanna be thoroughbred. Get you a couple of those guys for the one off projects like your mobile apps and you will impress the heck out of your customers. Be forewarned these guys are not worker bees. They ask questions and are engaging, but the bottom line is they get the job done.