Database atomic operations implementation

database-internalstransaction

The question is about queries that are not wrapped in 'begin-commit' block, but about plain inserts and updates that are atomic in PostgreSQL, MySQL (innodb engine at least). So how is this implemented internally?

Best Answer

A transaction is started for each statement that occurs outside of an explicit transaction block. Whether a commit is automatically issued following the statement is dependent on the RDBMS configuration. MySQL has the autocommit option, SQL Server has IMPLICIT_TRANSACTIONS, PostgreSQL is always auto commit.

PostgreSQL:

In the standard, it is not necessary to issue START TRANSACTION to start a transaction block: any SQL command implicitly begins a block. PostgreSQL's behavior can be seen as implicitly issuing a COMMIT after each command that does not follow START TRANSACTION (or BEGIN), and it is therefore often called "autocommit". Other relational database systems might offer an autocommit feature as a convenience.

InnoDB:

In InnoDB, all user activity occurs inside a transaction. If autocommit mode is enabled, each SQL statement forms a single transaction on its own. By default, MySQL starts the session for each new connection with autocommit enabled, so MySQL does a commit after each SQL statement if that statement did not return an error.

SQL Server:

SQL Server operates in the following transaction modes.

Autocommit transactions - Each individual statement is a transaction.

Explicit transactions - Each transaction is explicitly started with the BEGIN TRANSACTION statement and explicitly ended with a COMMIT or ROLLBACK statement.

Implicit transactions - A new transaction is implicitly started when the prior transaction completes, but each transaction is explicitly completed with a COMMIT or ROLLBACK statement.

Related Solutions

Sql-server – T-SQL – Enforce Atomic Operations x Locks

There are two different isolation levels you can use within a session that will insure that the data read at the beginning of the transaction doesn't change until the transaction with one of the isolation levels mentionned below is committed

BOL article on Isolation Levels

Serializable

Statements cannot read data that has been modified but not yet committed by other transactions

No other transactions can modify data that has been read by the current transaction until the current transaction completes.

Other transactions cannot insert new rows with key values that would fall in the range of keys read by any statements in the current transaction until the current transaction completes.

REPEATABLE READ

Specifies that statements cannot read data that has been modified but not yet committed by other transactions and that no other transactions can modify data that has been read by the current transaction until the current transaction completes.

The main difference between the two, is that the serializable isolation level prevents inserting new data anywhere within the range of rows.

USE MYDATABASE
GO

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
GO
BEGIN TRANSACTION;
sp_insert_operation
COMMIT TRANSACTION

There is also the option of setting snapshot isolation at the database level, but I don't believe that would give you the level of isolation you are looking for.

Here is the relevant documentation ALTER DATABASE look under snapshot isolation

Update on Serializable

http://blogs.msdn.com/ Serializable isolation level relies on pessimistic concurrency control. It guarantees consistency by assuming that two transactions might try to update the same data and uses locks to ensure that they do not but at a cost of reduced concurrency - one transaction must wait for the other to complete

Mongodb – About transactions on mongodb

Copying Joao's answer (from question comments) so this Q can have an A.

No, there is no problem with that. Single inserts/updates are atomic and reads are always consistent. However, you don't have transaction blocks in MongoDB (executing multiple inserts/updates atomically). – joao Feb 24 '14 at 13:26

Best Answer

Related Solutions

Sql-server – T-SQL – Enforce Atomic Operations x Locks

Mongodb – About transactions on mongodb

Related Question