3

We use Azure Data Studio for our SQL Server queries. However, it automatically commits everything and I cannot find an option to disable this. I found this page https://learn.microsoft.com/en-us/sql/odbc/reference/develop-app/setting-the-commit-mode?view=sql-server-ver15 but I cannot find any place in the connection settings where I can set the SQL_ATTR_AUTOCOMMIT attribute

Martijn Van Loocke
  • 433
  • 2
  • 6
  • 17
  • 1
    Perhaps the better solution would be, if you do need to wrap everything in a single transaction, is explicitly declare your transaction(s). – Thom A Jan 07 '21 at 14:11
  • Really bad idea to turn it off as you would have hanging transactions very often. Use explicit transactions – Charlieface Jan 07 '21 at 14:18
  • 1
    What about when you are trying a query out that changes data and you come to the conclusion that it's not doing what you want it to? You cannot rollback because the transaction has already been committed. Humans make mistakes... – Martijn Van Loocke Jan 07 '21 at 14:20
  • So you would rather have everyone else getting deadlocks, @MartijnVanLoocke ? Check your work before you make changes tom data; that's one of the basics. – Thom A Jan 07 '21 at 14:22
  • I don't try out things on production servers. This is on my local server where I am the only user. – Martijn Van Loocke Jan 07 '21 at 14:23
  • Just because it's a local environment doesn't mean you can use bad habits, @MartijnVanLoocke . – Thom A Jan 07 '21 at 14:24
  • Yes, I'm sure you write perfect code all the time and never make mistakes. Do you commit straight to master with git too? Anyway. The answer seems to be that it's not possible and I need to do it manually with each connection. – Martijn Van Loocke Jan 07 '21 at 14:25
  • By the way, we recently moved from Oracle to SQL Server and SQLDeveloper has the option to not autocommit by default as well and a simple commit and rollback button at the top of the screen. That is what I was expecting here too but maybe SQLServer just handles transactions completely differently from Oracle? – Martijn Van Loocke Jan 07 '21 at 14:35
  • *"Yes, I'm sure you write perfect code all the time and never make mistakes."* With that response, you have clearly missed the points we are making here. I didn't say I don't make mistakes, I just said I don't use a local environment to make poor choices or develop bad habits; very different things. – Thom A Jan 07 '21 at 15:32

1 Answers1

4

Autocommit is the default behavior in SQL Server, to turn it off set implicit_transactions on:

Sets the BEGIN TRANSACTION mode to implicit, for the connection.

When ON, the system is in implicit transaction mode. This means that if @@TRANCOUNT = 0, any of the following Transact-SQL statements begins a new transaction. It is equivalent to an unseen BEGIN TRANSACTION being executed first:

  • ALTER TABLE
  • FETCH
  • REVOKE
  • BEGIN TRANSACTION
  • GRANT
  • SELECT (See exception below.)
  • CREATE
  • INSERT
  • TRUNCATE TABLE
  • DELETE
  • OPEN
  • UPDATE
  • DROP

When OFF, each of the preceding T-SQL statements is bounded by an unseen BEGIN TRANSACTION and an unseen COMMIT TRANSACTION statement. When OFF, we say the transaction mode is autocommit. If your T-SQL code visibly issues a BEGIN TRANSACTION, we say the transaction mode is explicit.

So just open a query window (session) and run:

set implicit_transactions on

In Azure Data Studio you can set this by default here:

enter image description here

But if you do, and you're writing a reusable script, remember to add the setting to the script itself so another user doen't run it with different transaction behavior.

David Browne - Microsoft
  • 80,331
  • 6
  • 39
  • 67
  • 1
    So I have to do that manually every time I open a connection? I highly disagree having the setting to disable it is a bad feature. I used to work in SQLDeveloper with Oracle DB and there I would commit manually. And If I wanted to create a script that commit as part of it, I add "commit;" and that's enough. Today I accidentally deleted rows and had no way to roll back because of this. – Martijn Van Loocke Jan 07 '21 at 14:15
  • On Windows you can use SSMS, which does have this setting. https://learn.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms?view=sql-server-ver15 – David Browne - Microsoft Jan 07 '21 at 14:20
  • 1
    Yeah, I saw that. But it's a much more complicated tool and Microsoft recommends moving over to Azure Data Studio. So I would expect it to offer the same options. – Martijn Van Loocke Jan 07 '21 at 14:22
  • Microsoft does not "recommend moving over to Azure Data Studio" SSMS continues to be under active development, and has a huge user base. https://learn.microsoft.com/en-us/sql/azure-data-studio/faq?view=sql-server-ver15#now-that-theres-azure-data-studio-does-microsoft-plan-to-deprecate-ssms-and-ssdt But you can request new features for Data Studio here: https://github.com/microsoft/azuredatastudio/issues – David Browne - Microsoft Jan 07 '21 at 14:27
  • About the recommendation, see https://learn.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms?view=sql-server-ver15 the first "important" block. But thanks for the help. At least I have a way to do it now. – Martijn Van Loocke Jan 07 '21 at 14:32
  • 1
    It's there. I just missed it. See updated answer. – David Browne - Microsoft Jan 07 '21 at 14:35
  • Ok, thanks. I'm beginning to get the impression that I need to clean up all the transactions that were made while it was implicit and not just a single commit. I was hoping for a single transaction that I then rollback or commit – Martijn Van Loocke Jan 07 '21 at 14:39
  • You have to COMMIT as many times as you ran BEGIN TRANSACTION, explicitly or implicitly. This also differs from Oracle, which avoids this complexity by simply not having a BEGIN TRANSACTION statement. `select @@trancount` will show the nesting level. But a single ROLLBACK always rolls back the transaction. Oracle also autocommits DDL, which SQL Server doesn't do. Being able to run DDL in a transaction is a very useful feature. – David Browne - Microsoft Jan 07 '21 at 14:40