Controllo della durabilità delle transazioniControl Transaction Durability

QUESTO ARGOMENTO SI APPLICA A:sìSQL Server (a partire dalla versione 2014)sìDatabase SQL di AzurenoAzure SQL Data Warehouse noParallel Data Warehouse THIS TOPIC APPLIES TO:yesSQL Server (starting with 2014)yesAzure SQL DatabasenoAzure SQL Data Warehouse noParallel Data Warehouse

Il commit delle transazioni di SQL ServerSQL Server può essere completamente durevole, l'impostazione predefinita di SQL ServerSQL Server , oppure con durabilità ritardata (noto come Lazy Commit). default, or delayed durable (also known as lazy commit).

Il commit delle transazioni completamente durevole è sincrono e segnala il completamento del commit restituendo il controllo al client solo dopo che i record del log per la transazione vengono scritti su disco.Fully durable transaction commits are synchronous and report a commit as successful and return control to the client only after the log records for the transaction are written to disk. Il commit delle transazioni con durabilità ritardata è asincrono e segnala il completamento del commit prima che i record del log per la transazione vengano scritti su disco.Delayed durable transaction commits are asynchronous and report a commit as successful before the log records for the transaction are written to disk. La scrittura delle voci di log delle transazioni su disco è necessaria affinché una transazione sia durevole.Writing the transaction log entries to disk is required for a transaction to be durable. Le transazioni con durabilità ritardata diventano durevoli quando le voci di log delle transazioni vengono scaricate su disco.Delayed durable transactions become durable when the transaction log entries are flushed to disk.

In questo argomento vengono descritte in dettaglio le transazioni con durabilità ritardata.This topic details delayed durable transactions.

Transazioni completamente durevoli e transazioni con durabilità ritardataFull vs. Delayed Transaction Durability

Le transazioni con durabilità completa e quelle con durabilità ritardata hanno sia vantaggi che svantaggi.Both full and delayed transaction durability have their advantages and disadvantages. Un'applicazione può contenere una combinazione di transazioni completamente durevoli e transazioni con durabilità ritardata.An application can have a mix of fully and delayed durable transactions. È necessario valutare attentamente le esigenze aziendali e determinare quale dei due tipi vi si adatti meglio.You should carefully consider your business needs and how each fits into those needs.

Transazioni con durabilità completaFull transaction durability

Le transazioni completamente durevoli scrivono il log delle transazioni su disco prima di restituire il controllo al client.Fully durable transactions write the transaction log to disk before returning control to the client. Utilizzare le transazioni con durabilità completa ogni volta che:You should use fully durable transactions whenever:

  • Il sistema non può tollerare alcuna perdita di dati.Your system cannot tolerate any data loss.
    Per informazioni sul momento in cui può verificarsi una perdita di dati, vedere la sezione Quando può verificarsi una perdita di dati? .See the section When can I lose data? for information on when you can lose some of your data.

  • Il collo di bottiglia non è causato dalla latenza di scrittura del log delle transazioni.The bottleneck is not due to transaction log write latency.

    Le transazioni con durabilità ritardata riducono la latenza causata dall'I/O del log mantenendo i record del log delle transazioni in memoria e scrivendo nel log delle transazioni in batch, richiedendo pertanto un numero minore di operazioni di I/O.Delayed transaction durability reduces the latency due to log I/O by keeping the transaction log records in memory and writing to the transaction log in batches, thus requiring fewer I/O operations. Le transazioni con durabilità ritardata riducono potenzialmente la contesa di I/O del log, riducendo pertanto le attese del sistema.Delayed transaction durability potentially reduces log I/O contention, thus reducing waits in the system.

    Garanzie delle transazioni con durabilità completaFull Transaction Durability Guarantees

  • Una volta completato il commit della transazione, le modifiche apportate dalla transazione sono visibili alle altre transazioni nel sistema.Once transaction commit succeeds, the changes made by the transaction are visible to the other transactions in the system. Per altre informazioni sui livelli di isolamento delle transazioni, vedere SET TRANSACTION ISOLATION LEVEL (Transact-SQL) o Transactions with Memory-Optimized Tables (Transazioni con tabelle con ottimizzazione per la memoria).For more information about transaction isolation levels, see SET TRANSACTION ISOLATION LEVEL (Transact-SQL) or Transactions with Memory-Optimized Tables.

  • La durabilità è garantita in fase di commit.Durability is guaranteed on commit. I record di log corrispondenti vengono salvati in modo permanente sul disco prima del completamento del commit delle transazioni restituendo il controllo al client.Corresponding log records are persisted to disk before the transaction commit succeeds and returns control to the client.

