CODEWITHSUNDEEP
c#sql-serverdatabaserace-condition
richzilla
richzilla

Reputation: 42052

.Net / SQL Server race condition

I have an integration test that looks like the following:

[TestMethod]
    public async Task TestMethod1()
    {
        Guid guid = Guid.NewGuid();

        using (HttpClient client = new HttpClient())
            await client.GetAsync(String.Format(_uri, guid.ToString()));

        Guid retrieved = GetLastGuid();
        Assert.AreEqual(guid, retrieved);
    }

client.GetAsync is calling out to a simple web service that saves the Guid in a database table. GetLastGuid performs the following query on the table:

select top 1 * from dbo.Messages order by MessageId desc

Where message id is an identity column on the messages table.

Putting the wisdom of attempting to test database interactions aside, im seeing the following behaviour, and from a purely technical perspective, im interested to know whats happening.

What I expect to happen

The service is a called and the Guid is saved into the database. Looking for the most recent row in the database table will return the last entry. My understanding (and where i suspect im incorrect) is that executing stored procedures on SQL server is a synchronous process, i.e. it wont return until its committed the transaction.

What is actually happening

Chaining several calls to my test method results in some of them failing. It looks like theyre retrieving the Guid for the previous call.

What I think might be the cause

I suspect my assumption that stored procedure calls are synchronous is flawed, and that my failed tests are actually attempting to read the latest value before the previous transaction has been committed. The only thing that makes me doubt this, is that setting the GetLastGuid query to read uncommitted still results in the same problem.

All of the test methods are run sequentially, and nothing else is writing into this database table. Judicious use of Thread.Sleep / running in debug mode resolves the issue, all classic signs of a race condition.

Upvotes: 1

Views: 452

Answers (1)

user2930590
user2930590

Reputation:

select top 1 does not guarantee to give you the correct guid. You should specify to select * from dbo.Messages where guid = @guid or have the messageid (?) return from the http client call and use that to fetch the data and compare the guids. Although if no data is returned should be the first assert to be checked.

Upvotes: 1

Related Questions