All database statements are executed within the context of a physical transaction, even when we don’t explicitly declare transaction boundaries (BEGIN/COMMIT/ROLLBACK). If you don’t declare the transaction boundaries, then each statement will have to be executed in a separate transaction. This may even lead to opening and closing one connection per statement. Declaring a service … Read more
The exact same page explains what a dirty read is: Accessing an updated value that has not been committed is considered a dirty read because it is possible for that value to be rolled back to its previous value. If you read a value that is later rolled back, you will have read an invalid … Read more
There’s now a method disable_ddl_transaction! that allows this, e.g.: class AddIndexesToTablesBasedOnUsage < ActiveRecord::Migration disable_ddl_transaction! def up execute %{ CREATE INDEX CONCURRENTLY index_reservations_subscription_id ON reservations (subscription_id); } end def down execute %{DROP INDEX index_reservations_subscription_id} end end
There are many possible causes for serialization failures. Technically, a deadlock between two transactions is one form of serialization failure, and can potentially happen at any isolation level if there are concurrent modifications to the schema (database structure). Since you’re asking about PostgreSQL, you should be aware that in PostgreSQL this type of serialization failure … Read more
They are not only different, they are orthogonal. Inconsistency: A DB that reported employee Joe Shmoe’s department as Sales but that didn’t list Joe Shmoe among the employees in the Sales department would be inconsistent. It’s a logical property of the DB, independent of the actual data. Integrity: A DB that reported jOe SaleS to … Read more
This is defined behaviour. From the docs: Any RuntimeException triggers rollback, and any checked Exception does not. This is common behaviour across all Spring transaction APIs. By default, if a RuntimeException is thrown from within the transactional code, the transaction will be rolled back. If a checked exception (i.e. not a RuntimeException) is thrown, then … Read more
Take a look at http://dev.mysql.com/doc/refman/5.0/en/declare-handler.html Basically you declare error handler which will call rollback START TRANSACTION; DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN ROLLBACK; EXIT PROCEDURE; END; COMMIT;
From Wikipedia (which has great and detailed examples for this): A non-repeatable read occurs, when during the course of a transaction, a row is retrieved twice and the values within the row differ between reads. and A phantom read occurs when, in the course of a transaction, two identical queries are executed, and the collection … Read more
I know most databases have restrictions, but Postgres doesn’t. You can run any number table creations, column changes and index changes in a transaction, and the changes aren’t visible to other users unit COMMIT succeeds. That’s how databases should be! 🙂 As for SQL Server you can run DDL inside of a transaction, but SQL … Read more
I always wrap a transaction in a using statement. using(IDbTransaction transaction ) { // logic goes here. transaction.Commit(); } Once the transaction moves out of scope, it is disposed. If the transaction is still active, it is rolled back. This behaviour fail-safes you from accidentally locking out the database. Even if an unhandled exception is … Read more