MySQL Transaction Isolation Level
Explanation
XWiki uses the READ COMMITTED transaction isolation level on MySQL and MariaDB, rather than the REPEATABLE READ level these databases default to. This page explains why, what it requires from the database server, and how to set another level.
Why READ COMMITTED
What a request sees of the changes that other requests make at the same time depends on the isolation level. XWiki therefore uses the same level on every database, rather than each database's own default, so that the correctness of its code has to be reasoned about for one level only. That level is READ COMMITTED, at which every read sees the data committed when it runs. It is also the default level of PostgreSQL, Oracle and HSQLDB.
At REPEATABLE READ, every read of a transaction sees the database as it was at the transaction's first read instead, which XWiki's code doesn't expect. For example, when XWiki loads a page, it loads the classes of the page's objects in the same database transaction, so a class that another request saves while the page is loading is invisible to that load. XWiki then caches the class as missing, or in its previous version, and keeps it that way until the class is saved again or the wiki restarts: forms show no fields and sheets display empty values, although the class is intact in the database. This happens mostly when classes are saved on a wiki that is serving requests, for example while an extension is installed or upgraded.
Which Level XWiki Uses
XWiki 17.10.14+, 18.4.6+, 18.8.0+ XWiki uses READ COMMITTED unless the hibernate.connection.isolation property of WEB-INF/hibernate.cfg.xml sets another level. XWiki applies that level to every database connection it opens, so the server's transaction_isolation setting has no effect on it.
XWiki <17.10.14, <18.4.6, <18.8.0 XWiki uses the level set by the hibernate.connection.isolation property of WEB-INF/hibernate.cfg.xml and, when the property is not set, the level the database server defaults to: REPEATABLE READ, unless the server's transaction_isolation setting says otherwise. Configure MySQL shows the property value that makes XWiki use READ COMMITTED.
Binary Logging
InnoDB cannot record a write made at READ COMMITTED in a statement-based binary log, so a server configured with binlog_format=STATEMENT rejects every write XWiki makes, as described in "Impossible to write to binary log" MySQL Error. Binary logging therefore has to be either disabled or in the ROW or MIXED format. Both databases default to one of these: MySQL logs in the ROW format and MariaDB in the MIXED format.
Setting Another Level
The hibernate.connection.isolation property takes the JDBC number of the level:
| Value | Isolation level |
|---|---|
| 1 | READ UNCOMMITTED |
| 2 | READ COMMITTED |
| 4 | REPEATABLE READ |
| 8 | SERIALIZABLE |
For example, to keep REPEATABLE READ, add the following property to WEB-INF/hibernate.cfg.xml and restart XWiki:
<property name="hibernate.connection.isolation">4</property>Only do this to work around a problem, such as a binary log that has to stay statement-based: XWiki's code is not designed for another level, which can cause problems such as the class caching described above.
