BUG REPORT: Transaction Rollback Not Triggered (COMMIT Instead of ROLLBACK)

[maxingjia1314]

BUG REPORT: Transaction Rollback Not Triggered (COMMIT Instead of ROLLBACK)



1. Problem Description

When using activerecord-sqlserver-adapter, transactions are not rolled back as expected when an exception (ActiveRecord::Rollback or standard exceptions) is raised within a transaction block. Instead, the transaction is committed (log shows COMMIT TRANSACTION), leading to data inconsistency.




2. Steps to Reproduce

1. Execute the following code:

ActiveRecord::Base.transaction do
Contract.create!(result) # Assume `result` is valid
AnotherModel.create!(another_result) # Assume `another_result` is valid
raise ActiveRecord::Rollback # Or raise "Test error"
end

2. Observe the logs:

TRANSACTION (2.3ms)  COMMIT TRANSACTION
=> nil

3. Check the database tables to confirm that data was committed (not rolled back).
   

---



3. Expected Behavior

• When an exception is raised inside a transaction block (e.g., ActiveRecord::Rollback or any other exception), the transaction should be rolled back (log shows ROLLBACK TRANSACTION), and no changes should persist in the database.
  

4. Actual Behavior

• The transaction is committed (log shows COMMIT TRANSACTION), and data is persisted in the database despite the exception.
  

---



5. Environment Information



Item	Details
activerecord-sqlserver-adapter version	7.0.7
Rails version	7.0.8.6
Ruby version	3.3.3
SQL Server version	2014
Database Configuration	Provide the SQL Server section from config/database.yml :

production:
  sqlserver:
    adapter: sqlserver
    database: my_databse
    username: user
    password: psword!
    host: 192.168.28.102
    port: 1433
    timeout: 5000
    options: "XACT_ABORT=true" # Setting or not setting it here has no effect





6. Logs

Provide the full log snippet (redact sensitive info):

[Logs before transaction]
TRANSACTION (1.7ms)  BEGIN TRANSACTION
[Database operations]
TRANSACTION (2.3ms)  COMMIT TRANSACTION





7. Solutions Already Tried

• ✅ Replaced manual begin_transaction/commit_transaction with ActiveRecord::Base.transaction.
• ✅ Raised ActiveRecord::Rollback explicitly.
• ✅ Added options: "XACT_ABORT=true" to database.yml.
• ✅ Upgraded activerecord-sqlserver-adapter to the latest version (run bundle update activerecord-sqlserver-adapter).
  

---



8. Additional Notes

---



9. Attachments

---



10. Request for Help

Please help confirm:

1. Is this a known issue with activerecord-sqlserver-adapter?
2. Could this be related to SQL Server's XACT_ABORT configuration or transaction isolation levels?
3. Is further debugging of the adapter's transaction management logic required?
   

[maxingjia1314]

Do you need any additional information? @aidanharan

[aidanharan]

@maxingjia1314 Could you reproduce the issue using a script? See https://github.com/rails-sqlserver/activerecord-sqlserver-adapter/wiki/How-to-report-a-bug#minimal-reproducible-script

[maxingjia1314]

I used the same configuration information as my development environment in the test code, but it reported an error and cannot continue execution:
uninitialized constant ActiveSupport::LoggerThreadSafeLevel::Logger (NameError)

# minimal_repro.rb
require "bundler/inline"

gemfile(true) do
  source 'https://gems.ruby-china.com'
  gem "tiny_tds", "2.1.7"           
  gem "activerecord", '7.0.8.6'  
  gem "activerecord-sqlserver-adapter", "7.0.7"  
end

require "active_record"
require "minitest/autorun"
require "logger"

ActiveRecord::Base.establish_connection(
  adapter:  "sqlserver",
  timeout:  5000,
  pool:     100,
  encoding: "utf8",
  database: "SWtest20240416",
  username: "sa",
  password: "3one@2024!",
  host:     "192.168.28.102",
  port:     1433
)

ActiveRecord::Base.logger = Logger.new(STDOUT)

ActiveRecord::Schema.define do
  drop_table(:bug_tests) rescue nil
  create_table :bug_tests, force: true do |t|
    t.bigint :external_id
    t.string :name
  end
end

class BugTest < ActiveRecord::Base
end

class TestBugTest < Minitest::Test
  def setup
    @bug_test = BugTest.create!(name: "Test Record")
  end
end

[aidanharan]

Include logger gem in your gem file to get it to work.

gemfile(true) do
  source 'https://gems.ruby-china.com'

  gem "logger"

  gem "tiny_tds", "2.1.7"           
  gem "activerecord", '7.0.8.6'  
  gem "activerecord-sqlserver-adapter", "7.0.7"  
end

Also, note that support for v7.0 of the adapter has ended. See https://github.com/rails-sqlserver/activerecord-sqlserver-adapter?tab=readme-ov-file#about-the-adapter