Feb 18th, 2022 - written by Kimserey with .
When using DBeaver, we can choose betweeen using auto commit or manual commit modes when executing SQL scripts. In today’s post, we will look at how we can switch between the two and how transactions can be managed.
Auto-commit is the default mode in development and test connections. For every query executed, the changes will directly be applied to the database.
The connection type is specified in the general settings on connection creation:
In development and test, we can see the connection status is
For any sql query, the changes will automatically be applied to the database:
1 2 3 4 5 create table User ( id integer primary key autoincrement, name text not null, age integer );
After executing this query, we can directly see that the table was created on the database.
To switch to a manual commit mode, we can click on the transaction mode button:
which then switch to transaction mode:
From now onward any statement executed will be ran within the global transaction.
For example if we execute the following two queries:
1 2 insert into User (name, age) values ('kim', 10); insert into User (name, age) values ('tom', 10);
We can see that the transaction logs have increased to 2:
If we want to apply the changes, we must commit the changes with the commit button:
If we omit to commit or rollback, the transaction will still be open on the connection hence we could accumulate changes across multiple sql scripts tabs and commit at the end.
DBeaver will remind us that a transaction is in process if we try to close the connection.
In production mode, manual commit mode is set by default. This prevent accidental production data modification.
And that concludes today’s post on the differences between auto-commit and manual commit modes in DBeaver!
Today we looked at DBeaver auto-commit and manual commit feature. We started by looking at the behaviour of auto-commit which is the default behaviour for development and test connections. Then we moved on to looking at manual commit mode which is the default for production connection. I hope you liked this post and I’ll see you on the next one!