Transactions

A transaction is a group of database statements that are all done as a single atomic unit. Or they might all be invalidated (rolled back). Transactions can be started implicitly when you issue a statement. They start before the statement, and end after it. Transactions can also be explicit. You determine when the transaction begins, and when it ends or rolls back.

If you have started a transaction but not committed it, other sessions cannot change or query the data you are modifying. This is done by locks that are applied when you make changes. Transactions can be nested. You can tell how deep you are by checking the @@TRANCOUNT. This value is 0 for implicit transactions. It is 1 inside the first level of transaction, 2 for the second level, and so on. It gets reverted to 0 upon commit.

Obviously locks are placed when you INSERT/UPDATE/DELETE. However locks can also be placed upon SELECT of data. The data you are querying is locked from any change until you have finished retrieving the data. The discussion so far is for the default behavior in SQL Server. That is, this is how things work when the isolation level is set to the default "read committed" level. There are other levels you can set on a session basis.