Database atomic operations implementation 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?
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.