This article has participated in the call for good writing activities, click to view: back end, big front end double track submission, 20,000 yuan prize pool waiting for you to challenge!
Transactions are used to manage data updates and ensure that changes to data are written to the database or rolled back to the state before the data was updated.
A brief
- A transaction is a collection of update processing that needs to be performed in the same processing unit. Using transactions, you can manage the commit and cancel of data update processing in the database.
- The termination instructions for transactions include COMMIT (COMMIT) and ROLLBACK (cancel).
Use transaction processing to maintain database integrity by ensuring that batches of SQL operations are either completely executed or not executed at all.
What is a transaction?
In AN RDBMS, a transaction is the unit of updating data in a table. A transaction is a collection of update processing that needs to be performed in the same processing unit.
Transaction processing guarantees that a set of operations will not be stopped in the middle, and that they will either be executed completely or not at all (explicit instructions are required). The execution is committed (written) to the database on success, the error is rolled back (undone), and the database is restored to the state before the transaction began.
A transaction is a series of operations performed as a single logical unit of work. It’s all or nothing. Is an indivisible unit of work.
A transaction is a series of commands merged into a logical unit and executed as a whole. It mainly includes the following concepts:
- A transaction is a group of SQL statements;
- Rollback refers to the process of revoking specified SQL statements. Also call rollback.
- Commit refers to writing the result of an unstored SQL statement to a database table. The current transaction is about to commit and cannot be undone once committed.
- Savepoints are temporary placeholders set up in a transaction that can be rolled back to a reservation point (rather than the entire transaction). Or translated as save point.
Some RDBMS support multi-level transactions on the basis of reservation points
That is, a transaction is a set of SQL statements that can be executed and whose execution results can be committed or rolled back to indicate the end of a transaction.
Read-only transactions
If a transaction merely retrieves data instead of updating the database, this type of transaction is called a read-only transaction.
Create a transaction
In a DBMS, a complete transaction process is as follows:
Transaction start statement: DML statement1; DML statements2; DML statements3; . End-of-transaction statement (COMMITorROLLBACK);
Copy the codeThat is, you need to implement a transaction by enclosing a series of DML statements (INSERT/UPDATE/DELETE statements) using transaction start and transaction end statements.
Start of transaction statement
In fact, the start statement of a transaction is not defined in standard SQL, but is defined by the individual DBMS itself.
SQL Server, PostgreSQL start a transaction
BEGIN TRANSACTION;
Copy the codeBEGIN TRAN can be used only in SQL Server.
PostgreSQL also supports BEGIN; And START the TRANSACTION; Start a transaction.
MySQL, MariaDB start a transaction
START TRANSACTION;
Copy the codeMySQL also supports BEGIN; Or BEGIN WORK starts a TRANSACTION, which is START TRANSACTION; Alias: START TRANSACTION is recommended. .
Prevent begin from starting transactions, and begin… The end block is obfuscated and is generally not recommended. In particular, stored procedures resolve begin to begin… End, you must use START TRANSACTION;
Oracle starts a transaction
SET TRANSACTION;
Copy the codeDB2 has no transaction start statements. Oracle also appears to have no transaction start statements in older versions, at least 12C can use transaction start statements explicitly.
Example transactions for SQL Server, PostgreSQL, MySQL/MariaDB, Oracle, etc
For example, implementing the following transaction, reducing the unit price of sport T-shirts by 1000 and increasing the unit price of T-shirts by 1000, uses the following transaction in each RDBMS:
--SQL Server, PostgreSQL
BEGIN TRANSACTION; -- SQL Server: BEGIN TRAN; -- PostgreSQL can write BEGIN;
-- Cut the unit price of sports T-shirts by 1,000
UPDATE Product
SET sale_price = sale_price - 1000
WHERE product_name = 'Sport T-shirt';
-- The unit price of t-shirts increased by 1,000
UPDATE Product
SET sale_price = sale_price + 1000
WHERE product_name = 'T shirt';
COMMIT;
--MySQL
START TRANSACTION;
-- Cut the unit price of sports T-shirts by 1,000
UPDATE Product
SET sale_price = sale_price - 1000
WHERE product_name = 'Sport T-shirt';
-- The unit price of t-shirts increased by 1,000
UPDATE Product
SET sale_price = sale_price + 1000
WHERE product_name = 'T shirt';
COMMIT;
--Oracle
SET TRANSACTION; -- This sentence can be omitted
-- Cut the unit price of sports T-shirts by 1,000
UPDATE Product
SET sale_price = sale_price - 1000
WHERE product_name = 'Sport T-shirt';
-- The unit price of t-shirts increased by 1,000
UPDATE Product
SET sale_price = sale_price + 1000
WHERE product_name = 'T shirt';
COMMIT;
-- DB2
-- Cut the unit price of sports T-shirts by 1,000
UPDATE Product
SET sale_price = sale_price - 1000
WHERE product_name = 'Sport T-shirt';
-- The unit price of t-shirts increased by 1,000
UPDATE Product
SET sale_price = sale_price + 1000
WHERE product_name = 'T shirt';
COMMIT;
Copy the codeCOMMIT — COMMIT processing
COMMIT is the closing instruction to COMMIT all update processing contained in the transaction. Once committed, the transaction ends and cannot be restored to the state before the transaction began.
It is often necessary to confirm whether an update really needs to be committed before committing. Be sure to confirm your submission carefully.
ROLLBACK — cancel processing and ROLLBACK transactions
ROLLBACK is the closing instruction to cancel all update processing contained in the transaction. Once rolled back, the database is restored to the state before the transaction began.
--SQL Server, PostgreSQL
BEGIN TRANSACTION;
-- Cut the unit price of sports T-shirts by 1,000
UPDATE Product
SET sale_price = sale_price - 1000
WHERE product_name = 'Sport T-shirt';
-- The unit price of t-shirts increased by 1,000
UPDATE Product
SET sale_price = sale_price + 1000
WHERE product_name = 'T shirt';
ROLLBACK; -- Undo changes
Copy the codeA BEGIN TRANSACTION statement is required for a commit/rollback. If not, an error is reported in SQL Server and a warning is generated in PostgreSQL. MariaDB/MySQL can execute normally.
Whether or not these generate prompts or errors should be set by mode or error prompts. Usually the start and end of the best transaction (COMMIT /rollback) occur together.
SQL Server select Msg, Level 16, State 1, Line 19 The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION. Msg 3902, Level 16, State 1, Line 22 The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION. -- PostgreSQL Nothing is rolling back in operationCopy the codeAuto-commit transactions, implicit transactions, and explicit transactions
Autocommit Transaction mode, Implicit Transaction mode, and Explicit Transaction mode
You need to know where transactions start and end in a DBMS: knowing when transactions start in a particular DBMS is a simple test of one’s database knowledge.
Auto-commit transactions, implicit transactions, and explicit transactions
There is no standard start instruction for transactions. Therefore, transactions start differently for different DBMSS.
-
Typically, transactions are initiated without instruction. There are two main cases:
- Each SQL statement is a transaction (Auto commit mode–
Auto Commit, a statement is a transaction. - It counts as a transaction until the user performs COMMIT or ROLLBACK (Implicit transaction pattern–
implicit transaction, usually starting with the first statement, or after a COMMIT/ROLLBACK end-of-transaction statement, implicitly start a transaction until COMMIT/ROLLBACK is executed to end the transaction. Oracle also commits a transaction before DDL statement execution, not just after a COMMIT is encountered.
In an implicit transaction, a transaction is implicitly opened and then a explicit commit is made to end the transaction.
However, due to the way transactions are handled in Oracle, a transaction is implicitly committed before DDL statements are executed. This can lead to confusion between implicit transactions and implicit commits.
- Each SQL statement is a transaction (Auto commit mode–
-
Explicit transactions are explicit transactions that use explicit start and end instructions. Explicit transactions are explicit transactions that use explicit start and end statements. Explicit transactions can be complicated, especially in Oracle where DDL statements are implicitly committed in explicit transactions.
Only the explicit transaction mode gives us complete control over the start and end of a transaction.
Explicit transactions are the most widely used pattern and are best used in SQL writing.
How transactions are handled and implemented varies from RDBMS to RDBMS, depending on the relevant documentation.
SQL Server, PostgreSQL, and MySQL/MariaDB use auto-commit mode. Each DML statement has a transaction (committed on success). In this case, note the DELETE statement, which cannot be rolled back to the state before the deletion.
Statements in auto-commit mode are surrounded by invisible BEGIN and COMMIT transactions and COMMIT as soon as they are executed. That is, in automatic commit mode, a transaction begins before each SQL statement is executed and ends after each statement is executed.
Note the confusion between the start and end of a transaction and the SQL editor processing logic at execution time, batch SQL statement execution.
If the commit is not automatic, you can use the ROLLBACK command to cancel the transaction and restore the data in the table even after the DELETE statement is deleted.
Oracle databases use implicit transaction mode, starting a transaction before the first statement and ending a transaction after a COMMIT/ROLLBACK or before a DDL statement.
Transaction modes can be modified through configuration.
Note: Normally, the SELECT statement query table is also opened and in a transaction.
If you do not select from a table, you will not start a transaction in an implicit transaction, such as select GETDATE(); /SELECT CURRENT_DATE; Or SELECT 1, ‘ABC’; .
SELECT queries are also processed as transactions because the data needs to be read correctly by the isolation level of the transaction; And most queries need to be transacted as a whole along with the change data.
Why use explicit or implicit transactions?
In auto-commit mode, you can arguably do all the things you do in implicit and explicit transactions, so why use implicit and explicit transaction modes?
The most important point is that autocommit transactions are only used for a single query. If a transaction needs to involve multiple queries, you must use implicit or explicit transactions to achieve this. Ensure atomicity and integrity of multiple data manipulation statements.
And then there’s the one above. With explicit transactions, you have complete control over the start and end of a transaction; With implicit transactions, you can also control the end of a transaction.
User transaction
UserTransaction or UserTransaction, which is a user transaction. There is no special description of user transactions in the SQL standard or in the official documentation of each DBMS. From the data looked up, seems to come from Java about database programming JDBC in the statement.
User transactions allow explicit management of transaction boundaries, i.e. user transactions can control the start and end of a transaction, and obtain transaction state.
User transactions can be considered equivalent to explicit transactions.
PostgreSQL enables implicit transactions
Turn off auto commit on the command line
\set AUTOCOMMIT off
-- \set AUTOCOMMIT on
Copy the codeIn pgAdmin 4, you can turn off “auto commit” in the right drop-down with running triangles:
The disabling of automatic submission in PostgreSQL seems to be implemented only in the client administration tool. The PostgreSQL database server does not provide the ability to turn off auto-commit mode.
The client disables auto-commit by sending “BEGIN TRANSACTION” at the beginning of the statement.
Note: Implicit transactions are enabled in SQL Server
This section uses SQL Server to enable implicit transactions as an example
-- Enable implicit transactions
Set Implicit_Transactions On;
-- Closes implicit transactions
Set Implicit_Transactions Off;
Copy the codeAs follows, enable implicit transaction mode, perform DML, DDL, and roll back. You can see that none of the changes have been committed:
Set Implicit_Transactions On;
select * from Productcopy;
Update Productcopy set sale_price=2120 where product_id='0011';
Alter table Productcopy add test_col varchar(12) default null;
create table DDLTestTable (id int primary key identity);
rollback;
select * from Productcopy;
Set Implicit_Transactions Off;
Copy the codeIf you change the rollback above to commit, you can see all the changes.
As you can see, the implicit transaction mode in SQL Server only ends when an explicit commit/ ROLLBACK is encountered.
Enable “implicit transaction mode” in SSMS:
Top menu bar: Tools-> Options -> Query Execution -> SQL Server -> ANSI. Below, select SET IMPLICIT_TRANSACTIONS
In SQL Server, the global @@tranCount variable is used to obtain the number of transactions that have been enabled.
MySQL/MariaDB start implicit transactions
The SET AUTOCOMMIT command enables or disables automatic commit to enable or disable implicit transactions. The default autocommit is ON.
-- Turn off automatic commit and enable implicit transactions
SET AUTOCOMMIT = OFF;
-- SET autocommit = {0 | 1 | OFF | ON}
Copy the codeMySQL/MariaDB check the current auto-commit mode
MariaDB [test]> SHOW VARIABLES LIKE 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit | ON |
+---------------+-------+
1 row in set (0.001 sec)
Copy the code