Description:
Context:
I use EF4 code only, n-tier project. Custom created database and tables.
I Use concurrency using a Timestamp column "lastChangedDate" on all entities.
In c#, the property type is set to DateTime.
A custom generated Guid is used for ID.
Problem:
The insert statement is followed by a "select" to fetch the new timestamp:
INSERT INTO `address`(
....
SELECT
`lastChangedDate`
FROM `address`
WHERE row_count() > 0 AND `ID`=@gp4
...
-- @gp4 bla bla bla = 7a05830b-9308-4261-96f6-9e1600ed3a62
This is OK.
Now lets take a loop at an update statement for same entity, same ID:
UPDATE `address` SET
.......
WHERE (`ID` = @gp5) AND (`lastChangedDate` = @gp6);
SELECT
`lastChangedDate`
FROM `address`
WHERE row_count() > 0 AND `ID`=last_insert_id()
-- @gp5 blablabla = 7a05830b-9308-4261-96f6-9e1600ed3a62
-- @gp6 (dbtype=DateTime, size=0, direction=Input) = 21-10-2010 14:23:43
BUGS:
1. Why use `ID`=last_insert_id() in SELECT statement following the "UPDATE" ???
What if in between the insert and update another address was inserted?
You could better use the ID parameter @gp5 here.
Perhaps use the `ID`=last_insert_id() in the SELECT following the "INSERT"...
but not after an update statement. Or am i insane?
2. In my db i discovered another issue. This specific update and select query combination would result in selecting a `lastChangedDate` from another (wrong) address 'z' (with guid 0e9c0ee5-a73f-45f8-919c-9e1600ddcaa3) , but not because z was inserted between the insert and update of the 7a05830b-9308-4261-96f6-9e1600ed3a62 address. In this case, z was created before this address.
So it seems like last_insert_id() is also buggy, because in my case it actually should be 7a05830b-9308-4261-96f6-9e1600ed3a62!
If you need more info, please contact me. I have sql logs.
lastly, my concurrency property map for address in c#:
Property(x => x.lastChangedDate)
.IsConcurrencyToken()
//.IsComputed() -> commented, seems not to be needed
.StoreGeneratedPattern = StoreGeneratedPattern.Computed;
How to repeat:
I have no repeat scenario.
Here are my sql logs:
21-10-2010 14:23:29 :
INSERT INTO `user`(
`birthDate`,
`creationDate`,
`creationUserID`,
`ID`,
`lastChangedUserID`,
`name`,
`surname`) VALUES (
@gp1,
@gp2,
NULL,
@gp3,
NULL,
@gp4,
@gp5);
SELECT
`lastChangedDate`
FROM `user`
WHERE row_count() > 0 AND `ID`=@gp3
-- @gp1 (dbtype=DateTime, size=0, direction=Input) = 21-10-2010 14:23:25
-- @gp2 (dbtype=DateTime, size=0, direction=Input) = 21-10-2010 14:23:29
-- @gp3 (dbtype=Guid, size=0, direction=Input) = 9cb401f1-2d34-434a-86c1-9e1600ed2a9a
-- @gp4 (dbtype=String, size=4, direction=Input) = "Atam"
-- @gp5 (dbtype=String, size=6, direction=Input) = "Panday"
21-10-2010 14:23:43 :
INSERT INTO `address`(
`coordinatenID`,
`creationDate`,
`creationUserID`,
`houseNumber`,
`ID`,
`lastChangedUserID`,
`placeID`,
`street`) VALUES (
NULL,
@gp1,
@gp2,
@gp3,
@gp4,
NULL,
NULL,
@gp5);
SELECT
`lastChangedDate`
FROM `address`
WHERE row_count() > 0 AND `ID`=@gp4
-- @gp1 (dbtype=DateTime, size=0, direction=Input) = 21-10-2010 14:23:43
-- @gp2 (dbtype=Guid, size=0, direction=Input) = 9cb401f1-2d34-434a-86c1-9e1600ed2a9a
-- @gp3 (dbtype=String, size=2, direction=Input) = "16"
-- @gp4 (dbtype=Guid, size=0, direction=Input) = 7a05830b-9308-4261-96f6-9e1600ed3a62
-- @gp5 (dbtype=String, size=12, direction=Input) = "Edisonstraat"
21-10-2010 14:24:30 :
SELECT
`Extent1`.`coordinatenID`,
`Extent1`.`creationDate`,
`Extent1`.`creationUserID`,
`Extent1`.`houseNumber`,
`Extent1`.`ID`,
`Extent1`.`lastChangedDate`,
`Extent1`.`lastChangedUserID`,
`Extent1`.`placeID`,
`Extent1`.`street`
FROM `address` AS `Extent1`
WHERE `Extent1`.`ID` = @p__linq__0 LIMIT 2
-- p__linq__0 (dbtype=Guid, size=0, direction=Input) = 7a05830b-9308-4261-96f6-9e1600ed3a62
21-10-2010 14:26:36 :
UPDATE `address` SET `coordinatenID`=NULL, `creationDate`=@gp1, `creationUserID`=@gp2, `houseNumber`=@gp3, `lastChangedUserID`=NULL, `placeID`=NULL,
`street`=@gp4 WHERE (`ID` = @gp5) AND (`lastChangedDate` = @gp6);
SELECT
`lastChangedDate`
FROM `address`
WHERE row_count() > 0 AND `ID`=last_insert_id()
-- @gp1 (dbtype=DateTime, size=0, direction=Input) = 21-10-2010 14:23:43
-- @gp2 (dbtype=Guid, size=0, direction=Input) = 9cb401f1-2d34-434a-86c1-9e1600ed2a9a
-- @gp3 (dbtype=String, size=2, direction=Input) = "18"
-- @gp4 (dbtype=String, size=12, direction=Input) = "Edisonstraat"
-- @gp5 (dbtype=Guid, size=0, direction=Input) = 7a05830b-9308-4261-96f6-9e1600ed3a62
-- @gp6 (dbtype=DateTime, size=0, direction=Input) = 21-10-2010 14:23:43
Suggested fix:
use last_insert_id only following an Insert in a sql batch.
Not following an update.
check the last_insert_id() function as it seems buggy.
Description: Context: I use EF4 code only, n-tier project. Custom created database and tables. I Use concurrency using a Timestamp column "lastChangedDate" on all entities. In c#, the property type is set to DateTime. A custom generated Guid is used for ID. Problem: The insert statement is followed by a "select" to fetch the new timestamp: INSERT INTO `address`( .... SELECT `lastChangedDate` FROM `address` WHERE row_count() > 0 AND `ID`=@gp4 ... -- @gp4 bla bla bla = 7a05830b-9308-4261-96f6-9e1600ed3a62 This is OK. Now lets take a loop at an update statement for same entity, same ID: UPDATE `address` SET ....... WHERE (`ID` = @gp5) AND (`lastChangedDate` = @gp6); SELECT `lastChangedDate` FROM `address` WHERE row_count() > 0 AND `ID`=last_insert_id() -- @gp5 blablabla = 7a05830b-9308-4261-96f6-9e1600ed3a62 -- @gp6 (dbtype=DateTime, size=0, direction=Input) = 21-10-2010 14:23:43 BUGS: 1. Why use `ID`=last_insert_id() in SELECT statement following the "UPDATE" ??? What if in between the insert and update another address was inserted? You could better use the ID parameter @gp5 here. Perhaps use the `ID`=last_insert_id() in the SELECT following the "INSERT"... but not after an update statement. Or am i insane? 2. In my db i discovered another issue. This specific update and select query combination would result in selecting a `lastChangedDate` from another (wrong) address 'z' (with guid 0e9c0ee5-a73f-45f8-919c-9e1600ddcaa3) , but not because z was inserted between the insert and update of the 7a05830b-9308-4261-96f6-9e1600ed3a62 address. In this case, z was created before this address. So it seems like last_insert_id() is also buggy, because in my case it actually should be 7a05830b-9308-4261-96f6-9e1600ed3a62! If you need more info, please contact me. I have sql logs. lastly, my concurrency property map for address in c#: Property(x => x.lastChangedDate) .IsConcurrencyToken() //.IsComputed() -> commented, seems not to be needed .StoreGeneratedPattern = StoreGeneratedPattern.Computed; How to repeat: I have no repeat scenario. Here are my sql logs: 21-10-2010 14:23:29 : INSERT INTO `user`( `birthDate`, `creationDate`, `creationUserID`, `ID`, `lastChangedUserID`, `name`, `surname`) VALUES ( @gp1, @gp2, NULL, @gp3, NULL, @gp4, @gp5); SELECT `lastChangedDate` FROM `user` WHERE row_count() > 0 AND `ID`=@gp3 -- @gp1 (dbtype=DateTime, size=0, direction=Input) = 21-10-2010 14:23:25 -- @gp2 (dbtype=DateTime, size=0, direction=Input) = 21-10-2010 14:23:29 -- @gp3 (dbtype=Guid, size=0, direction=Input) = 9cb401f1-2d34-434a-86c1-9e1600ed2a9a -- @gp4 (dbtype=String, size=4, direction=Input) = "Atam" -- @gp5 (dbtype=String, size=6, direction=Input) = "Panday" 21-10-2010 14:23:43 : INSERT INTO `address`( `coordinatenID`, `creationDate`, `creationUserID`, `houseNumber`, `ID`, `lastChangedUserID`, `placeID`, `street`) VALUES ( NULL, @gp1, @gp2, @gp3, @gp4, NULL, NULL, @gp5); SELECT `lastChangedDate` FROM `address` WHERE row_count() > 0 AND `ID`=@gp4 -- @gp1 (dbtype=DateTime, size=0, direction=Input) = 21-10-2010 14:23:43 -- @gp2 (dbtype=Guid, size=0, direction=Input) = 9cb401f1-2d34-434a-86c1-9e1600ed2a9a -- @gp3 (dbtype=String, size=2, direction=Input) = "16" -- @gp4 (dbtype=Guid, size=0, direction=Input) = 7a05830b-9308-4261-96f6-9e1600ed3a62 -- @gp5 (dbtype=String, size=12, direction=Input) = "Edisonstraat" 21-10-2010 14:24:30 : SELECT `Extent1`.`coordinatenID`, `Extent1`.`creationDate`, `Extent1`.`creationUserID`, `Extent1`.`houseNumber`, `Extent1`.`ID`, `Extent1`.`lastChangedDate`, `Extent1`.`lastChangedUserID`, `Extent1`.`placeID`, `Extent1`.`street` FROM `address` AS `Extent1` WHERE `Extent1`.`ID` = @p__linq__0 LIMIT 2 -- p__linq__0 (dbtype=Guid, size=0, direction=Input) = 7a05830b-9308-4261-96f6-9e1600ed3a62 21-10-2010 14:26:36 : UPDATE `address` SET `coordinatenID`=NULL, `creationDate`=@gp1, `creationUserID`=@gp2, `houseNumber`=@gp3, `lastChangedUserID`=NULL, `placeID`=NULL, `street`=@gp4 WHERE (`ID` = @gp5) AND (`lastChangedDate` = @gp6); SELECT `lastChangedDate` FROM `address` WHERE row_count() > 0 AND `ID`=last_insert_id() -- @gp1 (dbtype=DateTime, size=0, direction=Input) = 21-10-2010 14:23:43 -- @gp2 (dbtype=Guid, size=0, direction=Input) = 9cb401f1-2d34-434a-86c1-9e1600ed2a9a -- @gp3 (dbtype=String, size=2, direction=Input) = "18" -- @gp4 (dbtype=String, size=12, direction=Input) = "Edisonstraat" -- @gp5 (dbtype=Guid, size=0, direction=Input) = 7a05830b-9308-4261-96f6-9e1600ed3a62 -- @gp6 (dbtype=DateTime, size=0, direction=Input) = 21-10-2010 14:23:43 Suggested fix: use last_insert_id only following an Insert in a sql batch. Not following an update. check the last_insert_id() function as it seems buggy.