Race Condition

[zso cii]

Hi there.

Admitting to be a newbie here, just started with MySQL and need help.

I have the following SP:

CREATE DEFINER=`root`@`localhost` PROCEDURE `LoginUser`(PLoginName VARCHAR(10),PPassWord VARCHAR(10), PInternalID INT)
BEGIN
SELECT Name, DBId, Country, UserType, InternalId, FlagIndex, Image FROM Users
WHERE LoginName = PLoginName and Password = PPassWord FOR UPDATE;
UPDATE Users SET InternalId = PInternalId, LastLogin = Now()
WHERE LoginName = PLoginName and Password = PPassWord;
COMMIT;
END

Initially the InternalId Fields are set to -1.

I call this SP from 10+ threads at the "same" time with same PLoginName and PPasword but different PInternalId parameters.

Sometimes the InternalId field in resultsets I am getting back contains the same value. E.g. two times I am getting back the same InternalId, however all results shall have different InternalId, as no duplicates happens in the parameter.

It seems I need to lock the table, but this is not efficient in my case. Is there a exclusive rowlock clause for the select statement that would solve this challenge within a transaction?

Thanks in advance,
regards
Zsocii



Edited 1 time(s). Last edit at 10/29/2008 03:12AM by zso cii.