Ik heb een InnoDB tabel die online gebruikers registreert. Het wordt bijgewerkt op elke pagina te vernieuwen door een gebruiker om bij te houden welke pagina’s ze op en de laatste toegang datum op de website te houden. Ik heb toen een cron die elke 15 minuten rijdt naar de oude records te verwijderen.
Ik heb een ‘impasse gevonden wanneer het proberen om slot te krijgen; probeer het opnieuw opstarten van de transactie’ voor ongeveer 5 minuten gisteravond en het lijkt te zijn bij het uitvoeren van Voegt in deze tabel. Kan iemand aangeven hoe om deze fout te vermijden?
=== EDIT ===
Hier zijn de vragen die worden uitgevoerd:
Eerste bezoek aan de site:
INSERT INTO onlineusers SET
ip = 123.456.789.123,
datetime = now(),
userid = 321,
page = '/thispage',
area = 'thisarea',
type = 3
Op elke pagina te vernieuwen:
UPDATE onlineusers SET
ips = 123.456.789.123,
datetime = now(),
userid = 321,
page = '/thispage',
area = 'thisarea',
type = 3
WHERE id = 888
Cron elke 15 minuten:
DELETE FROM onlineusers WHERE datetime <= now() - INTERVAL 900 SECOND
Het doet vervolgens een aantal tellingen om een aantal statistieken te melden. (Dat wil zeggen: leden online, bezoekers online)
Antwoord 1, Autoriteit 100%
Een eenvoudige truc die kunnen helpen met de meeste impasses is het sorteren van de maatregelen in een bepaalde volgorde.
Je krijgt een impasse wanneer twee transacties proberen om twee sloten aan de tegengestelde orders te vergrendelen, te weten:
- Aansluiting 1: sloten sleutel (1), sloten sleutel (2);
- aansluiting 2: sloten toets (2) vergrendelt sleutel (1);
Als beide op hetzelfde moment worden uitgevoerd, zal de verbinding 1 sleutel (1) vergrendelen, de verbinding 2 zal sleutel (2) vergrendelen en elke verbinding wacht op de andere om de sleutel – & GT vrij te geven; impasse.
Nu, als u uw vragen hebt gewijzigd, zodat de verbindingen de toetsen op dezelfde bestelling zouden vergrendelen, dwz:
- Aansluiting 1: sloten sleutel (1), sloten sleutel (2);
- Verbinding 2: Sloten sleutel (1 ), slotsleutel (2 );
Het is onmogelijk om een impasse te krijgen.
Dus dit is wat ik voorstel:
-
Zorg ervoor dat u geen andere vragen hebt die toegang hebben tot meer dan één sleutel tegelijkertijd behalve de verklaring verwijderen. Als u het doet (en ik vermoed dat u het doet), bestelt u hun waar in (K1, K2, .. KN) in oplopende volgorde.
-
Bevestig uw verklaring verwijderen om in oplopende volgorde te werken:
Wijzigen
DELETE FROM onlineusers
WHERE datetime <= now() - INTERVAL 900 SECOND
Naar
DELETE FROM onlineusers
WHERE id IN (
SELECT id FROM onlineusers
WHERE datetime <= now() - INTERVAL 900 SECOND
ORDER BY id
) u;
Een ander ding om in gedachten te houden, is dat MySQL-documentatie suggereert dat in het geval van een impasse de cliënt automatisch opnieuw moet proberen. U kunt deze logica toevoegen aan uw clientcode. (Zeg, 3 pogingen op deze specifieke fout voordat u opgeeft).
Antwoord 2, Autoriteit 26%
Deadlock gebeuren wanneer twee transacties op elkaar wachten om een slot te verwerven. Voorbeeld:
- TX 1: Sluit A, dan b
- TX 2: LOCK B, DAN EEN
Er zijn talloze vragen en antwoorden over impasses. Elke keer dat u een rij invoegt/bijwerkt/of verwijdert, wordt een vergrendeling verkregen. Om een impasse te voorkomen, moet u ervoor zorgen dat gelijktijdige transacties de rij niet bijwerken in een volgorde die tot een impasse zou kunnen leiden. Over het algemeen probeer slot altijd in dezelfde volgorde te verkrijgen, zelfs in verschillende transacties (bijv. altijd eerst tafel A, dan tafel B).
Een andere reden voor een impasse in de database kan zijn ontbrekende indexen. Wanneer een rij wordt ingevoegd/bijgewerkt/verwijderd, moet de database de relationele beperkingen controleren, dat wil zeggen, ervoor zorgen dat de relaties consistent zijn. Om dit te doen, moet de database de externe sleutels in de gerelateerde tabellen controleren. Het kanertoe leiden dat een ander slot wordt verkregen dan de rij die is gewijzigd. Zorg er dan voor dat je altijd index op de refererende sleutels (en natuurlijk primaire sleutels) hebt, anders kan het resulteren in een table lockin plaats van een row lock. Als tafelvergrendeling plaatsvindt, is de vergrendelingsconflict groter en neemt de kans op een deadlock toe.
Antwoord 3, autoriteit 4%
Het is waarschijnlijk dat de delete-instructie een groot deel van het totale aantal rijen in de tabel zal beïnvloeden. Uiteindelijk kan dit ertoe leiden dat een tabelvergrendeling wordt verkregen bij het verwijderen. Vasthouden aan een slot (in dit geval rij- of paginavergrendelingen) en het verkrijgen van meer vergrendelingen is altijd een risico op een impasse. Ik kan echter niet uitleggen waarom de insert-instructie tot een lock-escalatie leidt – het kan te maken hebben met het splitsen/toevoegen van pagina’s, maar iemand die MySQL beter kent, moet daar invullen.
Om te beginnen kan het de moeite waard zijn om te proberen om direct een tabelvergrendeling te verkrijgen voor het delete-statement. Zie VERGRENDELTABELLENen Tabelvergrendelingsproblemen.
Antwoord 4, autoriteit 3%
Als iemand nog steeds met dit probleem worstelt:
Ik kreeg te maken met een soortgelijk probleem waarbij 2 verzoeken tegelijkertijd de server bereikten. Er was geen situatie zoals hieronder:
T1:
BEGIN TRANSACTION
INSERT TABLE A
INSERT TABLE B
END TRANSACTION
T2:
BEGIN TRANSACTION
INSERT TABLE B
INSERT TABLE A
END TRANSACTION
Dus ik was verbaasd waarom er een impasse is ontstaan.
Toen ontdekte ik dat er een ouder-kindrelatie was tussen 2 tabellen vanwege een externe sleutel. Toen ik een record in de onderliggende tabel invoegde, kreeg de transactie een slot op de rij van de bovenliggende tabel. Onmiddellijk daarna probeerde ik de bovenliggende rij bij te werken die de verhoging van de vergrendeling activeerde tot EXCLUSIEVE. Omdat de 2e gelijktijdige transactie al een SHARED lock bevatte, veroorzaakte dit een deadlock.
Raadpleeg: https://blog.tekenlight.com /2019/02/21/database-deadlock-mysql.html
Antwoord 5, autoriteit 2%
Je zou kunnen proberen om die delete-taak te laten werken door eerst de sleutel van elke rij die moet worden verwijderd in te voeren in een tijdelijke tabel zoals deze pseudocode
create temporary table deletetemp (userid int);
insert into deletetemp (userid)
select userid from onlineusers where datetime <= now - interval 900 second;
delete from onlineusers where userid in (select userid from deletetemp);
Het op deze manier opsplitsen is minder efficiënt, maar het vermijdt de noodzaak om een toetsbereikvergrendeling vast te houden tijdens het delete.
Wijzig ook uw select-query’s om een where-clausule toe te voegen, met uitzondering van rijen die ouder zijn dan 900 seconden. Dit vermijdt de afhankelijkheid van de cron-taak en stelt u in staat om het opnieuw te plannen zodat het minder vaak wordt uitgevoerd.
Theorie over de impasses: ik heb niet veel achtergrond in MySQL, maar hier komt… De deletegaat een key-range lock vasthouden voor datetime, om te voorkomen dat rijen overeenkomen zijn where-clausule wordt niet toegevoegd in het midden van de transactie, en als het rijen vindt om te verwijderen, zal het proberen een vergrendeling te verkrijgen op elke pagina die het aan het wijzigen is. De insertkrijgt een slot op de pagina waarin het wordt ingevoegd, en vervolgensprobeert het keylock te verkrijgen. Normaal gesproken wacht de insertgeduldig tot die toetsvergrendeling opengaat, maar dit zal vastlopen als de deletedezelfde pagina probeert te vergrendelen als de insertis gebruiken omdat dedeletedie paginavergrendeling nodig heeft en de insertdie toetsvergrendeling nodig heeft. Dit lijkt echter niet goed voor invoegingen, de deleteen insertgebruiken datum/tijd-bereiken die elkaar niet overlappen, dus misschien is er iets anders aan de hand.
http://dev.mysql. com/doc/refman/5.1/en/innodb-next-key-locking.html
Antwoord 6
Voor Java-programmeurs die Spring gebruiken, heb ik dit probleem vermeden door een AOP-aspect te gebruiken dat automatisch transacties opnieuw probeert die tegen tijdelijke impasses aanlopen.
Zie @Retrytransactie javadoc voor meer informatie.
Antwoord 7
Ik heb een methode, waarvan de internals zijn gewikkeld in een MySQltransactie.
Het deadlock-kwestie kwam voor mij opdagen toen ik dezelfde methode parallel met zichzelf liep.
Er was geen probleem met een enkele instantie van de methode.
Toen ik MySQlTransactie verwijderde, kon ik de methode parallel met zichzelf zonder problemen uitvoeren.
Delen gewoon mijn ervaring, ik ben niets aan.
Antwoord 8
cronis gevaarlijk. Als een exemplaar van Cron faalt voordat de volgende verschuldigd is, zullen ze waarschijnlijk elkaar bestrijden.
Het zou beter zijn om een continu hardloopbaan te hebben die sommige rijen zou verwijderen, sommigen slaapt, herhaal dan.
Ook, INDEX(datetime)is erg belangrijk voor het vermijden van deadlocks.
Maar als de Datetime-test meer omvat dan, zeg 20% van de tabel, de DELETEzal een tabelcan doen. Kleinere brokken worden vaker verwijderd, is een oplossing.
Nog een reden voor het gaan met kleinere brokken is om minder rijen te vergrendelen.
Bottom line:
INDEX(datetime)- Voortdurend taak – Verwijderen, slaap een minuut, herhaal.
- Om ervoor te zorgen dat de bovenstaande taak niet is gestorven, een cron-taak heeft waarvan het enige doel is om het opnieuw op te starten bij falen.
Andere verwijdertechnieken: http://mysql.rjweb.org/doc.php/deleteBig
Antwoord 9
Antwoord van @omry Yadan (https://stackoverflow.com/a/2423921/1810962 ) kan worden vereenvoudigd door Order gebruiken door.
Wijzigen
DELETE FROM onlineusers
WHERE datetime <= now() - INTERVAL 900 SECOND
naar
DELETE FROM onlineusers
WHERE datetime <= now() - INTERVAL 900 SECOND
ORDER BY ID
om de volgorde waarin u items verwijdert consistent te houden. Ook als u meerdere inserts in een enkele transactie doet, zorg er dan voor dat ze ook altijd op id worden besteld.
Volgens de mysql delete-documentatie:
Als de ORDER BY-component is opgegeven, worden de rijen verwijderd in de opgegeven volgorde.
Je kunt hier een referentie vinden: https://dev. mysql.com/doc/refman/8.0/en/delete.html