Transazioni con durabilità ritardataDelayed transaction durability

Le transazioni con durabilità ritardata vengono eseguite utilizzando scritture del log su disco asincrone.Delayed transaction durability is accomplished using asynchronous log writes to disk. I record del log delle transazioni vengono mantenuti in un buffer e vengono scritti su disco quando il buffer si riempie o quando si verifica un evento di scaricamento del buffer.Transaction log records are kept in a buffer and written to disk when the buffer fills or a buffer flushing event takes place. Le transazioni con durabilità ritardata riducono la latenza e la contesa nel sistema in quanto:Delayed transaction durability reduces both latency and contention within the system because:

  • L'elaborazione del commit delle transazioni non attende il completamento delle operazioni di I/O del log e la restituzione del controllo al client.The transaction commit processing does not wait for log IO to finish and return control to the client.

  • La contesa di I/O del log da parte delle transazioni simultanee è meno probabile; al contrario, il buffer del log può essere scaricato su disco in blocchi più grandi, riducendo la contesa e aumentando la velocità effettiva.Concurrent transactions are less likely to contend for log IO; instead, the log buffer can be flushed to disk in larger chunks, reducing contention, and increasing throughput.

    Nota

    È comunque possibile che si verifichi una contesa di I/O del log se esiste un livello elevato di concorrenza, specialmente se il buffer del log si riempie più velocemente di quanto si scarichi.You may still have log I/O contention if there is a high degree of concurrency, particularly if you fill up the log buffer faster than you flush it.

    Quando utilizzare le transazioni con durabilità ritardataWhen to use delayed transaction durability

    Alcuni dei casi in cui è possibile trarre vantaggio dall'utilizzo delle transazioni con durabilità ritardata sono:Some of the cases in which you could benefit from using delayed transaction durability are:

    Possibilità di tollerare un'eventuale perdita di dati. You can tolerate some data loss.
    Se è possibile tollerare un'eventuale perdita di dati, ad esempio la perdita di singoli record non cruciali, purché si disponga della maggior parte dei dati, potrebbe essere opportuno considerare la durabilità ritardata.If you can tolerate some data loss, for example, where individual records are not critical as long as you have most of the data, then delayed durability may be worth considering. Se non è possibile tollerare un'eventuale perdita di dati, non utilizzare le transazioni con durabilità ritardata.If you cannot tolerate any data loss, do not use delayed transaction durability.

    Collo di bottiglia nella scrittura del log delle transazioni. You are experiencing a bottleneck on transaction log writes.
    Se i problemi di prestazioni sono dovuti alla latenza nella scrittura del log delle transazioni, l'applicazione probabilmente trarrà vantaggio dall'utilizzo delle transazioni con durabilità ritardata.If your performance issues are due to latency in transaction log writes, your application will likely benefit from using delayed transaction durability.

    Carichi di lavoro con una frequenza elevata di contesa. Your workloads have a high contention rate.
    Se nel sistema sono presenti carichi di lavoro con un livello elevato di contesa, si perde molto tempo nell'attesa del rilascio dei blocchi.If your system has workloads with a high contention level much time is lost waiting for locks to be released. Le transazioni con durabilità ritardata riducono il tempo di commit rilasciando i blocchi più velocemente con una velocità effettiva più elevata.Delayed transaction durability reduces commit time and thus releases locks faster which results in higher throughput.

    Garanzie delle transazioni con durabilità ritardataDelayed Transaction Durability Guarantees

  • Una volta completato il commit della transazione, le modifiche apportate dalla transazione sono visibili alle altre transazioni nel sistema.Once transaction commit succeeds, the changes made by the transaction are visible to the other transactions in the system.

  • La durabilità delle transazioni viene garantita solo in seguito a uno scaricamento su disco del log delle transazioni in memoria.Transaction durability is guaranteed only following a flush of the in-memory transaction log to disk. Il log delle transazioni in memoria viene scaricato su disco quando:The in-memory transaction log is flushed to disk when:

    • Una transazione completamente durevole nello stesso database apporta una modifica nel database e ne viene completato il commit.A fully durable transaction in the same database makes a change in the database and successfully commits.

    • L'utente esegue correttamente la stored procedure di sistema sp_flush_log .The user executes the system stored procedure sp_flush_log successfully.

      Se viene completato il commit di una transazione completamente durevole o di sp_flush_log, si è certi che tutte le transazioni con durabilità ritardata di cui è stato eseguito il commit in precedenza sono state rese durevoli.If a fully durable transaction or sp_flush_log successfully commits, all previously committed delayed durability transactions are guaranteed to have been made durable.

    • SQL ServerSQL Server prova a scaricare il log su disco sia in base alla generazione dei log che all'intervallo di tempo, anche se tutte le transazioni sono con durabilità ritardata. does attempt to flush the log to disk both based on log generation and on timing, even if all the transactions are delayed durable. Questo tentativo in genere riesce se il dispositivo I/O è aggiornato.This usually succeeds if the IO device is keeping up. Tuttavia, SQL ServerSQL Server non fornisce alcuna garanzia effettiva di durabilità oltre alle transazioni durevoli e a sp_flush_log.However, SQL ServerSQL Server does not provide any hard durability guarantees other than durable transactions and sp_flush_log.

