Unit 11 Transactions and Locks

Unit 11 Transactions and Locks

create database step2_unit13;
 go
 use step2_unit13;
 go
 -- Create data table
 CREATE TABLE account (
 id INT PRIMARY KEY identity,
 NAME VARCHAR(10  ),
 balance decimal(10  ,2)
 );
 -- Add data
 INSERT INTO account (NAME, balance) VALUES ('Zhang San', 1000), ('李思', 1000);

1. Application scenario description

What is a transaction: In the actual development process, a business operation such as transfer often requires multiple access to the database can be completed. Transfer

The account is that one user deducts money and another user adds money. If an exception occurs in one of the SQL statements, this SQL may fail to execute.

Transaction execution is a whole, allSQL statements must be executed successfully. If an exception occurs in one SQL statement, all

SQL statements must be rolled back , the entire business execution fails.

Simulate Zhang San to transfer 500 yuan to Li Si. A transfer business operation requires at least the following 2 statements:

Zhang San account number-500

Lee Si account +500

-- Zhang San account-500
 update account set balance = balance - 500 where name='Zhang San';
 --Li Si account +500
 update account set balance = balance + 500 where name='李思';

Suppose that when Zhang San’s account is -500 yuan, the server crashes. Li Si’s account did not add 500 yuan, so there was a problem with the data. We need to ensure that

OneSQL statement, the entire transfer will fail. The transfer is considered successful only if both SQL are successful. This is when you need to use transactions.

Case Demonstration 1: Transaction Commit

Simulating Zhang San transferring 500 yuan to Li Si (successful)

begin transaction -- tran, start transaction

 -- Zhang San account-500

 update account set balance = balance - 500 where name='Zhang San<span style="color: rgba(128, 0, 0, 1)Use resources in the same order in all transactions



Keep transactions as short as possible and in a batch


Avoid interacting with users within transactions and reduce resource locking time


Set a reasonable range for the deadlock timeout parameter



6. The relationship between transactions and locks
 Transactions and locks are different.  


Transactions have ACID (atomicity, consistency, isolation and durability), and locks are used to solve isolation  a mechanism.  


The isolation level of a transaction is achieved through the lock mechanism.  


In addition, locks have different granularities, and transactions also have different isolation levels (generally four types: read uncommitted  Read uncommitted, Read committed, Repeatable read, SerializableSerializable)  .  


 In specific programming, starting a transaction requires database support. If the database itself does not support transactions,  Then there is still no way to ensure that the transactions you use in your program are valid.  Locks can be divided into optimistic locks and pessimistic locks: Pessimistic locks: It is believed that there are transactions that also want to modify the data during the period of time when the database data is modified; Optimistic locks: It is believed that there will be no transactions to modify the database in a short period of time  Data; What we call locks in a general sense actually refers to pessimistic locks. During the data processing process, the data is placed in a locked state (implemented by the database).  Back to your question, if a transaction is enabled, others cannot modify the data before the transaction is submitted; if you rollback, your modifications in this transaction will be undone (not that others' modifications will not be done, because others will not modify the data at this time)  cannot be modified).  Of course, the premise is that the database you use supports transactions.  Another thing to note is that some databases support custom SQL locks to override the default transaction isolation level.  The lock mechanism, if a custom lock is used, is another matter.  Important: General transactions use pessimistic locking (exclusive).  
  
 Supporting video link: SQL Server Advanced Programming - NetEase Cloud Classroom (163.com)

          The sea is broad and the fish are leaping, the sky is high and I can do whatever I want. Give me a piece of blue sky and let me fly freely.