Path: | doc/transactions.rdoc |
Last Update: | Wed Apr 17 08:46:45 +0000 2013 |
Sequel uses autocommit mode by default for all of its database adapters, so in general in Sequel if you want to use database transactions, you need to be explicit about it. There are a few cases where transactions are used implicitly by default:
Everywhere else, it is up to use to use a database transaction if you want to.
In Sequel, the Database#transaction method should be called if you want to use a database transaction. This method must be called with a block. If the block does not raise an exception, the transaction is committed:
DB.transaction do # BEGIN DB[:foo].insert(1) # INSERT end # COMMIT
If the block raises a Sequel::Rollback exception, the transaction is rolled back, but no exception is raised outside the block:
DB.transaction do # BEGIN raise Sequel::Rollback end # ROLLBACK # no exception raised
If any other exception is raised, the transaction is rolled back, and the exception is raised outside the block:
# ArgumentError raised DB.transaction do # BEGIN raise ArgumentError end # ROLLBACK # ArgumentError raised
If you want Sequel::Rollback exceptions to be reraised, use the :rollback => :reraise option:
DB.transaction(:rollback => :reraise) do # BEGIN raise Sequel::Rollback end # ROLLBACK # Sequel::Rollback raised
If you always want to rollback (useful for testing), use the :rollback => :always option:
DB.transaction(:rollback => :always) do # BEGIN DB[:foo].insert(1) # INSERT end # ROLLBACK
If you want to check whether you are currently in a transaction, use the Database#in_transaction? method:
DB.in_transaction? # false DB.transaction do DB.in_transaction? # true end
You can add hooks to an in progress transaction that are called after the transaction commits or rolls back:
x = nil DB.transaction do DB.after_commit{x = 1} DB.after_rollback{x = 2} x # nil end x # 1 x = nil DB.transaction do DB.after_commit{x = 1} DB.after_rollback{x = 2} raise Sequel::Rollback end x # 2
You can nest calls to transaction, which by default just reuses the existing transaction:
DB.transaction do # BEGIN DB.transaction do DB[:foo].insert(1) # INSERT end end # COMMIT
You can use the :savepoint => true option in the inner transaction to explicitly use a savepoint (if the database supports it):
DB.transaction do # BEGIN DB.transaction(:savepoint => true) do # SAVEPOINT DB[:foo].insert(1) # INSERT end # RELEASE SAVEPOINT end # COMMIT
If a Sequel::Rollback exception is raised inside the savepoint block, it will only rollback to the savepoint:
DB.transaction do # BEGIN DB.transaction(:savepoint => true) do # SAVEPOINT raise Sequel::Rollback end # ROLLBACK TO SAVEPOINT # no exception raised end # COMMIT
Other exceptions, unless rescued inside the outer transaction block, will rollback the savepoint and the outer transactions, since they are reraised by the transaction code:
DB.transaction do # BEGIN DB.transaction(:savepoint => true) do # SAVEPOINT raise ArgumentError end # ROLLBACK TO SAVEPOINT end # ROLLBACK # ArgumentError raised
Sequel supports database prepared transactions on PostgreSQL, MySQL, and H2. With prepared transactions, at the end of the transaction, the transaction is not immediately committed (it acts like a rollback). Later, you can call commit_prepared_transaction to commit the transaction or rollback_prepared_transaction to roll the transaction back. Prepared transactions are usually used with distributed databases to make sure all databases commit the same transaction or none of them do.
To use prepared transactions in Sequel, you provide a string as the value of the :prepare option:
DB.transaction(:prepare => 'foo') do # BEGIN DB[:foo].insert(1) # INSERT end # PREPARE TRANSACTION 'foo'
Later, you can commit the prepared transaction:
DB.commit_prepared_transaction('foo')
or roll the prepared transaction back:
DB.rollback_prepared_transaction('foo')
The SQL standard supports 4 isolation levels: READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, and SERIALIZABLE. Not all databases implement the levels as specified in the standard (or implement the levels at all), but on PostgreSQL, MySQL, and Microsoft SQL Server, you can specify which transaction isolation level you want to use via the :isolation option to Database#transaction. The isolation level is specified as one of the following symbols: :uncommitted, :committed, :repeatable, and :serializable. Using this option make Sequel use the correct transaction isolation syntax for your database:
DB.transaction(:isolation => :serializable) do # BEGIN # SET TRANSACTION ISOLATION LEVEL SERIALIZABLE DB[:foo].insert(1) # INSERT end # COMMIT