Come controllare la durabilità delle transazioniHow to control transaction durability

Database level control Database level control

L'amministratore del database può controllare se gli utenti possono utilizzare le transazioni con durabilità ritardata in un database con l'istruzione seguente.You, the DBA, can control whether users can use delayed transaction durability on a database with the following statement. È necessario impostare il valore per la durabilità ritardata con ALTER DATABASE.You must set the delayed durability setting with ALTER DATABASE.

ALTER DATABASE … SET DELAYED_DURABILITY = { DISABLED | ALLOWED | FORCED }    

DISABLED DISABLED
[impostazione predefinita] Con questa impostazione, tutte le transazioni di cui è stato eseguito il commit nel database sono completamente durevoli, indipendentemente dall'impostazione del livello di commit (DELAYED_DURABILITY=[ON | OFF]).[default] With this setting, all transactions that commit on the database are fully durable, regardless of the commit level setting (DELAYED_DURABILITY=[ON | OFF]). Non è necessaria alcuna modifica e ricompilazione delle stored procedure.There is no need for stored procedure change and recompilation. In questo modo è possibile garantire che i dati non verranno in alcun modo messi in pericolo dalla durabilità ritardata.This allows you to ensure that no data is ever put at risk by delayed durability.

ALLOWED ALLOWED
Con questa impostazione, la durabilità di ogni transazione viene determinata a livello di transazione - DELAYED_DURABILITY = { OFF | ON }.With this setting, each transaction’s durability is determined at the transaction level – DELAYED_DURABILITY = { OFF | ON }. Per altre informazioni, vedere Controllo a livello di blocco atomico - Stored procedure compilate in modo nativo e Controllo a livello di COMMIT – Transact-SQL .See Atomic block level control – Natively Compiled Stored Procedures and COMMIT level control –Transact-SQL for more information.

FORCED FORCED
Con questa impostazione, ogni transazione di cui viene eseguito il commit nel database è con durabilità ritardata.With this setting, every transaction that commits on the database is delayed durable. Indipendentemente dal fatto che venga specificata una transazione completamente durevole (DELAYED_DURABILITY = OFF) o non venga specificata alcuna impostazione, la transazione è con durabilità ritardata.Whether the transaction specifies fully durable (DELAYED_DURABILITY = OFF) or makes no specification, the transaction is delayed durable. Tale impostazione risulta utile quando è necessario specificare le transazioni con durabilità ritardata per un database e non si desidera modificare il codice dell'applicazione.This setting is useful when delayed transaction durability is useful for a database and you do not want to change any application code.

Atomic block level control – Natively Compiled Stored Procedures Atomic block level control – Natively Compiled Stored Procedures

Il codice seguente va inserito nel blocco atomico.The following code goes inside the atomic block.

DELAYED_DURABILITY = { OFF | ON }    

