As we all know the convention.
GET Calls: Used to fetch data
POST/PUT/DELETE Calls: Used to update data
It is a general convention that a developer should not do writes while on a GET call.
Frappe also enforces this convention, that whatever is written to DB in a GET call, is rolled back on request end.
Do we need to roll it back??
So, what if we take that responsibility from developer, and force it via framework, to hard-enforce not writing anything on db in a GET call (except for temporary tables of course).
So, let’s introduce.
START TRANSACTION READ ONLY
This is something that that enforces that Writes cannot happen on the transaction that starts with “READ ONLY”. Any Update, Insert or Delete call will throw an error, which might be a good thing, as this enforces the convention, and as a cherry on the top, comes with a few of optimisations(https://dev.mysql.com/doc/refman/5.6/en/innodb-performance-ro-txn.html) as database engine knows it doesn’t need to write anything.
Hence, should we start a transaction with READ ONLY for get calls? This will ensure convention and a better throughput. This can be an Always ON connection as well
CAVEAT: But what will happen to error logs?
As we know, error log, needs to created regardless whether the API is GET or POST or anything, or SUCCESS or FAILURE. Right now i believe, this behaviour is replicated by making
tabError Log MyISAM table, which doesn’t care about txn and ignores rollback, hence data persists even if an error is thrown and transaction is rolled back.
To emulate this, we can have an always on, autocommit=1 (can be lazy loaded, and reconnect on error) connection, dedicated for writing error logs and updating session (last login). This will ensure even if the original connection rolls back, error log is still written.
So what does the community thinks of this idea?
EDIT: GET calls mostly include calls to get report, get list, and get form and a bunch of internal APIs which don’t write anything. So, let’s optimise them a bit more