Una transacció a SQL Server és a grup seqüencial d'instruccions o consultes per realitzar tasques individuals o múltiples en una base de dades. Cada transacció pot tenir una única operació de lectura, escriptura, actualització o supressió o una combinació de totes aquestes operacions. Cada transacció ha de passar dues coses a SQL Server:
- O totes les modificacions tenen èxit quan es confirma la transacció.
- O bé, totes les modificacions es desfereixen quan la transacció es reverti.
Una transacció no pot tenir èxit fins que s'hagin completat totes les operacions del conjunt. Significa que si algun argument falla, l'operació de transacció fallarà. Cada transacció comença amb la primera instrucció SQL executable i acaba quan troba una confirmació o una recuperació, ja sigui de manera explícita o implícita. Fa servir el COMPROMÈS o RECOLLIDA declaracions de manera explícita, així com implícitament quan s'utilitza una instrucció DDL.
La representació gràfica següent explica el procés de transacció:
L'exemple següent explicarà el concepte d'una transacció:
Aquest exemple utilitzarà el sistema de bases de dades bancàries per explicar el concepte d'una transacció. Suposem que un client bancari vol retirar diners del seu compte mitjançant el mode caixer automàtic. El caixer automàtic pot aconseguir aquesta operació en els tres passos:
- El primer pas és comprovar la disponibilitat de l'import sol·licitat al compte.
- El segon pas dedueix l'import del compte si l'import està disponible i, a continuació, actualitza el saldo del compte.
- El tercer pas és escriure l'operació de retirada de diners al fitxer de registre. Aquest pas escriu que la transacció ha estat correcta o ha fallat. Si té èxit, escriviu la modificació de dades a la base de dades. En cas contrari, la transacció es tornarà al seu estat anterior.
El principi bàsic darrere de les transaccions és que si una de les declaracions retorna un error, es reverteix tot el conjunt de canvis per garantir la integritat de les dades. I si les transaccions tenen èxit, tots els canvis seran permanents a la base de dades. Per tant, si hi ha un tall de corrent o altres problemes en retirar diners d'un caixer automàtic, les transaccions garanteixen que el nostre saldo es mantingui constant. Una declaració de transacció realitza millor aquestes operacions perquè les quatre propietats clau de la transacció fan que totes les operacions siguin més precises i coherents. Les quatre propietats de la transacció s'anomenen ACID.
Propietats de la transacció
Les propietats de la transacció s'anomenen propietat d'ACID (atomicitat, consistència, aïllament, durabilitat), que es comenten amb detall a continuació:
Atomicitat: Aquesta propietat garanteix que totes les declaracions o operacions incloses a la transacció s'han de realitzar correctament. En cas contrari, s'avortarà tota la transacció i totes les operacions es tornaran al seu estat anterior quan alguna operació falla.
Coherència: Aquesta propietat garanteix que la base de dades canvia d'estat només quan una transacció es confirmarà correctament. També s'encarrega de protegir les dades dels bloquejos.
Aïllament: Aquesta propietat garanteix que totes les transaccions estan aïllades d'altres transaccions, és a dir, que cada operació de la transacció s'opera de manera independent. També garanteix que les declaracions siguin transparents entre si.
S'ha inserit la targeta SIM però no hi ha servei Android
Durabilitat: Aquesta propietat garanteix que el resultat de les transaccions compromeses persisteix a la base de dades de manera permanent, fins i tot si el sistema falla o falla.
Modes de transacció a SQL Server
Hi ha tres modes de transacció diferents que pot utilitzar SQL Server:
Mode de transacció de confirmació automàtica: És el mode de transacció predeterminat de l'SQL Server. Avaluarà cada instrucció SQL com una transacció i els resultats es comprometen o es revertiran en conseqüència. D'aquesta manera, les declaracions reeixides es confirmen immediatament, mentre que les declaracions fallides es revertiran immediatament.
Mode de transacció implícita. Aquest mode permet que SQL Server comenci la transacció implícita per a cada instrucció DML, però requereix explícitament l'ús d'ordres commit o rollback al final de les sentències.
Mode de transacció explícit: Aquest mode el defineix l'usuari que ens permet identificar exactament els punts d'inici i finalització d'una transacció. S'avortarà automàticament en cas d'error fatal.
Control de transaccions
Les ordres que s'utilitzen per controlar les transaccions són les següents:
NOTA: només podem utilitzar instruccions DML (INSERT, UPDATE i DELETE) per a les ordres del llenguatge de control de transaccions. No els podem utilitzar mentre creem o deixem caure taules perquè aquestes operacions es comprometen a la base de dades automàticament.
Estat de la transacció
Indica com van les transaccions durant la seva vida. Descriu l'estat actual de la transacció i com es processarà en el futur. Aquests estats defineixen les regles que determinen si una transacció es confirma o s'avorta.
Anem a descriure cada estat de transacció a SQL Server:
Estat actiu: La transacció està en un estat actiu mentre s'executen les instruccions de la transacció. Es canvia a la 'Estat parcialment compromès' si totes les operacions de 'llegir i escriure' es completen sense errors. Si alguna instrucció falla, canvia a l'estat d'error.
Compromesos parcialment: Quan s'han completat totes les operacions de lectura i escriptura, el canvi es fa a la memòria principal o al buffer local. L'estat aniria a 'estat compromès' si els canvis es fan permanents a la base de dades. En cas contrari, passa a l''estat fallit'.
Estat fallit: Una transacció passa a l'estat d'error quan falla una instrucció de qualsevol transacció o falla una modificació permanent a la base de dades.
Estat avortat: La transacció es mou des de a 'estat fallit' a un 'estat avortat' quan es produeixi qualsevol tipus de fallada. Els canvis s'eliminen o es retreuen perquè aquests canvis només es fan a la memòria intermèdia local o a la memòria principal en estats anteriors.
Estat compromès: Una transacció es completa i passa a aquest estat quan els canvis es fan permanents a la base de dades i finalitzen al 'estat acabat'.
Estat acabat: Si no hi ha cap retrocés i la transacció es troba a 'estat compromès' el sistema és coherent i preparat per a una nova transacció mentre s'acaba l'antiga.
Implementació de la transacció en SQL Server
Prenguem alguns exemples per entendre com podem implementar la transacció a SQL Server. Aquí farem servir el 'Producte' taula per demostrar tots els estats de transacció.
Els següents scripts SQL creen la taula Producte a la base de dades seleccionada:
CREATE TABLE Product ( Product_id INT PRIMARY KEY, Product_name VARCHAR(40), Price INT, Quantity INT )
A continuació, executeu els scripts següents per inserir dades a aquesta taula:
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);
Executeu la instrucció SELECT per verificar les dades:
Exemple de transacció COMMIT
És una bona idea dividir les declaracions SQL utilitzades a la transacció en diverses parts lògiques. I llavors, podem decidir si volem confirmar o desfer les dades. Els passos següents il·lustren per crear una transacció:
- Inicieu la transacció amb el COMENÇAR LA TRANSACCIÓ comandament.
- Escriu les sentències SQL i divideix-les segons les nostres necessitats
- Utilitzar el COMPROMÈS extracte per completar la transacció i desar els canvis permanentment.
A continuació es mostren les ordres que expliquen les operacions COMMIT a 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
Si no es troba cap error, veurem la següent sortida on cada instrucció SQL de la transacció s'executa de manera independent:
Les sentències INSERT i UPDATE no es poden revertir un cop s'hagi confirmat la transacció. Quan verifiquem la taula després de l'operació de confirmació, veurem les dades següents:
Exemple de transacció ROLLBACK
Utilitzarem l'ordre ROLLBACK per desfer qualsevol transacció que encara no s'hagi desat a la base de dades i tornar al punt on va començar la transacció. L'exemple següent explica l'operació ROLLBACK a 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
Un cop executem la transacció anterior, podem veure que s'executarà correctament. Tanmateix, no afectarà cap canvi a la base de dades perquè fins que no executem la instrucció COMMIT o ROLLBACK, els canvis no es poden convertir en permanents. Per tant, tenim l'opció d'utilitzar l'ordre de transacció ROLLBACK per revertir totes les operacions de la base de dades. Aquí teniu l'extracte complet de la transacció:
-- 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
Ús de la variable global @@Error en transaccions:
Aquesta variable és s'utilitza per comprovar si hi ha un error o no. L'exemple següent explica el seu concepte. Aquí primer començarem la transacció utilitzant l'ordre BEGIN i després escriurem dues sentències d'inserció. A continuació, utilitzarem la variable del sistema global @@ERROR en el Declaració SI per comprovar un error. Si el valor és superior a 0, vol dir que hi ha algun error. Ara, la transacció és rollback; en cas contrari, la transacció es compromet.
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
Quan s'executi la transacció anterior, ens adonarem que s'ha revertit. Es deu al nostre intent d'inserir un valor duplicat a la columna Clau primària.
Transacció de retrocés automàtic
La majoria de les transaccions contenen més d'una consulta. Durant l'execució de la transacció, si alguna de les sentències SQL produeix un error, no es produeixen modificacions a la base de dades i les sentències restants no s'executen. Aquest concepte es coneix com a transacció de retrocés automàtic a SQL Server. Utilitzem un exemple senzill per demostrar aquest procés.
BEGIN TRANSACTION INSERT INTO Product VALUES(118, 'Desktop', 25000, 15) UPDATE Product SET Quantity = 'ten' WHERE Product_id = 113 SELECT * FROM Product COMMIT TRANSACTION
Aquesta transacció produeix la següent sortida:
programa d'encapsulació
En aquesta sortida, podem veure que la instrucció insert s'ha executat correctament. Tanmateix, en executar la instrucció d'actualització es va trobar un error a causa del problema de conversió del tipus de dades. En aquest cas, l'SQL Server no permet cap canvi a la base de dades, el que significa que l'operació d'inserció no afegeix cap valor i la instrucció select no s'executa.
Punt d'estalvi a les transaccions
El punt de desat insereix una marca especial en una transacció que ens permet revertir tots els canvis executats després del punt de desat. També s'utilitza per revertir qualsevol part específica de la transacció en lloc de tota la transacció. Podem definir-ho utilitzant el DESA LA TRANSACCIÓ nom_sp declaració. L'exemple següent explicarà l'ús del punt de salvament en les transaccions que compromet la instrucció d'inserció i anul·la la instrucció de supressió.
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;
Vegeu el resultat següent on podem veure que s'elimina l'identificador del producte 116 i s'insereix 117 a la primera sortida. No obstant això, a la segona sortida, l'operació d'eliminació es torna enrere a causa del punt de desat.
Com alliberar un punt de desat en una transacció?
Allibera el punt de desat s'utilitza per eliminar el punt de desat anomenat de la transacció actual sense revertir els resultats de les consultes executades després del punt de desat. MySQL té aquesta ordre, però SQL Server no proporciona cap ordre per alliberar un punt de desat. En lloc d'això, s'alliberen automàticament al final d'una transacció de confirmació o d'anul·lació, de manera que no ens hem de preocupar de manera intermèdia.
Transacció implícita a SQL Server
Podem definir una transacció implícita activant l'opció IMPLICIT_TRANSACTIONS. L'exemple següent explicarà aquest concepte fàcilment:
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
En aquesta transacció, hem utilitzat dues opcions @@OPTION i @@TRANCOUNT. @@OPTOPN proporciona la informació sobre les opcions SET actuals i @@TRANCOUNT proporciona la instrucció BEGIN TRANSACTION a la sessió actual.
Ara, executar la transacció retornarà la sortida següent:
Transacció explícita a SQL Server
Una transacció explícita s'ha de definir mitjançant l'ordre BEGIN TRANSACTION perquè identifica el punt de partida de la transacció explícita. Podem definir la transacció explícita a SQL Server de la següent manera:
BEGIN TRANSACTION [ @trans_name_variable [WITH MARK ['description']]]
A la sintaxi, l'opció trans_name indica un nom únic d'una transacció. El @trans_name_var indica una variable definida per l'usuari que emmagatzema el nom de la transacció. Finalment, el SENYAL L'opció ens permet marcar una transacció específica al fitxer de registre.
La transacció explícita mitjançant l'ordre BEGIN TRANSACTION va adquirir un bloqueig en funció del nivell d'aïllament dels recursos relacionats amb la transacció. Ajuda a reduir els problemes de bloqueig. Vegeu l'exemple següent:
BEGIN TRANSACTION UPDATE Product SET Quantity = 15 WHERE Product_id = 114 SELECT @@TRANCOUNT AS OpenTrans COMMIT TRANSACTION SELECT @@TRANCOUNT AS OpenTrans
Aquí teniu la sortida:
Transacció marcada a SQL Server
La transacció marcada s'utilitza per afegir una descripció a una transacció específica als fitxers de registre. Podem utilitzar-lo com a punt de recuperació en lloc d'una data i hora quan es restaura una base de dades a un estat anterior. Hem de saber que la marca s'afegeix als fitxers de registre només quan la transacció marcada modifica la base de dades. Podem entendre el seu concepte utilitzant l'exemple següent.
Suposem que hem modificat la base de dades de manera accidental i no sabem el moment exacte de l'alteració de les dades; en aquest cas, la recuperació de dades pot trigar molt de temps. Tanmateix, si fem servir les transaccions marcades, pot ser una eina útil per determinar el moment exacte de les alteracions de les dades.
.Equal java
La sintaxi següent il·lustra la transacció marcada a SQL Server:
BEGIN TRANSACTION trans_name WITH MARK 'description';
Aquí hem de definir el nom de la transacció i després afegir l'opció AMB MARCA. A l'exemple següent, suprimirem registres i afegirem la marca al fitxer de registre:
BEGIN TRANSACTION DeleteProduct WITH MARK 'Deleted Product with id = 117' DELETE Product WHERE Product_id = 117 COMMIT TRANSACTION DeleteProduct
El logmarkhistory la taula està inclosa a la base de dades msdb i emmagatzema informació sobre cada transacció marcada que s'ha compromès. Executeu la instrucció següent per obtenir els detalls de la taula logmarkhistory:
SELECT * FROM msdb.dbo.logmarkhistory
Transacció anomenada a SQL Server
També podem proporcionar un nom per a la nostra transacció a SQL Server. Sempre es recomana utilitzar la transacció anomenada quan es treballa amb moltes transaccions en una sola consulta. L'exemple següent explica com canviar el nom d'una transacció:
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
Aquí teniu la sortida:
Conclusió
Aquest article donarà una visió general completa de la transacció a les declaracions de SQL Server. Les transaccions són útils en els sistemes de bases de dades relacionals perquè garanteixen la integritat de la base de dades.