SQL Learning Summary (9)--transaction processing and optimized query technology

9.1 setting up and committing a transaction

Lower isolation levels increase concurrency, but dirty data can be used to reduce the correctness of the data. Higher isolation levels guarantee the correctness of the data, but reduce concurrency, which affects the speed of the program and is easy to deadlock.

SQL Server supports 4 isolation levels: UNCOMMITTED read (READ UNCOMMITTED) is equivalent to NOLOCK, Read committed default option value, repeatable read (repeadable read), can be read serially (serializable).

Set TRANSACTION ISOLATION LEVEL

Set TRANSACTION ISOLATION LEVEL

REPEATABLE READ

Start a transaction

BEGIN TRANSACTION

Transaction Execution Code

SELECT * from staff where employee number in

(select employee number from order form where amount >15000)

and warehouse number in (select warehouse number from warehouse where city! = ' Qingdao ')

and wages >1500

Commit TRANSACTION COMMIT TRANSACTION

9.2 rolling back a transaction

Syntax format:

Rollback{tran | transaction}

[transaction_name | @tran_name_variable | savepint_name | @savepoint_variable]

BEGIN TRANSACTION Update_data

Update worker Set name = ' Transaction modification ' where employee number = ' Zg2 '

ROLLBACK TRANSACTION Update_data

9.3 Save Transaction

Syntax format:

Save{tran | transaction} {savepoint | @savepoint_variable}

Note: When a transaction begins, the resources used during transaction processing are persisted until the transaction completes (that is, locked). When a part of a transaction is rolled back to the savepoint, the resource continues to be retained until the transaction completes (or the entire transaction is rolled back)

9.4 application of lock in transaction

Shared Lock (S): Select

Exclusive Lock (X): Insert, delete, UPDATE, the exclusive lock cannot have a shared lock.

Deadlock: Unavoidable.

1. When two transactions lock two separate objects at the same time, each transaction requires a lock on another transaction-locked object, so each transaction must wait for another transaction to release the lock.

2. In a database, there are several long-running transactions that perform concurrent operations.

9.5 Optimizing Queries

1. In most cases, the Where condition statement contains or, Not,sql will not use an index; you can use in instead of or to replace not with the comparison operator! =.

2. Do not use the DISTINCT keyword when it is not necessary to display non-recurring runs, avoiding increased processing time.

3. When using the AND operator to find data in a range, the index is generally not used and can be replaced with between.

SQL Learning Summary (9)--transaction processing and optimized query technology