Re: all about transaction

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

Date: Tue, 14 Dec 2004 10:50:08 +0100

On Tue, 14 Dec 2004 01:17:03 -0800, Deepson Thomas wrote:

>
>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 ???

Hi Deepson,

The transaction isolation level is a setting that controls how long shared
locks are held by a transaction and whether exclusive locks are honored.
All available transaction isolation levels are described in detail in
Books Online.

>..... whatz chained or unchained in transactions ??? .....

I've enver heard of these and I couldn't find any reference to this in
Books Online. Where have you heard these terms?

> from where i will
>get the full details of the four isolation levels u specified in the previous
>post ....

Books Online; use the index to find "SET TRANSACTION ISOLATION LEVEL".
That's a good starting point.

>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 ??? ...

If you update a row, it is locked. Nobody else will can access it (except
in uncommitted read mode), so nobody will be able to delete it until your
transaction is either committed or rolled back. The person who wants to
delete the row will have to wait until the lock is lifted.
On the other hand, if the delete comes in first, you'll have to wait until
the lock is lifted before you're told that the row you want to update
doesn't exist (anymore).

>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 don't think that there are any conditions where deliberately nesting
transactions would help. The only useful feature of nested transactions is
that stored procedures that use a transaction will still work as expected
when called from within an open transaction. Example:

CREATE PROC Test
AS
  -- Do something
  BEGIN TRAN
  -- Do some more
  COMMIT TRAN
go

EXEC Test --This works
go

BEGIN TRAN
-- Change some data for testing
EXEC Test -- This still works
-- Do some SELECTs to check test results
ROLLBACK TRAN -- To get rid of temporary changes for testing

Best, Hugo 

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

Relevant Pages

  • Re: [RFC] JBD ordered mode rewrite
    ... data buffers that need syncing on transaction commit but a list of inodes ... that need writeout during commit. ... the file then that inode would be added to the journal list and the pages ...
    (Linux-Kernel)
  • Re: Behavior of Connection.commit()
    ... the DBMS message when when the DBMS has already killed the whole tx, and in that case, throw an exception from any subsequent connection, statement or result set method that would/could do an update or query saying "The DBMS has killed the current transaction. ... The user application may depend on holding locks in order, and once it is allowed to proceed obtaining the lock for data 2, it may then go on to obtain other locks before trying to commit, and this not-expected order of locking may cause deadlocks that could kill other innocent, correct transactions. ...
    (microsoft.public.sqlserver.jdbcdriver)
  • Re: transactions
    ... | insert and commit tran ... If the timing is exactly as depicted above, the second transaction will ... wait for the first transaction to commit before the select statement ...
    (comp.databases.ms-sqlserver)
  • Re: [RFC] JBD ordered mode rewrite
    ... data buffers that need syncing on transaction commit but a list of inodes ... that need writeout during commit. ... delayed allocation, starting a new transaction could to happen a lot to ...
    (Linux-Kernel)
  • Re: [PATCH 2/2] improve ext3 fsync batching
    ... array than it takes to complete the transaction. ... when commit times go up to seconds? ... Transactions on that busier drive would take longer, we would sleep ... longer which would allow us to batch up more into one transaction. ...
    (Linux-Kernel)
Quantcast