MySQL typ: update/replace if row not exist then insert

[Wise]

Håller på med ett C/MySQL projekt och har en liten fundering.

Har en tabell som innehåller en kommandokö. I C programmet har jag en tråd som scannar av denna tabell, gör en SELECT, utför kommandot och sedan deletar den raden. Ett par andra trådar skriver nya kommandon till listan. Kommandona har en prioritet som ibland måste höjas. Problemet uppstår då att trådarna som ska skriva ett helt nytt kommando till listan eller om kommandot finns kvar, höja prioriteten på det kommandot. Skulle gå att först göra en SELECT för att sedan antingen göra en UPDATE eller en INSERT beroende på om kommandot fanns kvar i listan.

Det jag undrar över är om man kan göra allt detta i en fråga till servern?
(har inga kolumner som själva är unika, unika rader kan fås genom att kombinera två kolumner dock). Typ något liknande: UPDATE table SET C3=x C4=y C5=z WHERE C1=uttryck AND C2=uttryck IF NOT EXIST INSERT NEW;

/Henrik

[sodjan]

INSERT med normal-prioritet, om den går fel (med "duplicate index" fel eller liknande) gör
de en UPDATE med höjd prioritet. Det är helt standard förfarande och har inga problem.

Självklart har du något som ger en unik rad, men det har ju inte direkt med just detta
att göra, det har du säkert redan innan av andra orsaker, eller hur ?

EDIT: Såg en annan sak...

> Det jag undrar över är om man kan göra allt detta i en fråga till servern?

Vad menar du med "fråga" ? Om du menar SQL-kommando, så varför detta krav ?
Du gör det i en och samma transaktion i alla fall, eller hur ?

[speakman]

Om du kör innoDB så har du transaktionsstöd och då blir dina operationer atomiska.

[sodjan]

Ja just det, MySQL kan man ju köra transaktionslöst också.
Ingen speciellt bra ide kanske...

[Wise]

Först måste jag erkänna att jag är rätt grön på MySQL och databasstrukturer generellt..

Självklart har du något som ger en unik rad, men det har ju inte direkt med just detta
att göra, det har du säkert redan innan av andra orsaker, eller hur ?

Ja, unika rader bler det om man kombinerar två av kolumnerna, men har inte någon kolumn jag kan sätta UNIQUE eller PRIMARY KEY på. Kulumnerna i tabellen är:
------------------------------------
Slav | Register | Prioritet | Kommando
------------------------------------

Det kan ligga flera rader för samma slav men med olika register. Kombinationen av slav och register är alltid unika. Hade jag bara haft tex slav som alltid var unik kunde jag köra SQL:

INSERT INTO table (Slav,Register,Prioritet,Kommando) VALUES (1,2,3,4) ON DUPLICATE KEY UPDATE Prioritet=prioritet+1;

Vad menar du med "fråga" ? Om du menar SQL-kommando, så varför detta krav ?
Du gör det i en och samma transaktion i alla fall, eller hur ?

Det jag vill slippa är att först föra en SELECT för att se om raden redan existerar, sedan göra antingen en INSERT eller UPDATE beroende på svaret från SELECT. Alltså något i stil med:

UPDATE table Prioritet=Prioritet+1 WHERE Slav=1 AND Register=2 IFNOTEXIST INSERT INTO table (Slav,Register,Prioritet,Kommando) VALUES (1,2,Prioritet+1,4);

Om du kör innoDB så har du transaktionsstöd och då blir dina operationer atomiska.

Kör detta system på flashkort, och då det kan bli uppåt 1000 läsningar/skrivningar per sekund till denna tabell kör jag den med Memory motorn. innoDB körs mot disk ser det ut som. Är inte alls insatt i InnoDB för övrigt. (newbee warning) Vad Menar du med transaktionsstöd, atomiska och transaktionslöst

edit: Formatering

[speakman]

