Shulou(Shulou.com)06/01 Report--

2. T-SQL query statement explains the transaction and lock in detail

Designer: Yang Wen

Security code: although thousands of waves are hard, it is hard to blow all the yellow sand to gold.

SQL Server ensures data consistency by supporting transaction mechanisms.

Case study: in daily life, people may have used bank transfers, and bank transfers often involve two or more accounts. While the deposit transferred out of the account decreases by a certain amount, the deposit transferred to the account will increase the corresponding amount. Now, suppose Zhang San's account transfers 1000 yuan to Li Si's account. How to achieve it?

1. First of all, you need to create an account table bank to store the account information of users Zhang San and Li Si. We assume that the structure of the table is as shown in the figure

2. The account balance should not be less than one CNY in the account table, so you need to set a check constraint for the field currentMoney. The steps are as follows.

First, open the design window of the bank table, right-click on the currentMoney field, and select CHECK constraint from the pop-up shortcut menu

As shown in the figure:

3. Then, in the open "CHECK constraint" dialog box, the stand-alone "add" button, modify the constraint name, and edit the check constraint expression

As shown in the picture

4. How to solve the problem? With transactions, the transfer process is a transaction, which requires two UPDATE statements to complete, which are a whole.

If there is an error in any of these items, the entire transfer business should also be cancelled and the balance in the two accounts should be restored to the original data, thus ensuring the transfer

The sum of the balance before and after the transfer remains unchanged, that is, 1001 yuan.

5. Theory:

A. what is a transaction?

Concept

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

B, attribute

Atomicity: a transaction is a complete operation. The elements of a transaction are inseparable.

Consistency: when the transaction completes, the data must be in a consistent state.

Isolation: transactions are independent and do not depend on or affect other transactions in any way.

Persistence: once the transaction is committed, the effect of the transaction is permanently retained in the database.

6. How to execute the transaction

A. the syntax for executing the transaction

Start transaction: BEGIN TRANSACTION

Commit transaction: COMMIT TRANSACTION

Rollback (undo) transaction: ROLLBACK TRANSACTION

7. Transaction application example

Let's apply the transaction to solve the above transfer problem, as shown in the T=SQL statement.

Print 'check the balance before the 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 = @ errorsum + @ @ ERROR

Update bank set customermoney = customermoney + 1000

Where customername ='Li Si'

Set @ errorsum = @ errorsum + @ @ ERROR

Print 'View the balance during the transfer transaction'

Select * from bank

If @ errorsum 0

Begin

Print 'deal failed, rollback things'

Rollback transaction

End

Else

Begin

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

Commit transaction

End

Go

Print 'check the balance after the transfer transaction'

Select * from bank

Go

Deal failed:

Deal succeeded:

8 、

Cognitive lock

What is a lock?

Used to ensure database integrity and consistency in a multi-user environment.

Lock mode

Shared lock (S lock)

Used to read data. Resources with shared locks cannot be modified.

Exclusive lock (X lock)

Used for data modification. Is not compatible with any other lock.

Update lock (U lock)

Used for reading and modification. Compatible with S lock and does not affect reading; incompatible with U lock to avoid deadlock

How to view locks

Use sys.dm_tran_locks to dynamically manage views

Use Profiler to capture lock information

9. Recognize deadlocks

Conditions for the formation of deadlocks:

Mutually exclusive condition

Request and wait conditions

Do not deprive conditions

Loop waiting condition

Deadlock prevention:

Break the mutually exclusive condition

Break request and wait conditions

Destroy the non-deprivation condition

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.