Wednesday, February 15, 2012

Execution out of order?

Okay, this seems strange to me - wondering if it's a problem with my code, or the overall concept.

Using SQL Server 2005 SP2 (9.0.3033), on Server 2003 R2. I am building a MUD style game, and working on the issue of Speech - My method is:

log speech events on a given tile in the game world with a stored procedure (called via a web service) which stores the player ID(int), speech(nvarchar) and a timestamp (datetime).

The last time each player checked for speech events is stored in the players table, again as a datetime value.

a second SP checks for speech events on a game tile, looking for any event that has happened since the last check. This uses a simple comparison between the player's "lastspeechcheck" thus:

SELECT TalkingPlayer.PlayerName + ' says ' + SpeechLog.SpeechText AS PlayerSpeech

FROM SpeechLog INNER JOIN

players ON SpeechLog.SpeechLoc = players.currentlocation INNER JOIN

players AS TalkingPlayer ON SpeechLog.PlayerID = TalkingPlayer.PlayerID

WHERE (players.DNNUserID = @.DNNUserID) AND players.LastSpeechCheck < SpeechLog.SpeechTimeStamp )

ORDER BY SpeechLog.SpeechTimeStamp

so far, so good - the events are logged and returned... *but* when I add an update to update the player's lastspeechcheck column, it all falls apart. this is my update:

UPDATE players

SET LastSpeechCheck = GETDATE()

WHERE (DNNUserID = @.DNNUserID)

Sometimes it works as expected, sometimes (more often!) it seems that the update runs before the select, and I don't see the events.

If I remove the update, and manually alter the values in the database, the stored procedures work as expected...

I have tried having the update as part of the same SP, as a seperate SP called from the first, or even as a seperate SP called completely seperately from my webservice - even destroying my connection object and creating a new one doesn't seem to guarantee that the SPs execute as expected.

So, hopefully a simple question - how do I ensure that transactions happen in the order I submit them (or at least, ensure that I can see the values in the database before my update applies?)

Nobody?

Surely I can't be the only person with this problem?

Let's be honest here, this is pretty fundamental stuff - how can you even USE a database which executes things in it's own random order, rather than the order specified?

How can you you perform any kind of useful data manipulation, if the server takes it upon itself to perform your update *before* it has performed the select?

is this some datetime data type craziness, that renders them useless? would I get the same results if I stored a sequence number for the last record handled, grabbed everything written since that point, then updated a "lastsequencenumberseen" column?

I cannot grasp why on earth Microsoft thought this a good idea? Surely there must be some way of forcing SQL server to perform transactions in a logical order!!

|||

To start with I would try inserting the following line between the SELECT and UPDATE statements:

WAITFOR DELAY '00:00:05'

This will effectively halt execution of the stored proc for 5s and should help you to see if timing is the cause of the problem (obviously this is just to help you debug - it's not intended as a permanent fix).

I've never heard of this behaviour before - I doubt very much that the UPDATE statement is being executed before the SELECT. Could this stored proc be being executed (with the same parameters) simultaneously on more than one connection?

Chris

|||

A few questions:

Is this the accurate SQL - I ask because there seems to be a bracket missing from the WHERE statement of the SELECT after the AND. If this is the case then can you check there are no other errors in transcription.

No comments:

Post a Comment