SQL reading notes-transaction processing

Notes | transaction processing

Understanding SQL Incident Handling
1 The work done by the statement during SQL transaction can be canceled (using the rollback statement) before it is permanently written to the database. Commit tells the DBMS to make changes to the database permanent.

Let's understand the transaction process
such as: BEGIN TRANSACTION
Statement 1
Statement 2
Statement 3
Statement 4
The 4 modification statements were not committed until the application was properly terminated, which is not permanent, and thus the end of the exception will cause the DBMS to cancel the modifications made by all 4 SQL statements in the application body.

If you want the first two changes to be permanently saved to the database, regardless of whether the latter two were successful. You can perform the following transactions.
such as: BEGIN TRANSACTION
Statement 1
Statement 2
Commit TRANSACTION
BEGIN TRANSACTION
Statement 3
Statement 4

2 during an interactive session, the DBMS defaults to autocommit mode, which means that the DBMS automatically commits (making it permanent) the operation of each successfully executed SQL statement. Thus, if you execute the following statement
Delete FROM Employees
You will not be able to use ROLLBACK transaction to make the deleted data recover.

How do I disable autocommit mode? Ms_sql server allows automatic commit mode to be disabled by executing the following statement
BEGIN TRANSACTION

3 When using a large number of statements, you can use the method of the storage point, which allows the application to cancel the work and revert to a specific point in the transaction, whereby the application can use the rollback statement on the storage point to redo the job done by the partial statements in the transaction without having to start from scratch
such as: BEGIN TRANSACTION

CREATE TABLE Trans_table
(row_number SMALLINT,DESCRP varchar (35))

INSERT into trans_table values (1, ' Insert 1 ')
INSERT into trans_table values (2, ' Insert 2 ')
Storage Point Save1
Save Transaction Save1

Delete from trans_table where row_number = 2
INSERT into trans_table values (3, ' Insert 3 ')
INSERT into trans_table values (4, ' Insert 4 ')
Storage Point Save2
Save Transaction Save2

Delete from trans_table where row_number = 1
Delete from trans_table where row_number = 3

ROLLBACK TRANSACTION Save2

Update trans_table
Set DESCRP = ' Row 1 after 2 '
Delete trans_table where row_number = 4

Commit TRANSACTION

After executing the above statement
Execute SELECT * FROM trans_table
The results are as follows:
Row_number DESCRP
1 ' row 1 after 2 '
3 ' row 1 after 2 '