Tony (johansson.anders
...@telia.com) writes:
> Here is a text in a book. "To ensure that you don't leave your
> transaction open for too long, consider using sqltransaction object in a
> Using block. The longer the transaction remain open, the longer the
> database needs to maintain the locks for the rows. If the SqlTransaction
> has not been committed or rolled back at the end of the using block, the
> Rollback is called implicitly."
> The text mentioned maintain the locks for the rows. Does this mean that
> those rows that are included in the transaction is locked so that other
> users can't access these neither by select them or some other DML action
> query ?
If you start a transaction, and update rows, these rows are locked
exclusively by you, and no other user can access these rows. Depending the
operation, available indexes etc, the lock may be on page or table level,
meaning that also rows that you did not update. This condition lasts until
you commit or rollback the transaction. This is a key concept in an RDBMS.
However, the above needs some qualification. A user can set the isolation
level to READ UNCOMMITTED, or specify a NOLOCK hint which means just that.
They can see new values you have inserted. Using NOLOCK is a very risky
business, and should be avoided. Another possibility is that the user reads
from the snapshot. This happens if the database has setting
READ_COMMITTED_SNAPSHOT or the user explicitly specify the isolation level
SNAPSHOT. When reading from the snapshot, the users will see the old values,
but they will not be blocked.
To be a database programmer, you need to have a good understanding of
transaction. As it says in the book you quote, you should keep your
transactions longer then needed. For instance, it an absolute to no-no
to have a transaction open while waiting for user input. However, this
does mean that you commit every now and then for the sake of it. If you
are updating three tables, and the database would be inconsistent, if
one of the update fails, you MUST embed all updates in a transaction.
--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
