Transaction in sql server

What is transaction?

A transaction is a group of commands that change the data store in a database. a transaction, is treated as a single unit. a transaction is ensures that, either all of the commands succeed,or none of them. If one of the commands in the transaction fails, all of the commands fails, and any data that was modified in database is rolled back. In this way, transaction maintain the integrity of data in a database.

Transaction processing follows these steps : 

1.Begin a transaction.

2.Process database commands.

3.Check for errors.

If errors occurred,

  rollback the transaction,

Else

  commit the transaction

Note : we are not able to see the un-committed changes by using transaction in the query.

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

By setting isolation level read uncommitted in query the we can see the changes value in database.

We have apply the transaction on the following tables.

create table tblCustomerAddress(

id int identity primary key,

customer_code nvarchar(100),

city nvarchar(10),

pin_code nvarchar(6)

)

create table tblMailingAddress(

id int identity primary key,

customer_code nvarchar(100),

city nvarchar(10),

pin_code nvarchar(6)

)

create procedure SP_UpdateAddress

as

begin

begin try

begin transaction

update tblCustomerAddress set city='Delhi'

where id=1 and customer_code='101'

update tblMailingAddress set city='Delhi'

where id=1 and customer_code='101'

commit transaction

end try

begin catch

rollback transaction

end catch

End

The above procedure is working fine when execute the store procedure.

alter procedure SP_UpdateAddress

as

begin

begin try

begin transaction

update tblCustomerAddress set city='Delhi'

where id=1 and customer_code='101'

update tblMailingAddress set city='moradabad moradabad'

where id=1 and customer_code='101'

commit transaction

end try

begin catch

rollback transaction

end catch

End

The above procedure is not working fine because of that we are trying to update the longer value in city column which are set only 10 character limit in database,  so one query is fail then other query also fail here.