Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.ms-sqlserver > #911

Re: transaction

Show all headers | View raw

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

Back to comp.databases.ms-sqlserver | Previous | Next — Previous in thread | Find similar

Thread

transaction "Tony" <johansson.andersson@telia.com> - 2012-01-23 10:40 +0100
  Re: transaction Erland Sommarskog <esquel@sommarskog.se> - 2012-01-23 12:05 +0000

csiph-web