Pages

Saturday, 10 September 2016

Chapter-7 Managing Ado.net Transactions

A Transaction in Ado.net is a group of Commands that change the data
source in a database.The transaction which is treated as Single unit and
assures the commands will run sequentially .

1)Ado.Net transaction can be defined as a sequence of
operations that are performed together as a single logical unit of work.

2)If a transaction is successful, all the data
modifications performed in the database will be committed and saved.

3)If a transaction fails or an error occurs, then
the transaction is rolled back to undo the data modifications done in the
database.

4)For a transaction to commit successfully within
a database, it should possess the following four properties:

a)Atomicity
: Single unit of work. States that either all the modifications are
performed or none of them are performed.

b)Consistency
: All relationships between data in a database are maintained correctly. States
that data is in a consistent state after a transaction is completed
successfully to maintain the integrity of data

c)Isolation
: Change made by the other clients cannot effect the current changes.
States that any data modification made by one transaction must be isolated from
the modifications made by the other transaction

d)Durability
:Once a change is made, it is permanent . If a system error or power
failure occurs before a set of commands, those commands are undone and the data
is restored to its Original state. States that any change in data by a
completed transaction remains permanently in effect in the database

5)Transaction is important for Web application
because it is distributed among different clients across the world. There are
various Key problems:-

a)Contention
for resources : Various clients try to change the same record and same
time.

b)Unexpected
Failures : Internet is not 100% reliable due to sometime the power failure
or internet failure by service provider ,the user abnormally disconnect by the
web server.

c)Web
application life cycle : We application life cycle is not same as Window
application life cycle. Web forms have only one instant .Client can leave your
website at any point of contact after entering the address in the new browser.

6)ADO.NET provides support for two types of
transactions :-

a)Local
transactions : A local transaction performs on a single data source.
Because local transactions are performed on a single data source, these
transactions are efficient to operate and easy to manage.

b)Distributed
transactions : A distributed transaction performs on multiple data sources.
Distributed transactions enable you to incorporate several distinct
transactional operations into an atomic unit that either succeed or fail
completely.

DataSet provide implicit transaction processing because changes to a data
set are not made in database until you invoke the Update method on the data
adapter.

If an error occurs during the Update method none of the changes from the
data set is made in the database.

ButtonRestore_click()

{

//Restore
the dataset its original state

Ds.RejectChanges();

Gridview1.DataBind();

}

ButtonCommit_Click()

{

int
rows;

//Update
the database from the data set

rows=adt.Update(ds);

//Save
changes to state variable

Session[“dsContacts”]=ds;

Gridview1.DataBind();

}

RejectChanges method returns the data set to its state
before the row was deleted. Data set AcceptChanges method is the inverse of
RejectChanges, it reset the DataRowState property for all the changes rows in
dataset to Unchanged and remove any deleted rows.

AcceptChanges method prevents the Update method from making
those changes in the database.It is useful only when you do not intend to
update a database from data set.

Managing Distributed
Transactions

1)Distributed transactions are performed on
multiple data sources or multiple connections within a data source.

2)Distributed transactions are created in the
System.Transaction namespace.

3)The System.Transaction namespace has a
TransactionScope class, which enables a developer to create and manage
distributed transactions.

4)To create a distributed transaction, a
TransactionScope object is created in a using block.

5)The TransactionScope object decides whether to
create a local transaction or a distributed transaction. This is known as
transaction promotion.

1)Bulk copy operations can be performed as an
isolated operation or as a part of a transaction.

2)By default, a bulk copy operation is its own
transaction.

3)To perform a bulk copy operation, you need to
create a new instance of BulkCopy class with a connection string.

4)The bulk copy operation creates, and then,
commits or rolls back the transaction.

Specifying Isolation Levels
of a Transaction

1)An isolation level determines the effect a
transaction has on other transactions that are currently running, and vice
versa.

2)By default, all transactions are completely
isolated and run concurrently without impacting each other.

3)The isolation level of a transaction specifies
the locking strategy used by the connection running the transaction to prevent
concurrency problems when multiple transactions access the same data.

4)The following table describes the concurrency
errors that can occur if multiple transactions access the same data at the same
time.

Concurrency Error

Description

Dirty read

A transaction reads the data that has not been committed
by the other transaction. This can create problem if a transaction that has
added the data is rolled back.

Nonrepeatable read

A transaction reads the same row more than once and a
different transaction modifies the row between the reads.

Phantom read

A transaction reads a rowset more than once and a
different transaction inserts or deletes rows between the first transaction’s
reads.

1)The various types of isolation levels of a
transaction are:

a)Read
Uncommitted : Does not lock the records being read. This means that an
uncommitted change can be read and then rolled back by another client resulting
in a local copy of a record that is not consistent with what data is stored in
databse .It is also known as dirty read.

b)Read
Committed with Locks : Locks the records being read and immediately free
the lock as soon as the records have been read. This prevents any changes from
being read before they committed.

c)Read
Committed with Snapshots : Locks the records after taking Snapshots and
immediately free the lock as soon as the records have been read.

d)Repeatable
Read : Locks the records being read and keeps the lock until the
transaction completes. This ensures that data being read does not change during
the transaction:

e)Serializable
: Locks the entire data set being read and keeps the lock until the
transaction completes. This ensured that data and its order within database
donot change during the transaction.

SQL database connections provide one transaction capability that is
unavailable for OLE database connection : the ability to create save points within the transactions.

The following table describes the various isolation levels and the
corresponding concurrency errors for a transaction.

Isolation Level

Dirty Read

Nonrepeatable Read

Phantom Read

Read Uncommitted

Yes

Yes

Yes

Read Committed with Locks

No

Yes

Yes

Read Committed with Snapshots

No

Yes

Yes

Repeatable Read

No

No

Yes

Snapshot

No

No

No

Serializable

No

No

No

Let us understand how to set the
isolation level of a transaction to Read Committed.

g)A distributed transaction performs on multiple
data sources. A distributed transaction enables you to incorporate several
distinct transactional operations into an atomic unit that either succeed or
fail completely.

h)Bulk copy operations can be performed as an
isolated operations or as a part of a transaction. By default, a bulk copy
operation is its own transaction.

i)An isolation level determines the effect a
transaction has on other transactions that are currently running, and vice
versa.

j)The various concurrency errors that can occur
when multiple transactions access the same data at the same time are:

i)Dirty read

ii)Nonrepeatable read

iii)Phantom read

k)The various types of isolation levels are for a
transaction are:

i)Read Uncommitted

ii)Read Committed with Locks

iii)Read Committed with Snapshots

iv)Repeatable Read

v)Serializable

l)Manage data set transaction using Update method
to commit changes and the RejectChanges method to Undo ( or roll back) changes.

I hope that the functionality of the above attribute is clear to you now.
Now you can use this code in your application. If you have further question,
just drop a line below and I will try to answer you as soon as possible.

Last but not least, connect with me onTwitter , Facebook , LinkedInandGoogle+for technical updates and articles news. We won’t spam or share your
email address as we respect your privacy.