3

The best way I think I can ask my question is by starting with an example.

Imagine these two queries :

SELECT CAR.SerialNumber, TIRE.SerialNumber
FROM dbo.tCar AS CAR
INNER JOIN dbo.tTire AS TIRE ON ( TIRE.CarID = CAR.ID)
WHERE CAR.Brand = 'Jaguar'

and

SELECT CAR.SerialNumber, TIRE.SerialNumber
FROM dbo.tCar AS CAR
CROSS APPLY (
    SELECT TIRE.SerialNumber
    FROM dbo.tTire AS TIRE 
    WHERE TIRE.CarID = CAR.ID
) AS TIRE
WHERE CAR.Brand = 'Jaguar'

NOTE: tTire.CarID is indexed and tCar.Brand is indexed

These two would return the same output.

I know the second query is just foolish as it stand, but again, it just serve as an example for my question.

Now, I guess (even if I don't know the details) SQL Server will perform a clever and limited lock on the data read by the first query (JOIN).

But I'm wondering how it will perform lock for the data read by the second query (APPLY), mainly (and obviously imo) on the "tTire" data read.

In case of APPLY how do SQL Server perfom lock?

EDIT: This example is so simple it seems SQL Server manage to create the same execution plan, but my "APPLY" question should be though in terms where a RBAR effect occurs.

3
  • 1
    If you don't know the details of how SQL Server adds locks in the case of an INNER JOIN, how then are you going to see the difference when you are told about locks in the case of an equivalent CROSS APPLY (if there is any difference, that is)? Or, if you are guessing that in the case of INNER JOIN the locks will probably be "clever and limited", what prevents you from guessing the same about CROSS APPLY locks? Or are you actually asking for an explanation of how locking happens in each case?
    – Andriy M
    Aug 14 '15 at 9:46
  • 1
    These two queries should produce identical execution plans, so any locks would be identical. If two queries have different plans, then locks depend on the actual plan operators (like hash join or left anti semi join) and flow of the plan, what indexes are used and how they are used (seek or scan), not on high-level SQL abstractions like JOIN or APPLY. Aug 14 '15 at 10:23
  • My example is very simple but the generic behavior of APPLY would be a RBAR one, so doesn't RBAR cause other (poorer) results with locks than non RBAR? I need to know because APPLY can be really fast, but if it mean "whole table lock" then it could cause other "simultaneous" queries to wait or even dies on deadlocks. Aug 14 '15 at 10:36
10

A query is not an executable program. The SQL query specification language enables us to define the logical results of a query, but it is up to components like the query optimizer and execution engine to take care of the physical processing involved in delivering those results.

In principle, the physical processing side of things is free to perform whatever operations it likes, so long as the data returned matches the query specification. It might use hashing, scanning, sorting ... anything it likes, so long as the results match the logic of the query.

For example, the optimizer might well produce a very similar (or even identical) user-visible execution plan for both your sample queries, because they logically describe the same result. On the other hand, it might not; and it does not really matter either way.

The second major factor here concerns the transaction isolation level that the query is executed at. The different isolation levels available each provide different guarantees regarding the effects on one query due to concurrent data modifications by another.

Locks are just one possible mechanism by which these isolation guarantees might be enforced by the database engine. Other isolation implementations that do not use locks at all are available, in SQL Server as well as most mainstream products.

The point is that the engine guarantees to respect the requested isolation level, and it may do so using locks, or it may not. These details may be important sometimes from a practical point of view (for example when blocking occurs) but they are not truly fundamental.

As a result of all this, there is usually no direct relationship between the original written form of the query and the locks that might be taken - there's just too much other 'stuff' going on in between.

Isn't there even known "tendencies" of the SQL Server's behavior when it deals with RBAR scenarios? Something to be aware of maybe?

That comes with experience, though that is not universally a good thing. Certainly there is plenty of advice out there, though some is outdated, or based on a misconception.

In SQL Server, we can often safely encourage "set-based thinking" and say cursors, loops, and row-by-row processing should most often be avoided, but that is not the whole story, though this is all going a bit beyond your original question.

My usual advice is to write good queries first (using whatever syntax you find most natural) against well-designed and maintained databases, then worry about any specific issues like locking (optimizer limitations, plan shape issues etc.) if and when they arise. People do sometimes learn to avoid things like 'APPLY' for the wrong reasons, and have to unlearn it all later.

In general, I am pretty skeptical of advice that says to always prefer one syntax element or construction over another.


Resources:

1
  • Isn't there even known "tendencies" of the SQL Server's behavior when it deals with RBAR scenarosi? Something to be aware of maybe? Aug 14 '15 at 10:52
4

Locks will be taken in the same way in both cases, and that will depend on the isolation level and query hints. It will not do anything "clever and limited." It will do what it must to honour the semantics of those settings, in a memory-efficient way. If the two queries produce identical plans, as seems likely in this case, the same objects (row, page etc.) will be locked in the same modes (S, X etc.).

For more complex queries semantically equal SQL may produce different plans and the objects locked may be different.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.