Overview

In many of my courses and lectures, I talk about how unintuitive some features and syntax can be. In terms of development, one of the most complex, confusing, and potentially problematic features is locking. But, it’s not the locks themselves – it’s the way that data is handled. Locks are a necessary evil to ensure that data is consistent, accurate, and appropriately isolated from other users. But, did you realize that your statement’s accuracy is tied to your session’s isolation level? Did you realize that while “read committed” sounds like a perfectly reasonable isolation level in which to execute, it results in phenomena known as inconsistent analysis? Yes, you are only reading committed data – but at what point in time?

What does this all mean? What affects isolation? What affects your transactions? How do you control it? How and why might you want to change your isolation level?

Ultimately, being able to answer all of these questions will not only result in a better understanding of how the system works but also results in a more effective use of both the system and the data. Understanding your options will also allow you to increase statement accuracy, giving you a definable point in time to which a statement reconciles – even allowing you to do real-time analysis in an OLTP system without blocking.

Simply put, this is a course that every developer should attend so that they have a better grasp of what their batches, transactions, and code (in general) are doing as well as how their queries are affecting and affected by locks. For administrators, this course will give you a much better understanding of what’s going on, how to troubleshoot, and how to work with developers to create a system with more accurate data and less blocking!

Instructor: Kimberly L. Tripp

Need Help Justifying Training? Here’s a letter to your boss explaining why SQLskills training is worthwhile and a list of community blog posts about our classes.

Ready to buy? Please see our Immersion Event Offerings for options, bundles, pricing. and more.

Select Quotes From Past Attendees of Kimberly’s Online Classes

• “Kimberly is incredibly knowledgeable and was able to adapt the techniques to all the different scenarios presented to her.”
• “The best educator I’ve ever seen. She makes complex concepts “magically” easy to grasp. Incred-amazing.”
• “Great course. I could hear clearly, the content was relevant to current day problems, and provided clear instruction.”
• “This was REALLY good. Getting to an IE is tough there are only a few a year and more importantly because there are only a few they cover a pretty broad range of information. Since I do mainly database design and query tuning, I can’t justify much beyond IE1 as I don’t do day to day DBA work. Seeing you were offering the online event focused on a specific topic – very large tables – was PERFECT. I know I really need to improve my knowledge of the options in that area. I recalled the PV/PT architecture from IE1 and knew a refresher, coupled with the new information would be perfect. The cost was BEYOND reasonable. The time frame, at only about 1/2 a day, was easy to justify and easy to manage keeping up with regular work while I did it. So this worked out to be a perfect event.” – Todd Everett, USA
• “SLIGHTLY prefer the classroom option, but attendance to a location can be very hard to manage and therefore WebEx is a VERY, VERY good alternative. I didn’t think it would be, but I’m definitely sold.” – Kevin Urquhart, UK
• “I’m really glad I decided to participate in this event. I learnt a lot and I already see lots of opportunities to implement some of the strategies at my workplace (after working through all the materials of course :P).” – Ernst Kruger, South Africa
• “Loved the online aspect. It felt like I was there with the question ability and having the questions just answered right there. I felt I had a voice and could ask anything and the ability to watch it later made it totally worth the registration.”
• “Always love to sit at the feet of Kimberly. Always learn a ton.”
• “I struggled with WebEx at first… felt a little like watching a video… BUT the moment it became interactive (which was very early on), that whole perception changed. By the end I REALLY enjoyed it as a method.”
• “I really enjoyed the ability to ask questions as the course went along so that I didn’t forget what I wanted to ask while you were teaching. This allowed for questions to come through and class to continue until a good stopping point to answer the questions. Plus having the questions written from other attendees was nice for future reference instead of trying to remember from an in-person class discussion.”
• “Kimberly, I have to tell you that you are hands down the finest educator I have ever met. Your dynamic personality, combined with your depth of knowledge, and passion to fully understand the subject matter are unmatched, in my experience. I feel truly privileged to have had the opportunity to learn from you again and again. You really DO make learning fun. Thank you!” – Mike Petri, USA
• “I learned a lot of new topics and found this class really useful. Usually when I attend a class or an in person event I learn new things but no where near this class. Thanks for a great class.”

Curriculum

Module 1: Batches, Transactions, and Error Handling

Topics covered include:

• Batches
• Transaction
• Transaction mode
• Transaction termination
• Transaction flow / logic
• Error eandling

Module 2: The Anatomy of a Data Modification

Topics covered include:

• Data
• Caching
• Logging
• Locking
• Durability

Module 3: Locking / Isolation

Topics covered include:

• Statement accuracy vs. data accessibility/concurrency
• Read committed
• Versioning
• Lock hierarchy
• Row locks
• Page locks
• Table locks

Module 4: Table Maintenance and Schema Locks (if time permits)

Topics covered include:

• Schema locks (object’s schema)
• Schema locks (object)
• Relaxed FIFO

Module 5: Locking, Blocking, and an Intro to Deadlocks

Topics covered include:

• Blocking
• Escalation
• Lock analysis
• Deadlocks
• Deadlock analysis
• Deadlock mitigation

Module 6: Versioning

Topics covered include:

• Understanding isolation
  • Read uncommitted
  • Read committed with locking
  • Read committed with versioning
  • Repeatable reads
  • Serializable
  • Snapshot
• Controlling isolation levels
• Statement-level read consistency
• Transaction-level read consistency
• Overhead/monitoring
• Isolation summary

Ready to buy? Please see our Immersion Event Offerings for options, bundles, pricing. and more.

Questions?

If you have any questions not answered by our Immersion Events F.A.Q., please contact us.