[ASP/SQL] conditionele atomic record insert

[Zoijar]

Hoe kan ik het volgende het beste oplossen? Ik heb een database tabel waaruit ik een record selecteer (bij elke page hit). Die heeft een kolom/veld last_update dat de tijd bevat dat de laatste update is uitgevoerd voor dat record. Een "update" moet bv elk uur gebeuren, en de actie die dan ondernomen wordt is dat er in een andere tabel een record ge-insert wordt, gekoppeld aan dit record.

Dus in pseudo iets als:

select last_update, id from TableXXX where xxx
if (time() - last_update > 1 hour) {
insert into TableYYY values(xx, yy, zz, id)
update last_update = NOW()
}

Dit kan fout gaan als er twee tegelijk bezig zijn. Die halen allebei last_update op, zien dat het nodig is, inserten dan allebei een record, en updaten de last_update. Resultaat is twee updates ipv eentje. Dit lijkt me een veel voorkomend probleem. Hoe los ik dat het beste op, op een zo snel mogelijke manier. Ik lock bijvoorbeeld liever niet ook alle updates van andere records globaal. Kan het in een enkele atomic query?

[farlane]

Bij het inserten controleren of je datum nog hetzelfde is..

[kenneth]

farlane schreef op zondag 30 november 2008 @ 19:59:
Bij het inserten controleren of je datum nog hetzelfde is..

Dat gaat je race-probleem niet oplossen omdat de controles ook nog voor de dubbele update kunnen plaatsvinden.

[DaCoTa]

kenneth schreef op zondag 30 november 2008 @ 20:03:
[...]

Dat gaat je race-probleem niet oplossen omdat de controles ook nog voor de dubbele update kunnen plaatsvinden.

Checken of bij de update je datum nog hetzelfde is.

[_js_]

Je updates in cronjob/taskscheduler doen, en niet wanneer toevallig een bezoeker die pagina opent.

[ Voor 9% gewijzigd door _js_ op 30-11-2008 21:34 ]

[Zoijar]

_js_ schreef op zondag 30 november 2008 @ 21:34:
Je updates in cronjob/taskscheduler doen, en niet wanneer toevallig een bezoeker die pagina opent.

Ja, lastig... windows machine met sql server en ik mag niet bij de scheduling.

Ik las dit ergens. Lijkt me ook wel te werken met een kleine aanpassing

SQL:

DECLARE @CounterInitialValue INT
DECLARE @NewCounterValue INT
SELECT @CounterInitialValue = SELECT counter FROM MyTable WHERE MyID = 1234

-- do stuff with the counter value

UPDATE MyTable SET counter = counter + 1 WHERE MyID = 1234
AND 
counter = @CounterInitialValue -- prevents the update if counter changed.
-- the value of counter must not change in this scenario.
-- so we rollback if the update affected no rows
IF( @@ROWCOUNT = 0 )    ROLLBACK

[farlane]

Zoijar schreef op maandag 01 december 2008 @ 10:25:
[...]

Ja, lastig... windows machine met sql server en ik mag niet bij de scheduling.

Ik las dit ergens. Lijkt me ook wel te werken met een kleine aanpassing

SQL:

DECLARE @CounterInitialValue INT
DECLARE @NewCounterValue INT
SELECT @CounterInitialValue = SELECT counter FROM MyTable WHERE MyID = 1234

-- do stuff with the counter value

UPDATE MyTable SET counter = counter + 1 WHERE MyID = 1234
AND 
counter = @CounterInitialValue -- prevents the update if counter changed.
-- the value of counter must not change in this scenario.
-- so we rollback if the update affected no rows
IF( @@ROWCOUNT = 0 )    ROLLBACK

Dat komt toch neer op wat ik zei?

[ Voor 3% gewijzigd door farlane op 01-12-2008 11:48 ]

[Zoijar]

farlane schreef op maandag 01 december 2008 @ 11:48:
Dat komt toch neer op wat ik zei?

Hmm...ja... op dezelfde manier als dat iemand had gezegd "oh, dan moet je het locken", of "oh, dan gebruik je toch gewoon 1 query"

[Verwijderd]

Misschien is het ook een optie om een extra (unique) numeriek veld toe te voegen aan tabel B, waar bij elke record het totaal aantal records van tabel B van vóór de insert staat. Eerst bepaal je dan het aantal records in tabel B, daarna controleer je of de laatste update ruim een uur geleden is uitgevoerd. Als dat inderdaad zo is, insert je zowel de benodigde gegevens als het eerder bepaalde aantal records in één keer in tabel B. Door de unique-constraint op het toegevoegde veld kan nu telkens maar één keer een insert gedaan worden. Eventueel zouden alle waarden van het toegevoegde veld, behalve die van de laatst toegevoegde record, op null gezet kunnen worden.

pseudo:

code:

num = select count(id) from TableYYY
select last_update, id from TableXXX where xxx
if (time() - last_update > 1 hour) {
  if(insert into TableYYY values(xx, yy, zz, id, num) {
    update last_update = NOW()
  }
}

Voor die paar milliseconden verschil zou de laatste if waarschijnlijk niet eens gebruikt hoeven worden.

[ Voor 41% gewijzigd door Verwijderd op 02-12-2008 18:02 ]

[Cousin Boneless]

SQL Server neem ik aan.. Heb je voor dit probleem ook gekeken naar sp_getapplock?
En dan met name met de optie:

@LockTimeout = 0

"To indicate that a lock request should return an error instead of wait for the lock when the request cannot be granted immediately, specify 0"

http://msdn.microsoft.com/en-us/library/ms189823.aspx

[d00d]

Waar zet je eea niet een een transactie:

SQL:

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
BEGIN TRANSACTION
    -- SELECT BLA BLA
    -- UPDATE BLA BLA
COMMIT TRANSACTION