4 Fixed broken links
source | link

I would like to understand whether the following, very simple select statement would take any locks

It is a common misconception that a SELECT query running at the default READ COMMITTED transaction isolation level will always take shared locks to prevent dirty reads.

SQL Server can avoid taking shared row-level locksSQL Server can avoid taking shared row-level locks when there is no danger of reading uncommitted data without them (though higher-level Intent-Shared (IS) locks are still taken).

Even if shared row locks are taken (perhaps because another concurrent transaction has modified the page the row is on) they can be released long before the SELECT statement completes.

In most cases, the row is 'unlocked' just before the server processes the next row. There are circumstances in which shared locks taken at the default isolation level are held to the end of the current statement, but not to the end of the transaction.

Overriding the current isolation level with the NOLOCK table hint is almost always a bad ideaalmost always a bad idea.

Locking is an implementation detail. SQL Server takes locks when necessary to ensure it meets the semantic guarantees provided by the current isolation levelisolation level. There are certainly times where it is useful to know a little bit about why locks are taken, but attempting to predict them is very often counter-productive.

SQL Server provides a wide variety of isolation levels; choose the one that provides the guarantees and behaviours your data consumers need.

I would like to understand whether the following, very simple select statement would take any locks

It is a common misconception that a SELECT query running at the default READ COMMITTED transaction isolation level will always take shared locks to prevent dirty reads.

SQL Server can avoid taking shared row-level locks when there is no danger of reading uncommitted data without them (though higher-level Intent-Shared (IS) locks are still taken).

Even if shared row locks are taken (perhaps because another concurrent transaction has modified the page the row is on) they can be released long before the SELECT statement completes.

In most cases, the row is 'unlocked' just before the server processes the next row. There are circumstances in which shared locks taken at the default isolation level are held to the end of the current statement, but not to the end of the transaction.

Overriding the current isolation level with the NOLOCK table hint is almost always a bad idea.

Locking is an implementation detail. SQL Server takes locks when necessary to ensure it meets the semantic guarantees provided by the current isolation level. There are certainly times where it is useful to know a little bit about why locks are taken, but attempting to predict them is very often counter-productive.

SQL Server provides a wide variety of isolation levels; choose the one that provides the guarantees and behaviours your data consumers need.

I would like to understand whether the following, very simple select statement would take any locks

It is a common misconception that a SELECT query running at the default READ COMMITTED transaction isolation level will always take shared locks to prevent dirty reads.

SQL Server can avoid taking shared row-level locks when there is no danger of reading uncommitted data without them (though higher-level Intent-Shared (IS) locks are still taken).

Even if shared row locks are taken (perhaps because another concurrent transaction has modified the page the row is on) they can be released long before the SELECT statement completes.

In most cases, the row is 'unlocked' just before the server processes the next row. There are circumstances in which shared locks taken at the default isolation level are held to the end of the current statement, but not to the end of the transaction.

Overriding the current isolation level with the NOLOCK table hint is almost always a bad idea.

Locking is an implementation detail. SQL Server takes locks when necessary to ensure it meets the semantic guarantees provided by the current isolation level. There are certainly times where it is useful to know a little bit about why locks are taken, but attempting to predict them is very often counter-productive.

SQL Server provides a wide variety of isolation levels; choose the one that provides the guarantees and behaviours your data consumers need.

3 edited body
source | link

I would like to understand whether the following, very simple select statement would take any locks

It is a common misconception that a SELECT query running at the default READ COMMITTED transaction isolation level will always take shared locks to prevent dirty reads.

SQL Server does not takecan avoid taking shared row-level locks when there is no danger of reading uncommitted data without onethem (though higher-level Intent-Shared (IS) locks are still taken).

Even if shared row locks are taken (perhaps because another concurrent transaction has modified the page the row is on) they are can be released long before the SELECT statement completes.

In most cases, the row is 'unlocked' just before the server processes the next row. There are circumstances in which shared locks taken at the default isolation level are held to the end of the current statement, but not to the end of the transaction.

Overriding the current isolation level with the NOLOCK table hint is almost always a bad idea.

Locking is an implementation detail. SQL Server takes locks when necessary to ensure it meets the semantic guarantees provided by the current isolation level. There are certainly times where it is useful to know a little bit about why locks are taken, but attempting to predict them is very often counter-productive.

SQL Server provides a wide variety of isolation levels; choose the one that provides the guarantees and behaviours your data consumers need.

I would like to understand whether the following, very simple select statement would take any locks

It is a common misconception that a SELECT query running at the default READ COMMITTED transaction isolation level will always take shared locks to prevent dirty reads.

SQL Server does not take shared row-level locks when there is no danger of reading uncommitted data without one (though higher-level Intent-Shared (IS) locks are still taken).

Even if shared row locks are taken (perhaps because another concurrent transaction has modified the page the row is on) they are can be released long before the SELECT statement completes.

