CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Lu4
Lu4

Reputation: 15032

SQL the elegant way of treating concurrency

I havent found info on this subject yet, but maybe someone have tried doing this earlier. Imagine a heavily loaded web application (.NET/SQL Server 2008). The application serves clients that first request 'resources' and after a while 'submit' them. The resources are limited, so there is high risk of concurrent database access by several clients. The aim is to optimize the "request resource" mechanism in such way so that even when concurrent database access occurs the clients won't request overlapping database records and therefore the operation will be done as parallel as it is possible...

Conceptual database structure:

CREATE TABLE [dbo].[Resources] (
    [ID] [uniqueidentifier] NOT NULL,
    [Data] [xml] NOT NULL,
    [Lock] [uniqueidentifier] NULL,
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[ResourceRequests] (
    [ID] [uniqueidentifier] NOT NULL,
    [ResourceID] [uniqueidentifier] NOT NULL,
    [DateCreated] [datetime] NOT NULL,
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[ResourceSubmits](
    [ResourceRequestID] [uniqueidentifier] NOT NULL,
    [NewData] [xml] NOT NULL,
) ON [PRIMARY]
GO

To request data, first I need to "reserve" it using "UPDATE" statement, this is the point of synchronization, because updates are executed sequentially 

UPDATE T
SET T.Lock = (@lockID)
FROM
(
    SELECT TOP (@lockCount) R.*
    FROM [dbo].[Resources] AS R
    WHERE R.Lock IS NULL  
) AS T

(I'm not sure but...) As far as I know "UPDATE" operations use internal database locks and therefore it is not possible for two or more concurrent "UPDATE" operations will change the same record when using above query.

But when concurrent access occurs due to fixed order of "SELECT" only one of the clients will perform UPDATE at a time:

Resulting data

This is the result of concurrent execution, done by the following C#/Linq2Sql code:

Thread[] threads = new Thread[20];

for (int threadIndex = 0; threadIndex < 20; threadIndex++)
{
    threads[threadIndex] = new Thread
    (
        new ThreadStart
        (
            delegate
            {
                using (var context = new L2S.l2sDataContext())
                {
                    for (int i = 0; i < 20; i++)
                    {
                        context.LockRoots(Guid.NewGuid(), 5);
                    }
                }
            }
        )
    );
}

foreach (var thread in threads)
{
    thread.Start();
}

foreach (var thread in threads)
{
    thread.Join();
}

As you can see the locks are distributed sequentially the order of Lock column values is not mixed, this means that the update operations were executed sequentially.

So the question, how to execute them on parallel and would it change something (except performance) if the "UPDATE" query will be changed the following way:

UPDATE T
SET T.Lock = (@lockID)
FROM
(
    SELECT TOP (@lockCount) R.*
    FROM [dbo].[Resources] AS R
    WHERE R.Lock IS NULL  
    ORDER BY NEWID() <--------------- The order is random now, but will the updates get executed concurrently?
) AS T

Upvotes: 1

Views: 223

Answers (1)

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239794

You should be able to run your query as:

UPDATE top (@lockCount) T
SET Lock = @lockID
FROM
    [dbo].[Resources] T WITH (READPAST)
WHERE Lock IS NULL

Which will cause the UPDATE to skip rows which are already locked for update whilst searching for @lockCount rows for itself to update. Note that due to the nature of the way this works, the locks will still appear to be allocated in blocks of @lockCount at a time, unless, possibly, there are a lot of transactions in flight simultaneously. In fact, I'm struggling to think of a way to demo to your satisfaction that this is about as concurrent as you'll be able to get, but it is.

More on READPAST

Upvotes: 1

Related Questions