A key concept here is that real databases have transactions, there's some concurrency between transactions, and that in the more efficient modes, transactions can deadlock, fail, and be rolled back.

In MySQL, a statement within a transaction can return "ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction". This can be forced by the following sequence of events. (This assumes an InnoDB table in Repeatable Read mode.)

    Process A does a START TRANSACTION.
    Process B does a START TRANSACTION.
    Process A does a SELECT which reads row X.
    Process B does a SELECT which reads row X.  
    Process A does an UPDATE which writes row X.
    Process B does an UPDATE which writes row X.  - Deadlock error.

Process B gets a report that the transaction failed, and everything done in B's transaction is rolled back. The entire transaction has to be retried. Transactions are atomic if they commit, but can fail in a deadlock situation.

A SELECT does lock parts of the database. "Repeatable read" means that if you read the same data item twice within the same transaction, you get the same result, even if someone else is changing the data. This requires locking. If you try to update the data in conflict with another process, you'll get a deadlock error, but if you COMMIT a select-only transaction, you won't. You do have to COMMIT select-only transactions, or you'll fill memory with locks and stall out updates.

Ref: http://dev.mysql.com/doc/refman/5.1/en/innodb-lock-modes.htm...