OFF OFF
[impostazione predefinita] La transazione è completamente durevole, a meno che non sia attiva l'opzione di database DELAYED_DURABLITY = FORCED, nel qual caso il commit è asincrono e pertanto con durabilità ritardata.[default] The transaction is fully durable, unless the database option DELAYED_DURABLITY = FORCED is in effect, in which case the commit is asynchronous and thus delayed durable. Per altre informazioni, vedere Controllo a livello di database .See Database level control for more information.

ON ON
La transazione è con durabilità ritardata, a meno che non sia attiva l'opzione di database DELAYED_DURABLITY = DISABLED, nel qual caso il commit è sincrono e pertanto completamente durevole.The transaction is delayed durable, unless the database option DELAYED_DURABLITY = DISABLED is in effect, in which case the commit is synchronous and thus fully durable. Per altre informazioni, vedere Controllo a livello di database .See Database level control for more information.

Codice di esempio:Example Code:

CREATE PROCEDURE <procedureName> …    
WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER    
AS BEGIN ATOMIC WITH     
(    
    DELAYED_DURABILITY = ON,    
    TRANSACTION ISOLATION LEVEL = SNAPSHOT,    
    LANGUAGE = N'English'    
    …    
)    
END    

Tabella 1: durabilità nei blocchi atomiciTable 1: Durability in Atomic Blocks

Opzione di durabilità nei blocchi atomiciAtomic block durability option Nessuna transazione esistenteNo existing transaction Transazione in corso (completamente durevole o con durabilità ritardata)Transaction in process (fully or delayed durable)
DELAYED_DURABILITY = OFFDELAYED_DURABILITY = OFF Il blocco atomico avvia una nuova transazione completamente durevole.Atomic block starts a new fully durable transaction. Il blocco atomico crea un punto di salvataggio nella transazione esistente, quindi avvia una nuova transazione.Atomic block creates a save point in the existing transaction, then begins the new transaction.
DELAYED_DURABILITY = ONDELAYED_DURABILITY = ON Il blocco atomico avvia una nuova transazione con durabilità ritardata.Atomic block starts a new delayed durable transaction. Il blocco atomico crea un punto di salvataggio nella transazione esistente, quindi avvia una nuova transazione.Atomic block creates a save point in the existing transaction, then begins the new transaction.

COMMIT level control – Transact-SQLTransact-SQL COMMIT level control – Transact-SQLTransact-SQL

La sintassi di COMMIT viene estesa in modo da poter forzare le transazioni con durabilità ritardata.The COMMIT syntax is extended so you can force delayed transaction durability. Se DELAYED_DURABILITY è DISABLED o FORCED a livello di database (vedere sopra) questa opzione di COMMIT viene ignorata.If DELAYED_DURABILITY is DISABLED or FORCED at the database level (see above) this COMMIT option is ignored.

COMMIT [ { TRAN | TRANSACTION } ] [ transaction_name | @tran_name_variable ] ] [ WITH ( DELAYED_DURABILITY = { OFF | ON } ) ]    

OFF OFF
[impostazione predefinita] Il COMMIT della transazione è completamente durevole, a meno che non sia attiva l'opzione di database DELAYED_DURABLITY = FORCED, nel qual caso il COMMIT è asincrono e pertanto con durabilità ritardata.[default] The transaction COMMIT is fully durable, unless the database option DELAYED_DURABLITY = FORCED is in effect, in which case the COMMIT is asynchronous and thus delayed durable. Per altre informazioni, vedere Controllo a livello di database .See Database level control for more information.

ON ON
Il COMMIT della transazione è con durabilità ritardata, a meno che non sia attiva l'opzione di database DELAYED_DURABLITY = DISABLED, nel qual caso il COMMIT è sincrono e pertanto completamente durevole.The transaction COMMIT is delayed durable, unless the database option DELAYED_DURABLITY = DISABLED is in effect, in which case the COMMIT is synchronous and thus fully durable. Per altre informazioni, vedere Controllo a livello di database .See Database level control for more information.

Riepilogo delle opzioni e relative interazioniSummary of options and their interactions

Nella tabella seguente vengono riepilogate le interazioni tra le impostazioni di durabilità ritardata a livello di database e le impostazioni a livello di commit.This table summarizes the interactions between database level delayed durability settings and commit level settings. Le impostazioni a livello di database hanno sempre la precedenza sulle impostazioni a livello di commit.Database level settings always take precedence over commit level settings.

