Anyone who has used oracle or mysql knows that in SQLplus or mysql, if you find that you are doing something wrong, you can rollback; but in PostgreSQL's psql, if you execute a dml and do not run begin first, you will submit it immediately as soon as the execution is completed and cannot rollback. This can easily lead to misoperation. Is there any way to avoid this risk?
Of course, in psql, automatic submission is turned on by default. We can turn off automatic submission, as follows:
Set AUTOCOMMIT off
test=# create table test1 (x int);
CREATE TABLE
Time: 0.593 ms
test=# select * from test1;
x
---
(0 rows)
Time: 0.309 ms
test=# rollback;
ROLLBACK
Time: 1.501 ms
test=# select * from test1;
ERROR: relation "test1" does not exist
LINE 1: select * from test1;
^
Time: 0.376 ms
What we need to note here is that DDL transactions that are not synchronized with Oracle and PG can also be rolled back, and there is no concept of implicit commit. We need to pay attention to this
test=# \d
List of relations
Schema | Name | Type | Owner
--------+------+-------+-------
public | foo | table | kiwi
public | test | table | kiwi
(2 rows)
Supplement: PostgreSQL-Transactions and commit optimization
Basic concepts
Transaction Transaction is a logical unit in the execution process of the database management system DBMS, and is a sequence of SQL commands.
Its characteristic is that when a transaction is submitted to the DBMS, the DBMS needs to ensure that all operations are completed; if some operations in the transaction are not completed successfully, all operations will be rolled back and rolled back to the state before the transaction is committed.
property
Transactions have the following four standard properties
Atomicity: a transaction is executed as a whole, equivalent to an atom
Consistency: Ensure that the database meets the constraints before and after modification
Isolation: Multiple transactions can be executed concurrently without affecting each other
Persistence: The database modifications to the committed transaction should be permanently saved in the database.
Applicable scenarios
Someone pays 100 yuan in a store using electronic currency, including the following two actions:
1. Consumer account reduction of 100 yuan
2. Merchant account will increase by 100 yuan
The function of a transaction is to ensure that both operations either occur or do not occur, otherwise 100 yuan may disappear out of thin air.
Transaction control
Use the following command to control transactions
begin or begin transaction: start a transaction
commit or end transaction: commit transaction, execute a series of SQL
rollback: transaction rollback
After starting a transaction, the transaction will not be executed unless the commit or rollback command is encountered;
If you have not encountered commit or rollback, an exception occurs in the database and will automatically roll back.
Note that transaction commands can only be used for insert, delete, and update operations, while other commands, such as table creation and table deletion, will be automatically submitted.
To sum up: transactions need to be manually started and submitted manually; and this method can improve operational efficiency.
Example
Assume that there is the following table
id | name | age | address | salary ----+-------+-----+-----------+-------- 1 | Paul | 32 | California| 20000 2 | Allen | 25 | Texas | 15000 3 | Teddy | 23 | Norway | 20000 4 | Mark | 25 | Rich-Mond | 65000 5 | David | 27 | Texas | 85000 6 | Kim | 22 | South-Hall| 45000 7 | James | 24 | Houston | 10000
Operation 1: Start a transaction, delete the record with age 25 from the table, and finally use rollback to undo all operations
runoobdb=# BEGIN; DELETE FROM COMPANY WHERE AGE = 25; ROLLBACK;
The results are as follows
id | name | age | address | salary ----+-------+-----+-----------+-------- 1 | Paul | 32 | California| 20000 2 | Allen | 25 | Texas | 15000 3 | Teddy | 23 | Norway | 20000 4 | Mark | 25 | Rich-Mond | 65000 5 | David | 27 | Texas | 85000 6 | Kim | 22 | South-Hall| 45000 7 | James | 24 | Houston | 10000
We found that the original table has not changed
Operation 2: Start a transaction, delete the record with age 25 from the table, and finally submit the transaction with commit
runoobdb=# BEGIN; DELETE FROM COMPANY WHERE AGE = 25; COMMIT;
At this time, we found that the table with age of 25 has been deleted.
Python examples
()
conn = (host='172.16.89.80',user="postgres",password="postgres",database="postgres")
cur = ()
("BEGIN TRANSACTION") # Start a transactionif __name__=='__main__':
for i in range(0,1000):
('INSERT INTO test(a, b, c, d) VALUES (%d, %d, %d, %d);'%(i, i, i, i))
('commit') # Submit transaction ()
()
print(())
Successful execution, taking about 2 seconds
Keep trying
The transaction started manually above. I made the following attempt later and found that it took only 1s [commit optimization]
()
conn = (host='172.16.89.80',user="postgres",password="postgres",database="postgres")
cur = ()
if __name__=='__main__':
for i in range(0,1000):
('INSERT INTO test(a, b, c, d) VALUES (%d, %d, %d, %d);'%(i, i, i, i))
()
()
()
print(())
I executed a series of SQL and finally made a commit. The execution was also successful and took less time. I guess that Python automatically started the transaction and submitted it with the commit command without starting manually. [This guess will be verified when you have time later]
The above is personal experience. I hope you can give you a reference and I hope you can support me more. If there are any mistakes or no complete considerations, I would like to give you advice.