In most cases, the row is 'unlocked' just before the server processes the next row. There are circumstances in which shared locks taken at the default isolation level are held to the end of the current statement, but not to the end of the transaction.

Overriding the current isolation level with the NOLOCK table hint is almost always a bad idea.

Locking is an implementation detail. SQL Server takes locks when necessary to ensure it meets the semantic guarantees provided by the current isolation level. There are certainly times where it is useful to know a little bit about why locks are taken, but attempting to predict them is very often counter-productive.

SQL Server provides a wide variety of isolation levels; choose the one that provides the guarantees and behaviours your data consumers need.

I would like to understand whether the following, very simple select statement would take any locks

It is a common misconception that a SELECT query running at the default READ COMMITTED transaction isolation level will always take shared locks to prevent dirty reads.

SQL Server can avoid taking shared row-level locks when there is no danger of reading uncommitted data without them (though higher-level Intent-Shared (IS) locks are still taken).

Even if shared row locks are taken (perhaps because another concurrent transaction has modified the page the row is on) they can be released long before the SELECT statement completes.

In most cases, the row is 'unlocked' just before the server processes the next row. There are circumstances in which shared locks taken at the default isolation level are held to the end of the current statement, but not to the end of the transaction.

Overriding the current isolation level with the NOLOCK table hint is almost always a bad idea.

Locking is an implementation detail. SQL Server takes locks when necessary to ensure it meets the semantic guarantees provided by the current isolation level. There are certainly times where it is useful to know a little bit about why locks are taken, but attempting to predict them is very often counter-productive.

SQL Server provides a wide variety of isolation levels; choose the one that provides the guarantees and behaviours your data consumers need.

2 added 6 characters in body
source | link

I would like to understand whether the following, very simple select statement would take any locks

It is a common misconception that a SELECT query running at the default READ COMMITTED transaction isolation level will always take shared locks to prevent dirty reads.

SQL Server does not take shared row-level locks when there is no danger of reading uncommitted data without one (though higher-level Intent-Shared (IS) locks still are still taken).

Even if shared row locks are taken (perhaps because another concurrent transaction has modified the page the row is on) they are can be released long before the SELECT statement completes.

In most cases, the row is 'unlocked' just before the server processes the next row. There are circumstances in which shared locks taken at the default isolation level are held to the end of the current statement, but not to the end of the transaction.

Overriding the current isolation level with the NOLOCK table hint is almost always a bad idea.

Locking is an implementation detail. SQL Server takes locks when necessary to ensure it meets the semantic guarantees provided by the current isolation level. There are certainly times where it is useful to know a little bit about why locks are taken, but attempting to predict them is very often counter-productive.

SQL Server provides a wide variety of isolation levels; choose the one that provides the guarantees and behaviours your data consumers need.

I would like to understand whether the following, very simple select statement would take any locks

It is a common misconception that a SELECT query running at the default READ COMMITTED transaction isolation level will always take shared locks to prevent dirty reads.

SQL Server does not take shared row-level locks when there is no danger of reading uncommitted data without one (though higher-level Intent-Shared (IS) locks still are).

Even if shared row locks are taken (perhaps because another concurrent transaction has modified the page the row is on) they are can be released long before the SELECT statement completes.

In most cases, the row is 'unlocked' just before the server processes the next row. There are circumstances in which shared locks taken at the default isolation level are held to the end of the current statement, but not to the end of the transaction.

Overriding the current isolation level with the NOLOCK table hint is almost always a bad idea.

Locking is an implementation detail. SQL Server takes locks when necessary to ensure it meets the semantic guarantees provided by the current isolation level. There are certainly times where it is useful to know a little bit about why locks are taken, but attempting to predict them is very often counter-productive.

SQL Server provides a wide variety of isolation levels; choose the one that provides the guarantees and behaviours your data consumers need.

I would like to understand whether the following, very simple select statement would take any locks

It is a common misconception that a SELECT query running at the default READ COMMITTED transaction isolation level will always take shared locks to prevent dirty reads.

SQL Server does not take shared row-level locks when there is no danger of reading uncommitted data without one (though higher-level Intent-Shared (IS) locks are still taken).

Even if shared row locks are taken (perhaps because another concurrent transaction has modified the page the row is on) they are can be released long before the SELECT statement completes.

In most cases, the row is 'unlocked' just before the server processes the next row. There are circumstances in which shared locks taken at the default isolation level are held to the end of the current statement, but not to the end of the transaction.

Overriding the current isolation level with the NOLOCK table hint is almost always a bad idea.

Locking is an implementation detail. SQL Server takes locks when necessary to ensure it meets the semantic guarantees provided by the current isolation level. There are certainly times where it is useful to know a little bit about why locks are taken, but attempting to predict them is very often counter-productive.

SQL Server provides a wide variety of isolation levels; choose the one that provides the guarantees and behaviours your data consumers need.

1
source | link