From: esquel@sommarskog.se   
      
   Tony (johansson.andersson@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, esquel@sommarskog.se   
      
   Books Online for SQL Server 2005 at   
   http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx   
   Books Online for SQL Server 2000 at   
   http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)