En transaksjon i SQL Server er en sekvensiell gruppe av utsagn eller spørringer å utføre enkelt eller flere oppgaver i en database. Hver transaksjon kan ha enkelt lese-, skrive-, oppdaterings- eller sletteoperasjoner eller en kombinasjon av alle disse operasjonene. Hver transaksjon må skje to ting i SQL Server:
- Enten er all endring vellykket når transaksjonen er forpliktet.
- Eller alle modifikasjoner angres når transaksjonen tilbakestilles.
En transaksjon kan ikke lykkes før alle operasjonene i settet er fullført. Det betyr at hvis et argument mislykkes, vil transaksjonsoperasjonen mislykkes. Hver transaksjon begynner med den første kjørbare SQL-setningen og slutter når den finner en commit eller tilbakeføring, enten eksplisitt eller implisitt. Den bruker BEGÅ eller TILBAKE uttalelser eksplisitt, så vel som implisitt når en DDL-setning brukes.
Bildefremstillingen nedenfor forklarer transaksjonsprosessen:
Følgende eksempel vil forklare konseptet med en transaksjon:
Dette eksemplet vil bruke bankdatabasesystemet til å forklare konseptet med en transaksjon. Anta at en bankkunde ønsker å ta ut penger fra kontoen sin ved å bruke minibankmodus. Minibanken kan utføre denne operasjonen i tre trinn:
- De første skritt er å sjekke tilgjengeligheten til det forespurte beløpet på kontoen.
- De andre trinn trekker beløpet fra kontoen dersom beløpet er tilgjengelig og oppdaterer deretter kontosaldoen.
- De tredje trinn er å skrive pengeuttaksoperasjonen i loggfilen. Dette trinnet skriver om at transaksjonen enten er vellykket eller mislyktes. Hvis det lykkes, skriv dataendringen i databasen. Ellers vil transaksjonen bli rullet tilbake til sin forrige tilstand.
Det grunnleggende prinsippet bak transaksjoner er at hvis en av setningene returnerer en feil, rulles hele settet med endringer tilbake for å sikre dataintegritet. Og hvis transaksjonene blir vellykket, vil alle endringer være permanente i databasen. Derfor, hvis det er strømbrudd eller andre problemer når du tar ut penger fra en minibank, garanterer transaksjoner at saldoen vår forblir konsistent. En transaksjonserklæring utfører disse operasjonene best fordi transaksjonens fire nøkkelegenskaper gjør alle operasjoner mer nøyaktige og konsistente. Transaksjonens fire egenskaper omtales som ACID.
Transaksjonsegenskaper
Transaksjonsegenskapene blir referert til som ACID (Atomicitet, Konsistens, Isolasjon, Durability) eiendom, som diskuteres i detalj nedenfor:
Atomitet: Denne egenskapen sikrer at alle utsagn eller operasjoner som er inkludert i transaksjonen, må utføres vellykket. Ellers vil hele transaksjonen bli avbrutt, og alle operasjoner rulles tilbake til sin forrige tilstand når en operasjon mislykkes.
Konsistens: Denne egenskapen sikrer at databasen endrer tilstand bare når en transaksjon vil bli utført. Den er også ansvarlig for å beskytte data mot krasj.
Isolering: Denne egenskapen garanterer at alle transaksjoner er isolert fra andre transaksjoner, noe som betyr at hver operasjon i transaksjonen drives uavhengig. Det sikrer også at uttalelser er transparente for hverandre.
Varighet: Denne egenskapen garanterer at resultatet av begåtte transaksjoner vedvarer i databasen permanent selv om systemet krasjer eller mislyktes.
if else statement java
Transaksjonsmoduser i SQL Server
Det er tre forskjellige transaksjonsmoduser som SQL Server kan bruke:
Transaksjonsmodus for automatisk forpliktelse: Det er SQL Servers standard transaksjonsmodus. Den vil evaluere hver SQL-setning som en transaksjon, og resultatene blir forpliktet eller rullet tilbake tilsvarende. Dermed blir de vellykkede uttalelsene umiddelbart begått, mens de mislykkede uttalelsene umiddelbart rulles tilbake.
Implisitt transaksjonsmodus. Denne modusen lar SQL Server starte den implisitte transaksjonen for hver DML-setning, men den krever eksplisitt bruk av commit- eller rollback-kommandoer på slutten av setningene.
Eksplisitt transaksjonsmodus: Denne modusen er definert av brukeren som lar oss identifisere en transaksjons start- og sluttpunkter nøyaktig. Den vil automatisk avbryte i tilfelle en fatal feil.
Transaksjonskontroll
Følgende er kommandoene som brukes til å kontrollere transaksjoner:
MERK: Vi kan bare bruke DML-setninger (INSERT, UPDATE og DELETE) for Transaction Control Language-kommandoer. Vi kan ikke bruke dem mens vi oppretter eller sletter tabeller fordi disse operasjonene blir forpliktet til databasen automatisk.
Transaksjonstilstand
Den indikerer hvordan transaksjoner går i løpet av deres levetid. Den beskriver nåværende status for transaksjonen samt hvordan transaksjonen vil bli behandlet i fremtiden. Disse statene definerer reglene som bestemmer om en transaksjon forplikter eller avbryter.
La oss beskrive hver transaksjonstilstand i SQL Server:
Aktiv tilstand: Transaksjonen er i aktiv tilstand mens transaksjonens instruksjoner utføres. Det endres til 'delvis forpliktet stat' hvis alle 'lese og skrive' operasjoner er fullført uten feil. Hvis en instruksjon mislykkes, endres den til 'mislykket tilstand'.
Delvis engasjert: Når alle lese- og skriveoperasjoner er fullført, gjøres endringen til hovedminnet eller den lokale bufferen. Staten ville gå til 'forpliktet stat' hvis endringene gjøres permanent i databasen. Ellers går den til 'mislykket tilstand'.
Mislykket tilstand: En transaksjon går til mislykket tilstand når en transaksjons instruksjon mislykkes eller en permanent endring av databasen mislykkes.
Abortert tilstand: Transaksjonen går fra en 'mislykket tilstand' til en 'avbrutt tilstand' når noen form for feil oppstår. Endringene fjernes eller rulles tilbake fordi disse endringene bare gjøres til den lokale bufferen eller hovedminnet i tidligere tilstander.
Forpliktet stat: En transaksjon er fullført og går inn i denne tilstanden når endringene gjøres permanent i databasen og avsluttes i 'avsluttet tilstand'.
Avsluttet tilstand: Hvis det ikke er noen tilbakeføring og transaksjonen er i 'forpliktet stat', systemet er konsistent og klart for en ny transaksjon mens den gamle avsluttes.
Implementering av Transaksjon i SQL Server
La oss ta noen eksempler for å forstå hvordan vi kan implementere transaksjonen i SQL Server. Her skal vi bruke 'Produkt' tabell for å vise alle transaksjonstilstander.
Følgende SQL-skript oppretter produkttabellen i den valgte databasen:
CREATE TABLE Product ( Product_id INT PRIMARY KEY, Product_name VARCHAR(40), Price INT, Quantity INT )
Deretter kjører du skriptene nedenfor for å sette inn data i denne tabellen:
INSERT INTO Product VALUES(111, 'Mobile', 10000, 10), (112, 'Laptop', 20000, 15), (113, 'Mouse', 300, 20), (114, 'Hard Disk', 4000, 25), (115, 'Speaker', 3000, 20);
Utfør SELECT-setningen for å bekrefte dataene:
Eksempel på COMMIT-transaksjon
Det er en god idé å dele SQL-setningene som brukes i transaksjonen i flere logiske deler. Og så kan vi bestemme om vi skal forplikte eller rulle tilbake dataene. Følgende trinn illustrerer hvordan du oppretter en transaksjon:
- Start transaksjonen ved å bruke START TRANSAKSJONEN kommando.
- Skriv SQL-setningene og del dem ut fra våre behov
- Bruke BEGÅ uttalelse for å fullføre transaksjonen og lagre endringene permanent.
Nedenfor er kommandoene som forklarer COMMIT-operasjonene i SQL Server:
-- Start a new transaction BEGIN TRANSACTION -- SQL Statements INSERT INTO Product VALUES(116, 'Headphone', 2000, 30) UPDATE Product SET Price = 450 WHERE Product_id = 113 -- Commit changes COMMIT TRANSACTION
Hvis ingen feil blir funnet, vil vi se følgende utdata der hver SQL-setning av transaksjonen utføres uavhengig:
INSERT- og UPDATE-setningene kan ikke rulles tilbake etter at transaksjonen er utført. Når vi verifiserer tabellen etter commit-operasjon, vil vi se følgende data:
Eksempel på ROLLBACK-transaksjon
Vi vil bruke ROLLBACK-kommandoen til å angre transaksjoner som ennå ikke er lagret i databasen og gå tilbake til punktet der transaksjonen startet. Følgende eksempel forklarer ROLLBACK-operasjonen i SQL Server:
-- Start a new transaction BEGIN TRANSACTION -- SQL Statements UPDATE Product SET Price = 5000 WHERE Product_id = 114 DELETE FROM Product WHERE Product_id = 116
Når vi har utført transaksjonen ovenfor, kan vi se at den vil bli utført. Det vil imidlertid ikke påvirke noen endringer i databasen fordi før vi ikke kjører COMMIT eller ROLLBACK-setningen, kan ikke endringene bli permanente. Derfor har vi muligheten til å bruke ROLLBACK-transaksjonskommandoen for å rulle tilbake alle databaseoperasjoner. Her er hele transaksjonserklæringen:
-- Start a new transaction BEGIN TRANSACTION -- SQL Statements UPDATE Product SET Price = 5000 WHERE Product_id = 114 DELETE FROM Product WHERE Product_id = 116 --Undo Changes ROLLBACK TRANSACTION
Bruk av @@Error Global variabel i transaksjoner:
Denne variabelen er brukes til å sjekke om det er en feil eller ikke. Eksemplet nedenfor forklarer konseptet. Her vil vi først starte transaksjonen ved å bruke BEGIN-kommandoen og deretter skrive to insert-setninger. Deretter vil vi bruke den globale systemvariabelen @@FEIL i IF-uttalelse for å sjekke en feil. Hvis verdien er større enn 0, betyr det at det er en feil. Nå er transaksjonen tilbakerulling; ellers er transaksjonen forpliktet.
BEGIN TRANSACTION INSERT INTO Product VALUES(115,'Speaker', 3000, 25) -- Check for error IF(@@ERROR > 0) BEGIN ROLLBACK TRANSACTION END ELSE BEGIN COMMIT TRANSACTION END
Når transaksjonen ovenfor er utført, vil vi legge merke til at den er tilbakestilt. Det er på grunn av vårt forsøk på å sette inn duplisert en verdi i Primærnøkkel-kolonnen.
Automatisk tilbakeføringstransaksjon
De fleste transaksjonene inneholder mer enn ett søk. Under utføring av transaksjonen, hvis noen av SQL-setningene gir en feil, forekommer ingen endringer i databasen, og de resterende setningene blir ikke utført. Dette konseptet er kjent som en automatisk tilbakeføringstransaksjon i SQL Server. La oss bruke et enkelt eksempel for å demonstrere denne prosessen.
BEGIN TRANSACTION INSERT INTO Product VALUES(118, 'Desktop', 25000, 15) UPDATE Product SET Quantity = 'ten' WHERE Product_id = 113 SELECT * FROM Product COMMIT TRANSACTION
Denne transaksjonen gir følgende utgang:
I denne utgangen kan vi se at insert-setningen ble utført vellykket. Utførelse av oppdateringssetningen fant imidlertid en feil på grunn av datatypekonverteringsproblemet. I dette tilfellet tillater ikke SQL Server noen endringer i databasen, noe som betyr at innsettingsoperasjonen ikke tilfører noen verdi, og select-setningen blir ikke utført.
Lagrepunkt i transaksjoner
Lagringspunktet setter inn et spesielt merke i en transaksjon som lar oss tilbakestille alle endringer utført etter lagringspunktet. Den brukes også til å rulle tilbake en spesifikk del av transaksjonen i stedet for hele transaksjonen. Vi kan definere det ved å bruke LAGRE TRANSAKSJON sp_navn uttalelse. Følgende eksempel vil forklare bruken av lagringspunkt i transaksjoner som forplikter insert-setningen og tilbakestiller delete-setningen.
BEGIN TRANSACTION INSERT INTO Product VALUES(117, 'USB Drive', 1500, 10) SAVE TRANSACTION InsertStatement DELETE FROM Product WHERE Product_id = 116 SELECT * FROM Product ROLLBACK TRANSACTION InsertStatement COMMIT SELECT * FROM Product;
Se resultatet nedenfor der vi kan se produkt-ID 116 er slettet, og 117 er satt inn i den første utgangen. I den andre utgangen rulles imidlertid sletteoperasjonen tilbake på grunn av lagringspunktet.
Hvordan frigi et lagringspunkt i en transaksjon?
Frigjør lagringspunkt brukes til å fjerne det navngitte lagringspunktet fra gjeldende transaksjon uten å tilbakestille resultatene av spørringer utført etter lagringspunktet. MySQL har denne kommandoen, men SQL Server gir ingen kommando for å frigjøre et lagringspunkt. I stedet frigis de automatisk på slutten av en forpliktelse eller tilbakeføringstransaksjon, så vi trenger ikke å bekymre oss for dem i mellomtiden.
Implisitt transaksjon i SQL Server
Vi kan definere en implisitt transaksjon ved å aktivere alternativet IMPLICIT_TRANSACTIONS. Følgende eksempel vil forklare dette konseptet enkelt:
SET IMPLICIT_TRANSACTIONS ON UPDATE Product SET Quantity = 10 WHERE Product_id = 113 SELECT IIF(@@OPTIONS & 2 = 2, 'Implicit Transaction Mode ON', 'Implicit Transaction Mode OFF' ) AS 'Transaction Mode' SELECT @@TRANCOUNT AS OpenTrans COMMIT TRANSACTION SELECT @@TRANCOUNT AS OpenTrans
I denne transaksjonen har vi brukt to alternativer @@OPSJON og @@TRANCOUNT. @@OPTOPN gir informasjon om gjeldende SET-alternativer, og @@TRANCOUNT gir BEGIN TRANSACTION-setningen i gjeldende økt.
Utførelse av transaksjonen vil nå returnere utgangen nedenfor:
Eksplisitt transaksjon i SQL Server
En eksplisitt transaksjon må defineres gjennom kommandoen BEGIN TRANSACTION fordi den identifiserer startpunktet for den eksplisitte transaksjonen. Vi kan definere den eksplisitte transaksjonen i SQL Server som nedenfor:
BEGIN TRANSACTION [ @trans_name_variable [WITH MARK ['description']]]
I syntaksen indikerer alternativet trans_name et unikt navn på en transaksjon. De @trans_name_var indikerer en brukerdefinert variabel som lagrer transaksjonsnavnet. Til slutt, den MERKE alternativet lar oss merke en spesifikk transaksjon i loggfilen.
Den eksplisitte transaksjonen gjennom BEGIN TRANSACTION-kommandoen fikk en lås avhengig av isolasjonsnivået til de transaksjonsrelaterte ressursene. Det bidrar til å redusere låseproblemer. Se eksemplet nedenfor:
BEGIN TRANSACTION UPDATE Product SET Quantity = 15 WHERE Product_id = 114 SELECT @@TRANCOUNT AS OpenTrans COMMIT TRANSACTION SELECT @@TRANCOUNT AS OpenTrans
Her er utgangen:
erstatte fra streng i java
Merket transaksjon i SQL Server
Den merkede transaksjonen brukes til å legge til en beskrivelse av en bestemt transaksjon i loggfilene. Vi kan bruke det som et gjenopprettingspunkt i stedet for en dato og et klokkeslett når vi gjenoppretter en database til en tidligere tilstand. Vi må vite at merket legges til i loggfilene bare når den merkede transaksjonen endrer databasen. Vi kan forstå konseptet ved å bruke følgende eksempel.
Anta at vi har modifisert databasen ved et uhell, og vi vet ikke det nøyaktige øyeblikket for dataendringen; i så fall kan datagjenoppretting ta lang tid. Men hvis vi bruker de merkede transaksjonene, kan det være et nyttig verktøy for å bestemme nøyaktig tidspunkt for dataendringer.
Følgende syntaks illustrerer den merkede transaksjonen i SQL Server:
BEGIN TRANSACTION trans_name WITH MARK 'description';
Her må vi definere navnet på transaksjonen og deretter legge til MED MARK-alternativet. I eksemplet nedenfor skal vi slette poster og legge til merket i loggfilen:
BEGIN TRANSACTION DeleteProduct WITH MARK 'Deleted Product with id = 117' DELETE Product WHERE Product_id = 117 COMMIT TRANSACTION DeleteProduct
De logmarkhistorie tabellen er inkludert i msdb database og lagrer informasjon om hver merket transaksjon som er utført. Utfør setningen nedenfor for å få detaljene fra logmarkhistory-tabellen:
SELECT * FROM msdb.dbo.logmarkhistory
Navngitt Transaksjon i SQL Server
Vi kan også gi et navn for transaksjonen vår i SQL Server. Det anbefales alltid å bruke den navngitte transaksjonen når du arbeider med mange transaksjoner i en enkelt spørring. Eksempelet nedenfor forklarer hvordan du endrer navn på en transaksjon:
BEGIN TRANSACTION AddProduct INSERT INTO Product VALUES(118, 'Desktop', 25000, 15) UPDATE Product SET Product_name = 'Pen Drive' WHERE Product_id = 117 COMMIT TRANSACTION AddProduct
Her er utgangen:
Konklusjon
Denne artikkelen vil gi en fullstendig oversikt over transaksjonen i SQL Server-setninger. Transaksjoner er nyttige i relasjonsdatabasesystemer fordi de sikrer databaseintegritet.