Feature: Allow dirty reads (SQL READ_UNCOMMITTED / NOLOCK)

[Bakotec (BartKofoed)]

Hi all,

First of all, thank you for providing this very useful tool.

I'm building an AI agent that accesses a SQL database through MCP. This database is also used by an ERP application that runs 24/7. Some of the tables are quite large, so queries issued through MCP can take a while to complete — and since they run under the default READ COMMITTED isolation level, they end up blocking other operations on the database for that duration. RCSI isn't enabled on this database, and we're reluctant to enable it, since it would change read behavior for the ERP application as well, not just for the AI agent.

The AI agent is mainly used for reporting and insights, so occasional dirty reads are an acceptable tradeoff for us. My suggestion would be to add an option for READ UNCOMMITTED / WITH (NOLOCK), so that MCP queries can explicitly opt into dirty reads and reduce their impact on the ERP system's concurrent operations.

I'd appreciate your thoughts and ideas on this.

Kind regards,
Bart

[aaronburtle]

Hi Bakotec (@BartKofoed),

Thanks for the detailed explanation. This sounds like a reasonable feature request, as DAB does not current provide a configuration options to set the isolation level or otherwise add WITH (NOLOCK) to SQL generated via the built-in MCL tools. However, I can not make any promises about how quickly something like this will be able to be added, however, if you are able to get the work started here we are always happy to help external contributors get PRs across the finish line.

That said, in the meantime, there are two workable approaches I can think of.

1. Expose reporting views containing NOLOCK

This preserves the flexibility of read_records and aggregate_records, including filtering, sorting, field selection, and pagination.

CREATE SCHEMA mcp_reporting;
GO

CREATE VIEW mcp_reporting.Orders
AS
SELECT
    OrderId,
    CustomerId,
    OrderDate,
    Status,
    Total
FROM dbo.Orders WITH (NOLOCK);
GO

For a view containing joins, apply the hint to every underlying table. Then expose the view rather than the ERP table:

"OrdersReporting": {
  "source": {
    "object": "mcp_reporting.Orders",
    "type": "view",
    "key-fields": [ "OrderId" ]
  },
  "rest": {
    "enabled": false
  },
  "graphql": {
    "enabled": false
  },
  "mcp": {
    "dml-tools": true
  },
  "permissions": [
    {
      "role": "authenticated",
      "actions": [
        {
          "action": "read"
        }
      ]
    }
  ]
}

Views require one or more stable, unique [key-fields]. A dedicated reporting schema also lets you grant the DAB identity access to the views without granting it direct access to the ERP tables.

2. Expose purpose-built reporting procedures as MCP custom tools

For more expensive reports, a stored procedure offers better control over parameters, indexes, and query shape:

CREATE PROCEDURE mcp_reporting.GetOrderSummary
    @FromDate datetime2,
    @ToDate datetime2
AS
BEGIN
    SET NOCOUNT ON;
    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

    SELECT
        CustomerId,
        COUNT(*) AS OrderCount,
        SUM(Total) AS TotalValue
    FROM dbo.Orders
    WHERE OrderDate >= @FromDate
      AND OrderDate < @ToDate
    GROUP BY CustomerId;
END;
GO

It can be exposed as a dedicated MCP tool:

"GetOrderSummary": {
  "description": "Returns an order summary for a bounded date range.",
  "source": {
    "object": "mcp_reporting.GetOrderSummary",
    "type": "stored-procedure"
  },
  "rest": {
    "enabled": false
  },
  "graphql": {
    "enabled": false
  },
  "mcp": {
    "custom-tool": true,
    "dml-tools": false
  },
  "permissions": [
    {
      "role": "authenticated",
      "actions": [
        {
          "action": "execute"
        }
      ]
    }
  ]
}

SQL Server restores the previous isolation level when the stored procedure returns, so this does not leave a pooled DAB connection in READ UNCOMMITTED.

As a longer-term alternative, ALLOW_SNAPSHOT_ISOLATION is different from RCSI: it does not automatically change the ERP application's existing READ COMMITTED behavior. Reporting procedures can explicitly opt into SNAPSHOT, although enabling it still introduces row-versioning overhead and should be reviewed with the DBA.

One caution: NOLOCK can return rolled-back values, duplicate rows, or omit rows. It also avoids only data-lock blocking; long scans can still affect the ERP through CPU, I/O, memory pressure, and schema locks. Bounded date parameters, appropriate indexes, pagination, and a readable reporting replica, if available, remain preferable for production reporting workloads.