Metodo C # per bloccare la tabella di SQL Server

Ho un programma C # che deve eseguire un gruppo di aggiornamenti di massa (20k +) su una tabella di SQL Server. Poiché altri utenti possono aggiornare questi record uno alla volta tramite un sito Web intranet, è necessario creare il programma C # con la capacità di bloccare la tabella. Una volta che la tabella è bloccata per impedire a un altro utente di apportare modifiche / ricerche, sarà necessario preformare gli aggiornamenti / inserti richiesti.

Dato che stiamo elaborando così tanti record, non possiamo usare TransactionScope (sembrava il modo più semplice all’inizio) a causa del fatto che la nostra transazione finisce per essere gestita dal servizio MSDTC . Dobbiamo usare un altro metodo.

Sulla base di ciò che ho letto su Internet utilizzando un object SqlTransaction sembrava essere il metodo migliore, tuttavia non riesco a bloccare il tavolo. Quando il programma è in esecuzione e passo al codice sottostante, sono comunque in grado di eseguire aggiornamenti e ricerche tramite il sito intranet.

La mia domanda è duplice. Sto usando SqlTransaction correttamente? In tal caso (o anche se non lo è) esiste un metodo migliore per ottenere un blocco tabella che consente al programma corrente di eseguire ricerche e preformare gli aggiornamenti?

Vorrei che il tavolo fosse bloccato mentre il programma esegue il codice qui sotto.

