1

AFAIK, in MS SQL, PostgreSQL, Oracle and probably others, the default transaction isolation level is READ COMMITTED. Why in MySQL and MariaDB it is REPEATABLE READ?

5
  • 1
    READ COMMITTED is optimal in most cases. Opinion-based.
    – Akina
    Mar 29 at 10:27
  • 1
    Either SNAPSHOT (aka MVCC) or SERIALIZABLE are the only two levels that should ever have been invented, along with an administrator/debugging only READ UNCOMMITTED. Mar 31 at 2:39
  • The question is why top-level SQL servers have READ COMMITTED as default and in MySQL/MariaDB it is REPEATABLE READ.
    – i486
    Mar 31 at 7:43
  • 1
    @Charlieface - the question is about READ COMMITTED versus REPEATABLE READ and not READ UNCOMMITTED. To the OP, READ COMMITTED is less expensive than REPEATABLE READ, so better to use it, unless you have a good reason not to! Just because MySQL/MariaDB does something is (very, very much) NOT a reason for, as you say, top-level servers to do it! History, with numerous MySQL gotchas have proved this! Just my 02c! p.s. Oracle also uses the default isolation level of READ COMMITTED!
    – Vérace
    Apr 7 at 10:21
  • @Vérace I was primarily discussing the other two isolation levels, and saying those are the only ones you should use. The fact it's less expensive to use a lower level isn't really a good argument: for the same price you might as well not lock at all. Clearly locking needs to account for all possible race conditions. In some circumstances the logic might allow for a lower level to be used, but you'd need to analyze the possible interactions to be sure. Oracle, MySQL and some others actually use MVCC, so it's the equivalent of SNAPSHOT in some other systems. Apr 7 at 12:21

1 Answer 1

1

According to the ANSI/ISO SQL-99 standard, SERIALIZABLE is the default transaction isolation level.

https://sql-99.readthedocs.io/en/latest/chapters/37.html says:

Since the SERIALIZABLE level won’t tolerate Phantoms, it’s especially indicated for transactions which contain multiple SELECT statements, or for when you don’t know what the statements will be, as in dynamic SQL. It is the only level which assures safe, error-free transactions every time. If your application consists wholly of short (i.e. fast-executing) statements which affect only a few records at a time, don’t get fancy – leave everything at the default SERIALIZABLE level. Nevertheless, we suspect that SERIALIZABLE is used somewhat more often than appropriate. The choice of isolation level is something you should at least give a moment’s thought to, on a case-by-case basis.

In MySQL, the default of REPEATABLE READ is similar to SERIALIZABLE but supports non-locking reads. So it's a small compromise to allow greater concurrency.

If you think READ COMMITTED is the best for your application, go ahead and use it, and set it to the default level if you want.

5
  • "If you think READ COMMITTED is the best" - the whole point of the transaction manager is ideally to relieve the programmer from the need to routinely analyse concurrency concerns. There's perhaps never a program-logic reason for transactions to be anything but fully-isolated. The reasons for backing off from total isolation (all the way down to read-uncommitted) is typically the overall performance overhead of providing the isolation, rather than the desirability of non-isolation. (1/2)
    – Steve
    Mar 31 at 12:34
  • 1
    The vast majority of developers would not even be in a position to evaluate what the exact effects of non-isolation could be on a particular query (given the wide variety of possible query plans), or to assess the relative likelihood of those effects against the performance benefits. Advising developers to "use what is best" here is like advising the annoying child to "go and play with traffic". (2/2)
    – Steve
    Mar 31 at 12:34
  • 1
    @Steve That's a pretty dismal view of the responsibility and the professional skill of developers. But if you're right, then I fear for the future of a civilization that has become so dependent as ours has on software-managed services. Mar 31 at 14:34
  • I think it's much simpler than that Bill. Transaction managers are supposed to provide total isolation, so that concurrent execution is enabled for performance reasons, without the programmer's analysis having to become global across all potential algorithms and potential schedules of execution. There was never a time when programmers did perform or would have performed this global analysis - they would either have manually imposed locks to exclude concurrent execution between algorithms analysed to be conflicting, or they would have had all execution be serial by default. (1/2)
    – Steve
    Mar 31 at 17:53
  • 1
    I think as well, a lot of these inferior isolation modes were devised in an era when long-running cursor/RBAR operations were more common on much slower hardware, where the programmer has far more control over the structure of the algorithm that executes, and where interleaved, non-serialisable execution (of relatively simple algorithms) may in fact make good sense (and be within the analytical capability of a programmer to design correctly). But I think these circumstances apply far less often nowadays. (2/2)
    – Steve
    Mar 31 at 17:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.