SQL Server Transactions

2. T-SQL query statement detailed transaction and lock

Designer: Yangwen

Anti-counterfeiting code: Thousands of million waves, although hard, blowing yellow sand began to gold

SQL Server supports transactional mechanisms to ensure consistency of data.

650) this.width=650; "Src=" Http://s2.51cto.com/wyfs02/M01/86/3D/wKioL1e5uBixXpavAAD7eUNm8y0782.jpg-wh_500x0-wm_3 -wmp_4-s_1352460473.jpg "title=" plca51}ts{95{]n1k}6lwx0.jpg "alt=" Wkiol1e5ubixxpavaad7eunm8y0782.jpg-wh_50 "/>

Case: In daily life, you may have used bank transfers, and bank transfer operations often involve two or more two accounts. At the same time that the deposit of the transferred account is reduced by a certain amount, the transfer to the account will increase the corresponding amount of the deposit, now assume that Zhang San's account transfers 1000 yuan to John Doe's account. How to achieve it?

1, first, need to create the Account table bank, storage user Zhang San and John Doe account information. We assume that the table structure

650) this.width=650; "Src=" Http://s5.51cto.com/wyfs02/M02/86/3D/wKiom1e5tf3QOKy8AAAluX2IXE0424.png-wh_500x0-wm_3 -wmp_4-s_887647484.png "title=" ' 0s_4d9ui}i6jl{(rnmg3]i.png "alt=" Wkiom1e5tf3qoky8aaalux2ixe0424.png-wh_50 "/>

2, the account balance must not be less than one yuan, so you need to set the CHECK constraint for the field Currentmoney, set the following steps.

First, open the Bank table's design window, right-click on the Currentmoney field, and select the check constraint from the popup shortcut menu.

said: 650) this.width=650; "Src=" Http://s1.51cto.com/wyfs02/M00/86/3D/wKioL1e5tv2gKLh1AAA6UCdNOXM297.png-wh_ 500x0-wm_3-wmp_4-s_241929016.png "title=" WRV) v~]ysj@]yqz[5v) da1.png "alt=" Wkiol1e5tv2gklh1aaa6ucdnoxm297.png-wh _50 "/>

3. Then, in the Check Constraint dialog box that opens, the single-machine add button, modify the constraint name, and edit the check constraint expression,

650) this.width=650; "Src=" Http://s3.51cto.com/wyfs02/M01/86/3D/wKioL1e5t7KD_Tw9AAASOs-pbtY739.png-wh_500x0-wm_3 -wmp_4-s_3726957444.png "title=" 9l5uma_e~uqor7 ' Rpuasknc.png "alt=" Wkiol1e5t7kd_tw9aaasos-pbty739.png-wh_50 "/>

650) this.width=650; "src="/e/u261/themes/default/images/spacer.gif "style=" Background:url ("/e/u261/lang/zh-cn/ Images/localimage.png ") no-repeat center;border:1px solid #ddd;" alt= "Spacer.gif"/>650 "this.width=650;" src= "http ://s2.51cto.com/wyfs02/m00/86/3d/wkiom1e5ugla__zhaadg7uipf5u467.jpg-wh_500x0-wm_3-wmp_4-s_1833440672.jpg "title = "{[_b}{) {cjkl}gs) 7eoiz67.jpg" alt= "Wkiom1e5ugla__zhaadg7uipf5u467.jpg-wh_50"/>

4, how to solve it? With transactions, the transfer process is a transaction that requires two update statements to complete, and the two statements are a whole.

If any of these errors occur, the entire transfer business should also be canceled, and the balance in two accounts should be restored to the original data to ensure the transfer

The sum of the balances before and after the transfer is unchanged, that is, 1001 Yuan.

5. Theory:

A, what is a transaction?

Concept

A transaction is an inseparable unit of work, including a set of commands that are either executed or not executed.

B, Attributes

Atomicity: A transaction is a complete operation. The elements of a transaction are non-divided.

Consistency: When the transaction is complete, the data must be in a consistent state.

Isolation: A transaction is independent, and it does not in any way depend on or affect other transactions.

Persistence: Once a transaction is committed, the effect of the transaction is permanently persisted in the database.

6. How to perform a transaction

A, the syntax for executing a transaction

Start transaction: Begin TRANSACTION

Commit TRANSACTION: Commit TRANSACTION

Rollback (undo) Transaction: ROLLBACK TRANSACTION

7, Transaction application Example

Here we apply the transaction to solve the above transfer problem, the T=SQL statement is as follows.

print ' view balance before transfer transaction '

SELECT * FROM Bank

Go

BEGIN TRANSACTION

DECLARE @errorsum int

Set @errorsum =0

Update Bank set Customermoney =customermoney-1000

where CustomerName = ' Zhang San '

Set @errorsum [email protected] [email protected] @ERROR

Update bank set Customermoney =customermoney +1000

where CustomerName = ' John Doe '

Set @errorsum [email protected] [email protected] @ERROR

print ' View balances during transfer transactions '

SELECT * FROM Bank

If @errorsum <>0

Begin

print ' Trading failed, rolling back things '

ROLLBACK TRANSACTION

End

Else

Begin

print ' transaction successful, commit transaction, write to keyboard, permanently save '

Commit TRANSACTION

End

Go

print ' view balance after transfer transaction '

SELECT * FROM Bank

Go

Transaction failure:

650) this.width=650; "Src=" Http://s5.51cto.com/wyfs02/M02/86/3D/wKioL1e5vHPgHda_AABF2_VUe6w666.png-wh_500x0-wm_3 -wmp_4-s_1229932800.png "title=" V6jnf4 (wwe[) 6lawlkn$7ui.png "alt=" Wkiol1e5vhpghda_aabf2_vue6w666.png-wh_50 "/>

Trading Success:

650) this.width=650; "Src=" Http://s2.51cto.com/wyfs02/M00/86/3D/wKioL1e5vKfjS65jAABBhlc4BJ4562.png-wh_500x0-wm_3 -wmp_4-s_3962943067.png "title=" L2) DR JD ' {7GNN) pqn%@~mi.png "alt=" Wkiol1e5vkfjs65jaabbhlc4bj4562.png-wh_50 "/>

8.

Recognize locks

What is a lock

For ensuring database integrity and consistency in a multiuser environment.

Mode of Lock

Shared lock (S lock)

Used to read data. A resource that has a shared lock cannot be modified.

Exclusive lock (x Lock)

Used for data modification. Incompatible with any other lock.

Update lock (U lock)

for reading and modifying. Compatible with S lock, does not affect reading, incompatible with u lock, avoids deadlock

How to view Locks

Using the sys.dm_tran_locks dynamic management view

Use Profiler to capture lock information

9, recognize the deadlock 650) this.width=650; "Src=" http://s1.51cto.com/wyfs02/M01/86/3D/wKiom1e5vZuxb8DRAAAXPKTsZio256.png-wh_500x 0-wm_3-wmp_4-s_1432000334.png "title=" _luvuko]0ahp2x}0sa~vn8v.png "alt=" wkiom1e5vzuxb8draaaxpktszio256.png-wh_50 "/>

Conditions of the deadlock formation:

Mutex conditions

Request and wait conditions

Conditions of non-deprivation

Loop wait Condition

Prevent deadlocks:

Breaking mutually exclusive conditions

Destroy requests and wait conditions

Breaking the conditions of deprivation

This article is from "A Candle" blog, please be sure to keep this source http://yw666.blog.51cto.com/11977292/1840917

SQL Server Transactions