C #

 SqlConnection dbConnection = new SqlConnection(dbConn); dbConnection.Open(); using (SqlTransaction transaction = dbConnection.BeginTransaction(IsolationLevel.Serializable)) { //Instantiate validation object with zip and channel values _allRecords = GetRecords(); validation = new Validation(); validation.SetLists(_allRecords); while (_reader.Read()) { try { record = new ZipCodeTerritory(); _errorMsg = string.Empty; //Convert row to ZipCodeTerritory type record.ChannelCode = _reader[0].ToString(); record.DrmTerrDesc = _reader[1].ToString(); record.IndDistrnId = _reader[2].ToString(); record.StateCode = _reader[3].ToString().Trim(); record.ZipCode = _reader[4].ToString().Trim(); record.LastUpdateId = _reader[7].ToString(); record.ErrorCodes = _reader[8].ToString(); record.Status = _reader[9].ToString(); record.LastUpdateDate = DateTime.Now; //Handle DateTime types separetly DateTime value = new DateTime(); if (DateTime.TryParse(_reader[5].ToString(), out value)) { record.EndDate = Convert.ToDateTime(_reader[5].ToString()); } else { _errorMsg += "Invalid End Date; "; } if (DateTime.TryParse(_reader[6].ToString(), out value)) { record.EffectiveDate = Convert.ToDateTime(_reader[6].ToString()); } else { _errorMsg += "Invalid Effective Date; "; } //Do not process if we're missing LastUpdateId if (string.IsNullOrEmpty(record.LastUpdateId)) { _errorMsg += "Missing last update Id; "; } //Make sure primary key is valid if (_reader[10] != DBNull.Value) { int id = 0; if (int.TryParse(_reader[10].ToString(), out id)) { record.Id = id; } else { _errorMsg += "Invalid Id; "; } } //Validate business rules if data is properly formatted if (string.IsNullOrWhiteSpace(_errorMsg)) { _errorMsg = validation.ValidateZipCode(record); } //Skip record if any errors found if (!string.IsNullOrWhiteSpace(_errorMsg)) { _issues++; //Convert to ZipCodeError type in case we have data/formatting errors _errors.Add(new ZipCodeError(_reader), _errorMsg); continue; } else if (flag) { //Separate updates to appropriate list SendToUpdates(record); } } catch (Exception ex) { _errors.Add(new ZipCodeError(_reader), "Job crashed reading this record, please review all columns."); _issues++; } }//End while //Updates occur in one of three methods below. If I step through the code, //and stop the program here, before I enter any of the methods, and then //make updates to the same records via our intranet site the changes //made on the site go through. No table locking has occured at this point. if (flag) { if (_insertList.Count > 0) { Updates.Insert(_insertList, _errors); } if (_updateList.Count > 0) { _updates = Updates.Update(_updateList, _errors); _issues += _updateList.Count - _updates; } if (_autotermList.Count > 0) { //_autotermed = Updates.Update(_autotermList, _errors); _autotermed = Updates.UpdateWithReporting(_autotermList, _errors); _issues += _autotermList.Count - _autotermed; } } transaction.Commit(); } 

SQL non fornisce realmente un modo per bloccare esclusivamente una tabella: è progettato per cercare di massimizzare l’uso simultaneo mantenendo ACID.

Potresti provare a utilizzare questi suggerimenti sulla tabella nelle tue query:

  • TABLOCK

    Specifica che il blocco acquisito viene applicato a livello di tabella. Il tipo di blocco acquisito dipende dall’istruzione eseguita. Ad esempio, un’istruzione SELECT può acquisire un blocco condiviso. Specificando TABLOCK, il blocco condiviso viene applicato all’intera tabella anziché a livello di riga o di pagina. Se viene specificato anche HOLDLOCK, il blocco della tabella viene mantenuto fino alla fine della transazione.

  • TABLOCKX

    Specifica che un blocco esclusivo viene preso sul tavolo.

  • UPDLOCK

    Specifica che i blocchi di aggiornamento devono essere presi e conservati fino al completamento della transazione. UPDLOCK accetta i blocchi di aggiornamento per le operazioni di lettura solo a livello di riga o di pagina. Se UPDLOCK è combinato con TABLOCK, o un blocco a livello di tabella viene preso per qualche altro motivo, verrà invece adottato un blocco esclusivo (X).

  • XLOCK

    Specifica che i blocchi esclusivi devono essere presi e conservati fino al completamento della transazione. Se specificato con ROWLOCK, PAGLOCK o TABLOCK, i blocchi esclusivi si applicano al livello appropriato di granularità.

  • HOLDLOCK / SERIALIZABLE

    Rende i blocchi condivisi più restrittivi trattenendoli fino al completamento di una transazione, invece di rilasciare il blocco condiviso non appena la tabella o la pagina di dati richieste non sono più necessari, indipendentemente dal fatto che la transazione sia stata completata o meno. La scansione viene eseguita con la stessa semantica di una transazione in esecuzione al livello di isolamento SERIALIZABLE. Per ulteriori informazioni sui livelli di isolamento, vedere SET TRANSACTION ISOLATION LEVEL (Transact-SQL).

In alternativa, puoi provare SET TRANSACTION ISOLATION LEVEL SERIALIZABLE:

  • Le istruzioni non possono leggere i dati che sono stati modificati ma non ancora impegnati da altre transazioni.

  • Nessuna altra transazione può modificare i dati letti dalla transazione corrente fino al completamento della transazione corrente.

  • Altre transazioni non possono inserire nuove righe con valori chiave che rientrerebbero nell’intervallo di chiavi lette da qualsiasi istruzione nella transazione corrente fino al completamento della transazione corrente.

I blocchi di intervallo sono collocati nell’intervallo di valori chiave che corrispondono alle condizioni di ricerca di ogni istruzione eseguita in una transazione. Ciò blocca altre transazioni dall’aggiornamento o dall’inserimento di qualsiasi riga che si qualificherebbe per una qualsiasi delle dichiarazioni eseguite dalla transazione corrente. Ciò significa che se una delle istruzioni di una transazione viene eseguita una seconda volta, leggeranno lo stesso insieme di righe. I blocchi dell’intervallo vengono mantenuti fino al completamento della transazione. Questo è il più restrittivo dei livelli di isolamento perché blocca interi intervalli di chiavi e mantiene i blocchi fino al completamento della transazione. Poiché la concorrenza è inferiore, utilizzare questa opzione solo quando necessario. Questa opzione ha lo stesso effetto dell’impostazione di HOLDLOCK su tutte le tabelle in tutte le istruzioni SELECT in una transazione.

Ma quasi certamente, l’escalation dei blocchi causerà il blocco e gli utenti saranno praticamente morti nell’acqua (nella mia esperienza).

Così…

Attendi finché non hai una finestra di manutenzione del programma. Imposta il database in modalità utente singolo, apporta le modifiche e riportalo online.

Prova questo: quando ottieni i record dalla tua tabella (nella funzione GetRecords ()?) Usa l’hint TABLOCKX:

  SELECT * FROM Table1 (TABLOCKX) 

Accoderà tutte le altre letture e gli aggiornamenti al di fuori della transazione fino a quando la transazione non sarà confermata o annullata.

È tutto sul livello di isolamento qui. Modifica il livello di isolamento della transazione in ReadCommited (non ha cercato il valore Enum in C # ma dovrebbe essere chiuso). Quando si esegue il primo aggiornamento / inserimento nella tabella, SQL inizierà il blocco e nessuno sarà in grado di leggere i dati che si stanno modificando / aggiungendo fino a quando non si esegue il commit o la transazione di rollback, a condizione che non eseguano letture sporche (utilizzando NoLock sul proprio SQL, o avere il livello di isolamento della connessione impostato su Leggi non trasmesso). Tuttavia, a seconda di come si stanno inserendo / aggiornando i dati, è ansible bloccare l’intera tabella per la durata della transazione, tuttavia ciò causerebbe errori di timeout in il client quando tentano di leggere da questa tabella mentre la transazione è aperta. Senza vedere l’SQL dietro gli aggiornamenti, anche se non posso dire se ciò accadrà qui.

Come qualcuno ha sottolineato, la transazione non sembra essere utilizzata dopo essere stata ritirata.

Dalle informazioni limitate che abbiamo sull’app / scopo, è difficile da dire, ma dal frammento di codice, mi sembra che non abbiamo bisogno di alcun blocco. Stiamo ottenendo alcuni dati dall’origine X (in questo caso _reader) e quindi inserendo / aggiornando nella destinazione Y.

Tutta la convalida avviene contro i dati di origine per assicurarsi che sia corretta, non sembra che stiamo prendendo una decisione o cura di ciò che è nella destinazione.

Se quanto sopra è vero, un approccio migliore sarebbe quello di caricare tutti questi dati in una tabella temporanea (può essere una tabella temporanea “#” reale o una tabella reale che distruggiamo in seguito, ma lo scopo è lo stesso), quindi in una singola istruzione sql, possiamo fare un inserimento / aggiornamento di massa dalla tabella temporanea alla nostra destinazione. Supponendo che lo schema db sia in una forma decente, 20 (o anche 30) migliaia di record dovrebbero accadere quasi istantaneamente senza bisogno di aspettare la finestra di manutenzione o bloccare gli utenti per lunghi periodi di tempo

Anche per rispondere rigorosamente alla domanda sull’utilizzo della transazione, di seguito è riportato un semplice esempio su come utilizzare correttamente una transazione, ci dovrebbero essere molti altri esempi e informazioni sul web

 SqlConnection conn = new SqlConnection(); SqlCommand cmd1 = new SqlCommand(); SqlTransaction tran = conn.BeginTransaction(); ... cmd1.Transaction = tran; ... tran.Commit();