Impostazione di COMMIT/Impostazione di databaseCOMMIT setting/Database setting DELAYED_DURABILITY = DISABLEDDELAYED_DURABILITY = DISABLED DELAYED_DURABILITY = ALLOWEDDELAYED_DURABILITY = ALLOWED DELAYED_DURABILITY = FORCEDDELAYED_DURABILITY = FORCED
DELAYED_DURABILITY = OFF Transazioni a livello di database.DELAYED_DURABILITY = OFF Database level transactions. La transazione è completamente durevole.Transaction is fully durable. La transazione è completamente durevole.Transaction is fully durable. La transazione è con durabilità ritardata.Transaction is delayed durable.
DELAYED_DURABILITY = ON Transazioni a livello di database.DELAYED_DURABILITY = ON Database level transactions. La transazione è completamente durevole.Transaction is fully durable. La transazione è con durabilità ritardata.Transaction is delayed durable. La transazione è con durabilità ritardata.Transaction is delayed durable.
DELAYED_DURABILITY = OFF Transazione distribuita o tra database.DELAYED_DURABILITY = OFF Cross database or distributed transaction. La transazione è completamente durevole.Transaction is fully durable. La transazione è completamente durevole.Transaction is fully durable. La transazione è completamente durevole.Transaction is fully durable.
DELAYED_DURABILITY = ON Transazione distribuita o tra database.DELAYED_DURABILITY = ON Cross database or distributed transaction. La transazione è completamente durevole.Transaction is fully durable. La transazione è completamente durevole.Transaction is fully durable. La transazione è completamente durevole.Transaction is fully durable.

Come forzare lo scaricamento di un log delle transazioniHow to force a transaction log flush

Sono disponibili due metodi per forzare lo scaricamento del log delle transazioni su disco.There are two means to force flush the transaction log to disk.

  • Eseguire una transazione completamente durevole che modifica lo stesso database.Execute any fully durable transaction that alters the same database. In questo modo viene forzato lo scaricamento su disco dei record del log di tutte transazioni con durabilità ritardata di cui è stato eseguito il commit in precedenza.This forces a flush of the log records of all preceding committed delayed durability transactions to disk.

  • Eseguire la stored procedure di sistema sp_flush_log.Execute the system stored procedure sp_flush_log. In questo modo viene forzato lo scaricamento su disco dei record del log di tutte transazioni con durabilità ritardata di cui è stato eseguito il commit in precedenza.This procedure forces a flush of the log records of all preceding committed delayed durable transactions to disk. Per altre informazioni, vedere sys.sp_flush_log (Transact-SQL).For more information see sys.sp_flush_log (Transact-SQL).

Durabilità ritardata e altre funzionalità di SQL ServerSQL Server Delayed durability and other SQL ServerSQL Server features

Rilevamento delle modifiche e Change Data Capture Change tracking and change data capture
Tutte le transazioni con rilevamento delle modifiche sono completamente durevoli.All transactions with change tracking are fully durable. Una transazione dispone della proprietà di rilevamento delle modifiche se esegue operazioni di scrittura in tabelle abilitate per il rilevamento delle modifiche.A transaction has the change tracking property if it does any write operations to tables that are enabled for change tracking. L'uso di durabilità posticipata non è supportato per i database che usano Change Data Capture (CDC).The use of delayed durability is not supported for databases which use change data capture (CDC).

Recupero a seguito dell'arresto anomalo del sistema Crash recovery
La coerenza è garantita, ma alcune modifiche delle transazioni con durabilità ritardata di cui è stato eseguito il commit possono andare perse.Consistency is guaranteed, but some changes from delayed durable transactions that have committed may be lost.

Transazione distribuita o tra database Cross-database and DTC
Se una transazione è distribuita o tra database, è completamente durevole, indipendentemente da qualsiasi impostazione del commit della transazione o del database.If a transaction is cross-database or distributed, it is fully durable, regardless of any database or transaction commit setting.

