Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss
transaction
3 views
Skip to first unread message
Tony
Jan 23, 2012, 4:40:27 AM1/23/12
to
Hello!
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 ?
//Tony
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 ?
//Tony
Erland Sommarskog
Jan 23, 2012, 7:05:48 AM1/23/12
to
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.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
0 new messages