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?
1 Answer
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
SERIALIZABLElevel won’t tolerate Phantoms, it’s especially indicated for transactions which contain multipleSELECTstatements, 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 defaultSERIALIZABLElevel. Nevertheless, we suspect thatSERIALIZABLEis 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.
-
"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)– SteveCommented Mar 31 at 12:34
-
1The 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)– SteveCommented Mar 31 at 12:34
-
2@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. Commented 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)– SteveCommented Mar 31 at 17:53
-
1I 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)– SteveCommented Mar 31 at 17:53
SNAPSHOT(aka MVCC) orSERIALIZABLEare the only two levels that should ever have been invented, along with an administrator/debugging onlyREAD UNCOMMITTED.READ COMMITTEDversusREPEATABLE READand notREAD UNCOMMITTED. To the OP,READ COMMITTEDis less expensive thanREPEATABLE 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-levelservers to do it! History, with numerous MySQL gotchas have proved this! Just my 02c! p.s. Oracle also uses the default isolation level ofREAD COMMITTED!SNAPSHOTin some other systems.