Re: all about transaction

From: Deepson Thomas (DeepsonThomas_at_discussions.microsoft.com)
Date: 12/14/04 

Date: Tue, 14 Dec 2004 01:17:03 -0800

Thank u very much Hugo... u cleared almost all my doubts... just have some
more .. hope u will help me... whatz this transaction isolation level ???
..... whatz chained or unchained in transactions ??? ..... from where i will
get the full details of the four isolation levels u specified in the previous
post .... and what if i start a transaction and edited a record and at the
same time if someother ppl start a transaction and deleted that record. when
both of us commit the transactions whatever the data i edited will get
removed or will get restored even after their deletion ??? ... just have a
look on the following block

begin tran
           --- SQL Satetments 1 ---
           begin tran
                       --- SQL Satetments 2 ---
                        begin tran
                                --- SQL Satetments 3 ---
                        commit tran
           commit tran
commit tran

This is a nested transaction where the outer most trans is responsible for
actual commitment according to my knowledge... can u just tell me in which
all conditions this type of nested trans helps us ... coz till we commit this
outer committrans the inner details also wont commit na .... i know this post
is little big but i hope u wont mind ...

Thanks in advance
Deepson

"Hugo Kornelis" wrote:

> 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: controlling lock order in transactions
    ... I believe the default isolation level for .net may be ... My transaction ONLY performs INSERTS. ... Yes it will put an exclusive lock on the row being inserted but Serializable ... COMMIT TRANSACTION ...
    (microsoft.public.sqlserver.programming)
  • Re: counting rows
    ... e.g. in read commited isolation level with two transactions ... At the start of the transaction they do (sql 2005 has versioning now also ... you'd need to serialise in order to make sure you didn't have ... s2: commit; ...
    (comp.databases.theory)
  • Re: restore transaction isolation level
    ... Fatal error doesn't go through the trap ... > You can set the isolation level back to READ COMMITTED: ... > SET TRANSACTION ISOLATION SERIALIZABLE ... >> COMMIT TRANSACTION ...
    (microsoft.public.sqlserver.server)
  • Re: restore transaction isolation level
    ... Fatal error doesn't go through the trap ... > You can set the isolation level back to READ COMMITTED: ... > SET TRANSACTION ISOLATION SERIALIZABLE ... >> COMMIT TRANSACTION ...
    (microsoft.public.sqlserver.programming)
  • Re: Update a record in SQL Database from C#.NET
    ... I begin a transaction with isolation level read committed and read ... When the user saves the new values I do the update and the commit transaction. ... > approach whether you are building your own UPDATE statement or if you ...
    (microsoft.public.dotnet.languages.csharp)