db/webapi: Run all transactions at an isolation level of Serializable

Modify both the ‘db’ and the ‘webapi’ versions of ‘runQuery’ to use the ‘Serializable’ isolation level which is the most strict.

Before this change queries returning a ‘CAddressSummary’ struct would sometimes return negative balances although this was actually a consequence of previous database corruption.

See: https://www.postgresql.org/docs/current/transaction-iso.html

View on GitHub
File Changes

                      
import           Database.Persist.Postgresql (withPostgresqlConn, openSimpleConn)
import           Database.PostgreSQL.Simple (connectPostgreSQL)
-
import           Database.Persist.Sql (SqlBackend, runSqlConn)
+
import           Database.Persist.Sql (SqlBackend, IsolationLevel (..), runSqlConnWithIsolation)

                      
import           Database.Esqueleto
import           Database.Esqueleto.Internal.Sql
    pgconf <- readPGPassFileEnv
    runHandleLoggerT .
      withPostgresqlConn (toConnectionString pgconf) $ \backend ->
-
        -- The 'runSqlConn' function starts a transaction, runs the 'dbAction'
+
        -- The 'runSqlConnWithIsolation' function starts a transaction, runs the 'dbAction'
        -- and then commits the transaction.
-
        runSqlConn dbAction backend
+
        runSqlConnWithIsolation dbAction backend Serializable
  where
    runHandleLoggerT :: LoggingT m a -> m a
    runHandleLoggerT action =
    pgconf <- readPGPassFileEnv
    (runIohkLogging tracer) .
      withPostgresqlConn (toConnectionString pgconf) $ \backend ->
-
        runSqlConn dbAction backend
+
        runSqlConnWithIsolation dbAction backend Serializable

                      
runIohkLogging :: Trace IO Text -> LoggingT m a -> m a
runIohkLogging tracer action =
  pgconfig <- readPGPassFileEnv
  runNoLoggingT .
    withPostgresqlConn (toConnectionString pgconfig) $ \backend ->
-
      runSqlConn action backend
+
      runSqlConnWithIsolation action backend Serializable

                      
-- | Run a DB action with stdout logging. Mainly for debugging.
runDbStdoutLogging :: ReaderT SqlBackend (LoggingT IO) b -> IO b
runDbStdoutLogging action = do
  pgconfig <- readPGPassFileEnv
  runStdoutLoggingT .
    withPostgresqlConn (toConnectionString pgconfig) $ \backend ->
-
      runSqlConn action backend
+
      runSqlConnWithIsolation action backend Serializable

                      
-- from Control.Monad.Logger, wasnt exported
defaultOutput :: Handle
import           Data.Time.Clock.POSIX (POSIXTime, utcTimeToPOSIXSeconds)
import           Data.Word (Word16, Word64)

                      
-
import           Database.Persist.Sql (SqlBackend, runSqlConn)
+
import           Database.Persist.Sql (IsolationLevel (..), SqlBackend, runSqlConnWithIsolation)

                      
import           Explorer.DB (Block (..), TxId)

                      

                      
runQuery :: MonadIO m => SqlBackend -> ReaderT SqlBackend IO a -> m a
runQuery backend query =
-
  liftIO $ runSqlConn query backend
+
  liftIO $ runSqlConnWithIsolation query backend Serializable

                      
slotsPerEpoch :: Word64
slotsPerEpoch = k * 10