Some More
David's answer is correct, but to add a few more things that may be important...
You can generally infer from an execution plan and query that:
- Seeks will start with key locks
- Scans will start with page locks
- Local locking hints will usually override other settings (rowlock may not always be honored though)
- Foreign Keys with cascading actions will take serializable locks
- Indexed view maintenance will take serializable locks when the view definition spans multiple tables
Some things can only be determined at runtime, and some only under concurrency. For example, lock escalation may be attempted, but may not always be successful due to competing lock incompatibility on the object. There may also be some local factors that change locking behavior, like changing index options around allowing row and page locks, etc.
To monitor locks, you can use:
You can monitor locks taken by using an Extended Event session like this one from a previous answer of mine: Offline Index Rebuild on a Partitioned Table.
But fair warning, you will need to alter it to be compatible with Azure SQLDB. You'll need to change references to SERVER
to DATABASE
, and you'll need to choose between storing the file in Blob Storage or just using the Ring Buffer instead. You will also need to hit slightly different views when attempting to query the session data if you choose to use that over the GUI.