Läs på om transaktioner här. Om du startar en transaktion innan du gör första SELECT-satsen så kan du avgöra om du behöver INSERT eller UPDATE och utföra dessa innan du avslutar transaktionen. Då kommer hela sekvensen räknas som en enda fråga, och det uppstår inga race conditions med parallella klienter.

[sodjan]

> men har inte någon kolumn jag kan sätta UNIQUE eller PRIMARY KEY på.

Skapa ett unikt index.

Nu kan jag inte MySQL, men är de där SQL satserna korrekt syntax ?
D.v.s med "ON DUPLICATE" eller "IF NOT EXIST" ? I så fall har du ju
redan hela problemet löst. Normelt så blir det separata INSERT och
UPDATE med en kontroll av returnkoden mellan. Kontrollen görs
i C-koden.

D.v.s något i stil med nedan. Jag har inte en susning hur syntaxen ska se ut
i C och MySQL, men generellt sätt kan de se ut något i stil med :

Kod: Markera allt

execsql ("START TRANSACTION READ WRITE");

status = execsql ("INSERT INTO table (Slav,Register,Prioritet,Kommando) values (:v1, :v2, :v3, :v3));

if (status != OK) {

  v3 = v3 + 1;
  status = execsql ("UPDATE table set Slav = :v1, Register = :v2, Prioritet = :v3, Kommando = :v4));
}

if (status = OK) {
  execsql ("COMMIT");
else {
  execsql ("ROLLBACK");
}

I detta fall så utgår jag bara från att om INSERT'en går fel, så är det p.g.a att posten
redan finns. I verkligehen så behöver man sannolikt kolla att det verkligen var just det
felet man fick...

[Wise]

Skapa ett unikt index.

Skulle jag kunna göra, men jag kan inte referera till detta index på något sätt vid ett senare tillfälle i trådarna, eftersom jag då måste hålla reda på indexet i C-koden och det blir nog rätt grötigt, iaf som jag tänker för tillfället.

Nu kan jag inte MySQL, men är de där SQL satserna korrekt syntax ?

"ON DUPLICATE KEY" är en extension i MySQL som ska fungera om man har en UNIQUE.
"IF NOT EXIST" finns inte, men det är just en sån SQL-query jag skulle vilja göra...

men generellt sätt kan de se ut något i stil med :...

I princip ser det ut sådär i C, fast din kodsnutt kör transaktioner tycker jag mig se,vilket inte kommer fungera, se nedan svar till speakman.

speakman:
Har läst på lite om transaktioner nu och det låter intressant, problemet är bara att eftersom jag kör denna tabell med ENGINE=MEMORY så funkar inte det då Memory motorn inte har stöd för just transactions.. Får nog helt enkelt göra den där SELECT för att sedan avgöra om det blir en INSERT eller UPDATE som måste göras, och lägga en mutex kring dessa kodrader på tabellen så man slipper race conditions.

edit:formatering

[sodjan]

> >> Skulle jag kunna göra, men jag kan inte referera till detta index på något

Nej, självklart inte. Vad menar du med "referera till" ??

> eftersom jag då måste hålla reda på indexet i C-koden

Absolut inte, det är ingen som helst skillnad i C-koden.
Indexet bara hjälper MySQL att "göra jobbet" på rätt sätt.

Det ända som skiljer i C-koden är att du kan få en returkod
för "duplicate index key", men det är allt. ""ON DUPLICATE KEY"
borde fungera även i det fallet. Kolla det.

Har du inte stöd för transaktioner så blir allt annorlunda, det
finns tekniker för att lösa det också, fast man får sköta
"synkningen" mellan användare själv. Du kan ju t.ex få problem
om processen som läser tabellen (och utför "Kommando" samt tar
bort posten antar jag) gör det mellan din första SELECT och
t.ex en UPDATE). Att lägga en "mutex kring kodraderna" kanske
löser det, jag vet inte vad det är.

[Micke_s]

Du kan väl enkelt lägga till ett date/time fält och när du insertar kör du CURDATE och CURTIME(har upplösning på microsekunder) och när en slav letar kommandon så sorterar den efter date/time fältet och ett prioritetsfält.

om alla har samma prioritet så går den efter date/time fältet.

[Wise]

Nej, självklart inte. Vad menar du med "referera till" ??

Menade att jag inte kan använda indexet som unika referenser till enskiljda rader, knepigt att förklara det som snurrar i min hjärna nu

Absolut inte, det är ingen som helst skillnad i C-koden.
Indexet bara hjälper MySQL att "göra jobbet" på rätt sätt.
Det ända som skiljer i C-koden är att du kan få en returkod
för "duplicate index key", men det är allt. ""ON DUPLICATE KEY"
borde fungera även i det fallet. Kolla det.

Är tveksam att "ON DUPLICATE KEY" fungerar, har iofs inte testat, må göra det ordentligt på alla möjliga vis.

Har du inte stöd för transaktioner så blir allt annorlunda, det
finns tekniker för att lösa det också, fast man får sköta
"synkningen" mellan användare själv. Du kan ju t.ex få problem
om processen som läser tabellen (och utför "Kommando" samt tar
bort posten antar jag) gör det mellan din första SELECT och
t.ex en UPDATE). Att lägga en "mutex kring kodraderna" kanske
löser det, jag vet inte vad det är.

Mutex är precis som du beskriver, att låsa resurser i ett fleranvändar(trådar)system. Absolut lösbart med mutexes. Ska även testa transaktioner på Memory tables, även om manualen säger att det inte ska fungera, man vet ju aldrig.

Micke_s:
Jag har redan en time-kollumn, och det du beskriver görs redan. gruppera i prioritet, utföra kommandot med högsta prioritet som är äldst. Detta är dock inte ursprungsproblemet. Tack för tipset om CURDATE och CURTIME, hade jag inte sett förut, kommer absolut användas på andra ställen.

edit: Formatering

[sodjan]

Kan du själv se vad som är citerat och vad du själv har lagt till ?
Varför inte försöka fixa dina inlägg så att det går att läsa ?

> Menade att jag inte kan använda indexet som unika referenser till enskiljda rader,

Vad menar du med att "använda" ? Det är ju inte *du* utan MySQL som använder indexet....

[Wise]

Bättre med formateringen nu kanske..

Ja MySQL kanske kan göra ett lite bättre jobb med indexet, men jag i C koden kan inte utnyttja det, det är det jag försöker säga.

[Barry_Lyndon]

Nu vet jag inte hur din kod ser ut, men som sodjan säger behöver den inte hålla reda på indexet, det sköter mysql. Det enda du behöver göra är att utöka tabellen med ett fält till, sätta vettiga värden på de rader som redan finns, och sen slå på auto increment och indexera tabellen på det fältet. Sen håller mysql reda på indexen åt dig. C-koden behöver inte modifieras. Dessutom går det mycket snabbare att söka i en korrekt indexerad tabell.


edit: stavfel

Edit2: Vänta nu, jag kanske är ute och cyklar ovan... Ett id-fält hjälper nog inte i ditt fall. Men du borde kunna göra en update först, kolla antal påverkade rader och sen göra en insert om det behövs?

/Andreas

[sodjan]

Men så är det ju alltid. Man "använder" aldrig ett index från sin egen kod (1).

(Det beror lite på vad du menar med utnyttja när du säger "men jag i C koden kan inte utnyttja det".)

Det är upp till databasen att använda index när den finner att det är den
bästa lösningen i just det fallet. Däremot kan man "använda" index indirekt
genom att man t.ex får returnkoder om man försöker göra en INSERT som
ger en "duplicate key", och det är så man normalt gör. D.v.s. att man låter
databasen kontrollera om en viss post post finns istället för att ha egen
kod som t.ex söker efter en viss post.

(1) Vissa databas produkter kan ge programmeraren möjlighet att
styra om man ska använda index eller inte genom speciell kodning.
Jag vet dock inte om MySQL stöder det, Kanske, men det ligger
sannolikt lite utanför det aktuella fallet i tråden i alla fall.