SQL - prevent a function from being executed in a short period of time

Question

I'm using a lot of functions to make a website up and running, almost every action in that website leads to executing a SQL functions. Imagine it this way, almost all data processing and website logic is handled by database and partly web server, crazy right? not my fault.

But there's a problem here, there are multiple event handlers in front-end which send requests to database and in return ask for huge amount of data, like by one click asking for big JSON strings, so there's a chance that users mistakenly call these SQL functions by simultaneously clicking on these events in a short period of time, where it can lead to drop database connection or bring the server down.

So, all I need is to know is whether I can prevent SQL functions from being executed multiple times in a short period of time? or at lease to be executed once before it returns data? and how?

score 6 · Accepted Answer · edited Mar 14 '18 at 20:56

You can prevent a stored procedure from running more often than every "x" seconds using a table to keep track of the last run-time of the given procedure.

Create a table to store the last-run-time:

CREATE TABLE dbo.ProcControl
(
    ProcName sysname NOT NULL
        CONSTRAINT PK_ProcControl
        PRIMARY KEY
        CLUSTERED
    , LastExecution datetime NOT NULL
);
INSERT INTO dbo.ProcControl (ProcName, LastExecution)
VALUES (N'dbo.TestProc', '2017-01-01 00:00:00')
GO

Create a test stored procedure:

CREATE PROCEDURE dbo.TestProc
AS
BEGIN
    SET NOCOUNT ON;
    IF NOT EXISTS (
        SELECT 1 
        FROM dbo.ProcControl pc
        WHERE pc.ProcName = N'dbo.TestProc'
            AND pc.LastExecution >= DATEADD(SECOND, -5, GETDATE())
            )
    BEGIN
        /*
            run the code you want to run at most every 5 seconds.
        */
        SELECT Result = 'This is a result'
            , [Timestamp] = GETDATE();
        UPDATE dbo.ProcControl
        SET LastExecution = GETDATE()
        WHERE ProcName = N'dbo.TestProc';
    END
END
GO

Here, we run the proc twice in quick succession; the first iteration returns a valid result, the second iteration does not. Then we wait for 6 seconds, and run the procedure again, which in-turn returns a result :

DECLARE @returnVal int;
EXEC @returnVal = dbo.TestProc;
PRINT @returnVal;
EXEC @returnVal = dbo.TestProc;
PRINT @returnVal;
WAITFOR DELAY '00:00:06';
EXEC @returnVal = dbo.TestProc;
PRINT @returnVal;

The results:

Cleanup:

IF OBJECT_ID(N'dbo.ProcControl', N'U') IS NOT NULL
DROP TABLE dbo.ProcControl;
IF OBJECT_ID(N'dbo.TestProc', N'P') IS NOT NULL
DROP PROCEDURE dbo.TestProc;
GO

Would I suggest doing this? Absolutely not!

What if you have two customers using the site at the same time? The first customer will get results, the second customer may not. Also, writing into the dbo.ProcControl table unnecessarily may reduce performance, and might become a central bottleneck.

Re-architect your website to use caching and only make calls to the database when data is needed from it.

score 2 · Answer 2 · answered Mar 16 '18 at 02:41

SQL Server functions are designed to not allow side effects. There's a good quote in books online:

The statements in a BEGIN...END block cannot have any side effects. Function side effects are any permanent changes to the state of a resource that has a scope outside the function such as a modification to a database table. The only changes that can be made by the statements in the function are changes to objects local to the function, such as local cursors or variables. Modifications to database tables, operations on cursors that are not local to the function, sending e-mail, attempting a catalog modification, and generating a result set that is returned to the user are examples of actions that cannot be performed in a function.

That creates quite the obstacle for what you hope to accomplish in a function. You can't do DML, open a transaction, take an application long, call most stored procedures, and so on. You can do something like take an exclusive lock on a large object in a query to effectively force serialization of function execution, but that will only slow down the rate of execution. It won't stop functions from being executed. You will need to wrap the function call with some other code or make some kind of other change to your application.

It's true that there are workarounds for causing side effects in functions, but it would be very unwise to attempt them in production.

SQL - prevent a function from being executed in a short period of time

2 Answers2

Linked