Seize the deal!
Caching Best Practices
Imagine having a tool that can automatically detect if you are using JPA and Hibernate properly. Hypersistence Optimizer is that tool!
You read it correctly. Any is a very broad term. However, these two tools that I’m going to talk about in this article allow me to solve any software development problem across a very broad range of skills.
Let’s first start with a very nice problem. As already explained in this post, some database systems (e.g. MySQL, SQL Server) allow you to acquire physical predicate locks on range scans so that no other transaction can modify the locked range.
Let’s give it a try on MySQL, and see if the predicate lock blocks an insert in the same range of rows:
Since we locked all
employee rows for the
department_id with a value of
1, Bob’s INSERT statement is going to be blocked waiting for Alice to release the lock, which she does at the end of her currently running transaction.
However, if we try to add a new
employee record in the
3, we can do it since the predicate lock only affects the
employee(s) belonging to the first department:
So far, so good!
Let’s see what happens when we switch to SQL Server. The first example works just as expected. However, the second use case yields the following outcome:
Why is Bob’s INSERT block since there is no overlapping between the locked
departement_id = 1 range and the
departement_id = 3 which is used by the incoming INSERT statement?
How do you figure it out, right? You start reading SQL Server documentation hoping to find the right answer, but whata if you still can’t understand why SQL Server behaves like this in this particular scenario?
Every time I run into a problem like this one, I use the first best developer tool: StackOverflow.
Many developers rely on StackOverflow to find the answer to a problem, but what if no one asked the same question as yours? Unfortunately, few developers I know turn to StackOverflow whenever they have a good question to ask.
StackOverflow is a two-way street. If you are an avid StackOverflow consumer, you need to pay back as well. I like to give answers on the topics that I know best, but it’s impossible to know everything! Therefore, why not ask the pool of thousands of passionate developers whenever I bump into a very interesting issue?
That’s what I did, and here you can find my StackOverflow question.
In less than 30 minutes, I already got two very good answers which explain why SQL Server behaved as it did.
This time, I got lucky. But what if no one answers your question? Then, you should add a bounty to it and see if that helps.
Right after asking the question on StackOverflow, I like to turn to the second best tool for solving any software development problem: Twitter.
Twitter is awesome! It allows you to get in touch with so many great people that share the same passion for software as you do.
So, I also asked the question on Twitter, and guess who replied back?
Hi Vlad, thanks for reaching out! Both answers are correct.— Microsoft SQL Server (@SQLServer) March 6, 2017
The official account of SQL Server validated that both answers are correct. How cool is that!
Most software companies hire Developer Advocates that monitor the official twitter accounts and try to help you get the most out of their products. So, take advantage of this opportunity whenever you have a good question that is not easily solvable.
If you enjoyed this article, I bet you are going to love my upcoming Online Workshops!
- Caching Best Practices with JPA and Hibernate (2.5 hours) on the 30th of September
- High-Performance SQL (4 hours) on the 6th of October in collaboration with Voxxed Days Ticino
- High-Performance SQL (12 hours) starting on the 28th of October in collaboration with Bouvet
The 2 best tools for solving ANY software developer problems are social tools, and that shouldn’t come as a surprise. We are all social beings, and we have evolved to rely on each other whenever we have a problem.
So, don’t hesitate to ask for help! It’s as simple as that.