Re: all about transaction

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Hugo Kornelis (hugo_at_pe_NO_rFact.in_SPAM_fo)
Date: 12/14/04 

Date: Tue, 14 Dec 2004 09:38:11 +0100

On Mon, 13 Dec 2004 21:05:03 -0800, Deepson Thomas wrote:

Hi Deepson,

>Hi steave. u gave an intresting answer and frankly it increased my number of
>doubts also :) .. so according to u the sql statements executes after
>begintrans is directly affecting the tables but sql server is maintaining all
>these details in the transaction log right ??

Yep.

> .... so where sql server is
>keeping all the old data to put back if we executed rollback or if we didnt
>called rollback.

The entries in the transaction log are used to restore old values on a
ROLLBACK. Likewise, during recovery after a system crash ALL uncommitted
transactions will be rolled back (from the transaction log).

> this transaction log is a physical file in the server or itz
>saves in memory ??

It's a physical file and SQL Server uses no caching for this file. This
ensures that no modifications can be lost, not even on a system crash or
power outage.

> and what if i saw the details updated by some transaction
>queries and if i did some calculations and stored in some other table but
>after that he rolledback all the old data will come back and the data on
>which i did the calculations wont be there

Default behaviour of SQL Server won't allow this. Normally, all data that
is modified in a transaction is also locked. No other connection is able
to read those rows until the modifying transaction is either committed or
rolled back.

If you *choose* to override this default behaviour by forcing read
uncommitted transaction isolation level, you'll have to be aware that you
might indeed be reading changes that will eventually be rolled back.

> na...and whatz Dan said like "read
>uncommitted" and all.... is there anything else like that ....

Yes, there are a total of four isolation levels:
* Read committed (the default)
* Read uncommitted (aka dirty read - described above)
* Repeatable read
* Serializable

The last two have the effect of keeping data locked even if it is only
used in a read operation. More details can be found in Books Online,
subject SET TRANSACTION ISOLATION LEVEL.

Best, Hugo 

-- 
(Remove _NO_ and _SPAM_ to get my e-mail address)

Relevant Pages

  • Re: delete statement makes SQL Server hang
    ... But generally, a transaction won't be allowed to get more than say, several ... You can capture a lock escalation event with a profiler trace. ... transaction is committed and the transaction log is backed up. ... BG, SQL Server MVP ...
    (microsoft.public.sqlserver.programming)
  • Re: Transaction Isolation Level
    ... This means that while I initiate a transaction selecting all new orders it ... My query is executed on a SQL server 2005. ... the isolation level you should use is snapshot isolation. ...
    (comp.databases.ms-sqlserver)
  • Re: Table Variable vs Temporary Table
    ... > logging to the transaction log for table variables). ... Pro SQL Server 2000 Database Design - ... >>> We have a query that declares a table variable, ...
    (microsoft.public.sqlserver.programming)
  • Re: Windows Completely Locks Up!! FREEZE
    ... failing to start/run the Distributed Transaction Coordinator. ... Error Specifics: ... The Business Contact Manager SQL Server Startup Service service terminated ... The service did not respond to the start or control request in a timely ...
    (microsoft.public.windowsxp.general)
  • Re: Asynchronous Stored Procedure Never Returns - Help?
    ... If you have the Sql Server 2000 or 2005 docs they are thorough and can be ... for Transaction SQL Reference from the drop-down or select a keyword from ... your query in Query Analyzer or Sql Server Management Studio, ...
    (microsoft.public.dotnet.languages.csharp)