Gruppi di disponibilità AlwaysOn e mirroring Always On Availability Groups and Mirroring
Le transazioni con durabilità ritardata non garantiscono alcuna durabilità nel database primario né in quelli secondari.Delayed durable transactions do not guarantee any durability on either the primary or any of the secondaries. Inoltre, non garantiscono informazioni sulla transazione nel database secondario.In addition, they do not guarantee any knowledge about the transaction at the secondary. Dopo il commit, il controllo viene restituito al client prima che venga ricevuto un acknowledgement da un database secondario sincrono.After commit, control is returned to the client before any acknowledgement is received from any synchronous secondary. La replica in repliche secondarie continua a verificarsi come scaricamento su disco per la replica primaria.Replication to secondary replicas does continue to happen as flush to disk on the primary happens.

Clustering di failover Failover clustering
Alcune scritture delle transazioni con durabilità ritardata potrebbero andare perse.Some delayed durable transaction writes might be lost.

Replica transazionale Transaction Replication
La replica transazionale non supporta le transazioni con durabilità ritardata.Delayed durable transactions is not supported with Transactional Replication.

Log shipping Log shipping
Solo le transazioni che sono diventate durevoli vengono incluse nel log shipping.Only transactions that have been made durable are included in the log that is shipped.

Backup del log Log Backup
Solo le transazioni che sono diventate durevoli vengono incluse nel backup.Only transactions that have been made durable are included in the backup.

When can I lose data? When can I lose data?

Se si implementa la durabilità ritardata in una delle tabelle, è importante comprendere che in determinate circostanze può verificarsi una perdita di dati.If you implement delayed durability on any of your tables, you should understand that certain circumstances can lead to data loss. Se non è possibile tollerare un'eventuale perdita di dati, è consigliabile non usare la durabilità ritardata nelle tabelle.If you cannot tolerate any data loss, you should not use delayed durability on your tables.

Eventi irreversibiliCatastrophic events

Nel caso di un evento irreversibile, come ad esempio un arresto anomalo del server, si verificherà una perdita di dati per tutte le transazioni di cui è stato eseguito il commit che non sono state salvate su disco.In the case of a catastrophic event, like a server crash, you will lose the data for all committed transactions that have not been saved to disk. Le transazioni con durabilità ritardata vengono salvate su disco ogni volta che in una tabella del database (durevole con ottimizzazione per la memoria o basata su disco) viene eseguita una transazione completamente durevole o viene chiamato sp_flush_log .Delayed durable transactions are saved to disk whenever a fully durable transaction is executed against any table (durable memory-optimized or disk-based) in the database, or sp_flush_log is called. Se si usano le transazioni con durabilità ritardata, è possibile creare una tabella di piccole dimensioni nel database da aggiornare periodicamente oppure è possibile chiamare periodicamente sp_flush_log per salvare tutte le transazioni in sospeso di cui è stato eseguito il commit.If you are using delayed durable transactions, you may want to create a small table in the database that you can periodically update or periodically call sp_flush_log to save all outstanding committed transactions. Inoltre, il log delle transazioni viene scaricato ogni volta che diventa pieno, condizione che però è difficile da prevedere e impossibile da controllare.The transaction log also flushes whenever it becomes full, but that is hard to predict and impossible to control.

Arresto e riavvio di SQL ServerSQL Server shutdown and restart

Per la durabilità ritardata non esiste alcuna differenza tra un arresto imprevisto e un arresto/riavvio previsto di SQL ServerSQL Server.For delayed durability, there is no difference between an unexpected shutdown and an expected shutdown/restart of SQL ServerSQL Server. Analogamente agli eventi irreversibili, occorre prevedere la possibilità di una perdita di dati.Like catastrophic events, you should plan for data loss. In un arresto/riavvio pianificato alcune transazioni che non sono state scritte su dico possono essere prima salvate su disco ma non è una condizione che è possibile pianificare.In a planned shutdown/restart some transactions that have not been written to disk may first be saved to disk, but you should not plan on it. Considerare un arresto/riavvio, indipendentemente che sia pianificato o meno, allo stesso modo di un evento irreversibile in cui può verificarsi una perdita di dati.Plan as though a shutdown/restart, whether planned or unplanned, loses the data the same as a catastrophic event.

Vedere ancheSee Also

Transazioni in tabelle con ottimizzazione per la memoriaTransactions with Memory-Optimized Tables