SQL Query with Table Locking

Question

I am having an argument with a developer colleague on the team.

Problem: SQL query X runs for 1 second on the test system, but for an unknown amount of time on live system (150 users can run queries at the same time). The query causes locks on 8 tables, of which 7 are useless. His idea is to put a WITH (NOLOCK) on the 7 tables so there aren't any more locks.

My argument:

My suggestion is, with a nolock there is the chance that user 1 runs the select query which needs 10 seconds to complete because the server performance is low at the moment and user 2 changes a value in one of the 7 tables, e.g. a customer. Then the query would be completely wrong or maybe the expected dataset can't be filled and it crashes and throws an error. So use a Rowlock.

His argument:

We don't need a rowlock, the chances of getting such a scenario are too low. We don't need to be perfect. Do what is asked of you and don't think.

What can I say to win against such people who don't count on perfectionism?

Answer 1

What kind of database do you use. In Oracle for instance selects don't lock rows. We need more information.

Answer 2

I believe, based on what you have said that you are correct in your reasoning.

If there is ANY chance that something could go wrong, no matter how small a chance in the operation that causes the database to lose integrity it MUST be fixed.

Integrity is one of the basic premises of database design your co worker sounds like he is not being rigorous in his work.

If you are trying to construct a technical argument to "beat" your co worker, note that it may not give you the desired outcome you imagine.

If your co worker is not amenable to what you are saying AND if you are REALLY sure that you are correct in your reasoning, then I would inform your team leader why you think this is important and show him your solution. If he agrees with your co worker because he believes that database integrity is not important, then perhaps you should look at working somewhere else.

Don't get me wrong, I realise that in the real world software cannot be 'perfect' otherwise it would never be released. But something as fundamental as data input checking should not be skipped over, and it isn't difficult to do. It's basically the same as saying, "well let's not bother to validate user input". This is something you learn how to do this in a first year Computer Science class!

We have enough crappy software on this planet and this is the age where we are capable of AMAZING THINGS. Sloppiness in Software Engineering doesn't have a place anymore and I hope that you do not let your co worker lower your standards. Keep your standards high and you will learn more than he does and eventually do better in the long run.

Answer 3

If you are using SQL Server, which it sounds like you are then you instead of worrying about using NOLOCK for readers blocking writers (a common issue on high use SQL Server DBs doing lots of reads and writes) you should consider using SQL Server Row Versioning transaction isolation. This works with SQL Server 2005 and above.

This makes SQL Server work much more like Oracle does and eliminates the issues caused by readers blocking writers. Please read into the disadvantages too before you make the decision to use it.

Answer 4

ACID: - atomicity, consistency, isolation and durability. These are the basic tenets of databases that you ignore at your peril.

What your colleague is stating is that it's okay to ignore isolation, the property that you don't get to see half-done transactions. That's okay in some situations.

For example, we have a set of reports that are not used for critical business purposes but merely to give an indication as to the general health of the system. For that, 95% accuracy is good enough and we don't want the reporting to get in the way of the real work.

But, for a statement from a bank to one of it's customers, 100% is the absolute minimum accuracy. In situations where you will rely on the data, isolation must be adhered to.

You need to decide which bucket your particular system falls into. I'd be willing to bet good money that the number of situations in which you can ignore any of the ACID principles is minimal.

Answer 5

From my experience, Murphy's law is true: If anything can go wrong, it will.

We don't need to be perfect is not an argument. You, and your colleage, have certainly requirements to conform with.

Answer 6

"Do what is want from you and don't think."

Remember that you're always the person in charge of your own code, if something goes wrong, you can't say "He told me that to do it bla bla bla" ...

Your collegue is wrong, you always have to think, they pay you for use your brain, you're not a Teacher of aerobics (only a joke, sorry for all those Teachesr of aerobics that love programming).

Answer 7

Locking hints in SQL Server 2000 (SS2k) were useful because SS2k was greedy about locking on UPDATE statements and would default to TABLELOCK and narrow it as it progressed. If you knew your UPDATE statement's pattern you could use locking hints to increase performance and SS2k would escalate the lock if needed.

NOLOCK was introduced for dirty reads of locked data. If a table is frequently updated and queries that don't rely on the validity of the underlying data are being blocked, you could use NOLOCK to read the data in whatever state it was in. If you need to read records to generate a search results page you might choose to specify the NOLOCK hint to ensure your query isn't blocked by any update statements.

I believe lock escalation was reworked in SQL Server 2005 and locking hints are no longer respected.