Free Press

Lukitseminen on välttämätöntä SQL Serverin tapahtumien onnistuneelle käsittelylle, ja se on suunniteltu niin, että SQL Server voi toimia saumattomasti monen käyttäjän ympäristössä. Lukitus on tapa, jolla SQL Server hallitsee transaktioiden samanaikaisuutta. Pohjimmiltaan lukitukset ovat muistissa olevia rakenteita, joilla on omistajat, tyypit ja sen resurssin hash-tunnus, jota sen pitäisi suojata. Muistin sisäisenä rakenteena lukko on kooltaan 96 tavua.

SQL Serverin lukituksen ymmärtämiseksi paremmin on tärkeää ymmärtää, että lukitus on suunniteltu varmistamaan tietokannan tietojen eheys, sillä se pakottaa jokaisen SQL Serverin tapahtuman läpäisemään ACID-testin.

ACID-testi koostuu neljästä vaatimuksesta, jotka jokaisen transaktion on läpäistävä onnistuneesti:

• Atomisuus – edellyttää, että transaktion, joka koskee kahta tai useampaa erillistä tiedon osaa, on sitouduttava kaikkiin osiin tai ei mihinkään
• Johdonmukaisuus – edellyttää, että transaktion on luotava kelvollinen tila uusille tiedoille tai sen on palautettava kaikki tiedot tilaan, joka oli olemassa ennen transaktion suorittamista
• Eristäminen – edellyttää, että transaktio, joka on vielä käynnissä ja joka ei ole vielä sitonut kaikkia tietoja, on pysyttävä eristettynä kaikista muista transaktioista
• Kestävyys – edellyttää, että sitoutuneet tiedot on tallennettava menetelmällä, joka säilyttää kaikki tiedot oikeassa tilassa ja käyttäjän käytettävissä myös vikatilanteessa

SQL Server -lukitus on olennainen osa eristysvaatimusta, ja sen tehtävänä on lukita transaktion kohteena olevat objektit. Kun objektit on lukittu, SQL Server estää muita transaktioita tekemästä muutoksia niihin objekteihin tallennettuihin tietoihin, joihin määrätty lukitus vaikuttaa. Kun lukitus vapautetaan tekemällä muutokset tai palauttamalla muutokset alkutilaan, muut transaktiot voivat tehdä tarvittavat tietomuutokset.

SQL Serverin kielelle käännettynä tämä tarkoittaa sitä, että kun transaktio määrää lukon objektille, kaikki muut transaktiot, jotka tarvitsevat pääsyn kyseiseen objektiin, joutuvat odottamaan, kunnes lukko vapautetaan, ja tämä odotus rekisteröidään riittävällä odotustyypillä

SQL Serverin lukkoja voidaan määritellä lukkotilojen ja lukkorakeisuuden avulla

Lukkotilat

Lukkotiloissa huomioidaan erilaiset lukkotyypit, joita voidaan soveltaa resurssiin, joka halutaan lukita:

• Yksinoikeus (X)
• Jaettu (S)
• Päivitys (U)
• Tarkoitus (I)
• Skeema (Sch)
• Joukkopäivitys (BU)

Yksinoikeuslukko (X) – Tämä lukitustyyppi, kun se on asetettu, varmistaa, että sivu tai rivi on varattu yksinomaan yksinoikeuslukon asettaneelle tapahtumalle niin kauan kuin tapahtuma pitää lukon hallussaan.

Yksinoikeuslukon asettaa transaktio, kun se haluaa muuttaa sivun tai rivin tietoja, eli DML-lauseiden DELETE, INSERT ja UPDATE tapauksessa. Yksinoikeuslukko voidaan asettaa sivulle tai riville vain, jos kohteeseen ei ole jo asetettu muuta jaettua tai yksinoikeuslukkoa. Tämä tarkoittaa käytännössä sitä, että sivulle tai riville voidaan asettaa vain yksi yksinoikeuslukko, ja kun se on asetettu, lukittuihin resursseihin ei voida asettaa muita lukkoja

Jakolukko (S) – tämä lukitustyyppi, kun se on asetettu, varaa sivun tai rivin käytettäväksi vain lukemista varten, mikä tarkoittaa sitä, että mikään muu transaktio ei voi muuttaa lukittua tietuetta niin kauan kuin lukitus on käytössä. Useat transaktiot voivat kuitenkin käyttää jaettua lukitusta samaan aikaan samalle sivulle tai riville, jolloin useat transaktiot voivat jakaa tiedonlukukyvyn, koska lukuprosessi ei vaikuta millään tavalla varsinaiseen sivun tai rivin tietoon. Lisäksi jaettu lukko sallii kirjoitusoperaatiot, mutta DDL-muutokset eivät ole sallittuja

Päivityslukko (U) – tämä lukko on samanlainen kuin yksinoikeuslukko, mutta se on suunniteltu tietyllä tavalla joustavammaksi. Päivityslukko voidaan asettaa tietueelle, jolla on jo jaettu lukko. Tällöin päivityslukko asettaa toisen jaetun lukon kohderiville. Kun päivityslukkoa hallussaan pitävä tapahtuma on valmis muuttamaan tietoja, päivityslukko (U) muutetaan yksinoikeuslukoksi (X). On tärkeää ymmärtää, että päivityslukko on epäsymmetrinen jaettuihin lukkoihin nähden. Vaikka päivityslukko voidaan määrätä tietueelle, jolla on jaettu lukko, jaettua lukkoa ei voida määrätä tietueelle, jolla on jo päivityslukko

Aikomuslukot (I) – tämä lukko on keino, jolla transaktio ilmoittaa toiselle transaktiolle aikomuksestaan hankkia lukko. Tällaisen lukon tarkoituksena on varmistaa, että tietojen muokkaus suoritetaan asianmukaisesti estämällä toista transaktiota hankkimasta lukitusta hierarkiassa seuraavaan objektiin. Käytännössä, kun transaktio haluaa hankkia lukon riville, se hankkii aikomuslukon taulukkoon, joka on hierarkiassa ylempänä oleva objekti. Hankkimalla aikomuslukon transaktio ei salli muiden transaktioiden hankkia yksinoikeuslukkoa kyseiseen taulukkoon (muutoin jonkin toisen transaktion määräämä yksinoikeuslukko kumoaisi rivin lukituksen).

Tämä on suorituskyvyn kannalta tärkeä lukitustyyppi, sillä SQL Serverin tietokantamoottori tarkastaa intent-lukot vain taulukkotasolla tarkistaakseen, voiko transaktio hankkia lukon turvallisesti kyseiseen taulukkoon, ja siksi intent-lukolla ei tarvitse tarkastaa jokaista taulukon rivi-/sivulukkoa varmistaakseen, että transaktio voi hankkia lukon koko taulukkoon

Tavallisia intent-lukotyyppejä (intent locks) ja niin sanottuja muuntolukotyyppejä (conversion locks) on kolme:

Säännölliset aikomuslukot:

Yksinoikeuslukko (IX) – kun yksinoikeuslukko (IX) hankitaan, se osoittaa SQL-palvelimelle, että transaktiolla on aikomus muuttaa joitakin alemman hierarkian resursseja hankkimalla yksinoikeuslukot (X) yksittäin kyseisiin alemman hierarkian resursseihin

Jakolukko (IS) – kun jaettu lukko (IS) hankitaan, se osoittaa SQL-palvelimelle, että transaktio on aikomus lukea joitakin alemman hierarkian resursseja hankkimalla jaetut lukot (S) yksitellen kyseisiin hierarkiassa alempana oleviin resursseihin

Intent update (IU) (aikomuspäivitys) – kun aikomus jaettu lukko (IS) hankitaan, se osoittaa SQL Serverille, että transaktiolla on aikomus lukea joitakin alemman hierarkian resursseja hankkimalla jaetut lukot (S) yksitellen kyseisiin hierarkiassa alempana oleviin resursseihin. Aikomuspäivityslukko (IU) voidaan hankkia vain sivutasolla, ja heti kun päivitysoperaatio tapahtuu, se muuntuu aikomusyksinoikeuslukoksi (IX)

Muunnoslukot:

Jakolukko aikomusyksinoikeuslukolla (SIX) – kun tämä lukko on hankittu, se ilmaisee, että transaktio aikoo lukea kaikkia alemman hierarkian resursseja ja näin ollen hankkia jaetun lukon kaikkiin hierarkiassa alempana oleviin resursseihin ja vuorostaan muokata osaa niistä, mutta ei kaikkia. Näin tehdessään se hankkii tarkoituksellisen yksinoikeuslukon (IX) niihin alemman hierarkian resursseihin, joita on tarkoitus muuttaa. Käytännössä tämä tarkoittaa sitä, että kun transaktio hankkii SIX-lukon taulukkoon, se hankkii yksinoikeuslukon (IX) muutetuille sivuille ja yksinoikeuslukon (X) muutetuille riveille.

Taulukkoon voidaan hankkia kerrallaan vain yksi jaettu yksinoikeuslukko (SIX), ja se estää muita transaktioita tekemästä päivityksiä, mutta se ei estä muita transaktioita lukemasta alemman hierarkian resursseja ne voivat hankkia taulukkoon jaetun lukon (IS)

Jaettu päivityslukko (SIU) – tämä lukko on hiukan spesifisempi lukko, sillä se on jaetun lukon (S) ja aikomuspäivitystoiminnon lukon (IU) yhdistelmä. Tyypillinen esimerkki tästä lukosta on, kun transaktio käyttää kyselyä, joka on suoritettu PAGELOCK-vihjeellä ja kyselyllä, ja sitten päivityskyselyä. Kun transaktio on hankkinut SIU-lukon taulukkoon, PAGELOCK-vihjeellä varustettu kysely hankkii jaetun (S) lukon, kun taas päivityskysely hankkii intent update (IU) -lukon

Update with intent exclusive (UIX) – kun päivityslukko (U) ja intent exclusive (IX) -lukot hankitaan taulukon alemman hierarkian resursseihin samanaikaisesti, sen seurauksena taulukkotasolle hankitaan päivitys aikomuksella yksinoikeuslukko

Skeemalukot (Sch) – SQL Serverin tietokantamoottori tunnistaa kahdenlaisia skeemalukotyyppejä: Skeeman muutoslukko (Sch-M) ja skeeman vakauslukko (Sch-S)

• Skeeman muutoslukko (Sch-M) hankitaan, kun DDL-lause suoritetaan, ja se estää pääsyn lukittuihin objektitietoihin, kun objektin rakennetta muutetaan. SQL Server sallii yhden Schema Modification Lock (Sch-M) -lukon mille tahansa lukitulle objektille. Taulukon muuttaminen edellyttää, että transaktio odottaa Sch-M-lukon saamista kohdeobjektiin. Kun se on hankkinut skeeman muokkauslukon (Sch-M), transaktio voi muokata kohdetta, ja kun muokkaus on valmis ja lukko vapautetaan. Tyypillinen esimerkki Sch-M-lukosta on indeksin uudelleenrakennus, koska indeksin uudelleenrakennus on taulukon muutosprosessi. Kun indeksin uudelleenrakentamisen tunnus on annettu, kyseiseen tauluun hankitaan skeeman muokkauslukko (Sch-M), joka vapautetaan vasta indeksin uudelleenrakentamisprosessin päätyttyä (kun sitä käytetään ONLINE-option kanssa, indeksin uudelleenrakentaminen hankkii Sch-M-lukon pian prosessin päätyttyä)
• Skeeman stabiilisuuslukko (Sch-S) hankitaan, kun skeemasta riippuvaista kyselyä käännetään ja suoritetaan ja suoritussuunnitelma luodaan. Tämä lukitus ei estä muita transaktioita käyttämästä objektin tietoja, ja se on yhteensopiva kaikkien lukitustilojen paitsi skeeman muutoslukon (Sch-M) kanssa. Pohjimmiltaan Schema stability -lukot hankitaan jokaisella DML- ja select-kyselyllä taulukon rakenteen eheyden varmistamiseksi (varmistetaan, että taulukko ei muutu kyselyiden suorittamisen aikana).

Bulk-päivityslukot (BU) – tämä lukko on suunniteltu käytettäväksi bulk-tuontioperaatioissa, kun se annetaan TABLOCK-argumentilla/vihjeellä. Kun bulk-päivityslukko on hankittu, muut prosessit eivät voi käyttää taulukkoa bulk-latauksen suorituksen aikana. Bulk-päivityslukko ei kuitenkaan estä toisen bulk-latauksen rinnakkaista käsittelyä. Muista kuitenkin, että TABLOCK-lukon käyttäminen klusteroituun indeksitauluun ei salli rinnakkaista irtotuontia. Lisätietoja tästä on kohdassa Guidelines for Optimizing Bulk Import

Lukitushierarkia

SQL Server on ottanut käyttöön lukitushierarkian, jota sovelletaan, kun tietoja luetaan tai muutetaan. Lukitushierarkia alkaa tietokannasta ylimmällä hierarkiatasolla ja laskee taulukon ja sivun kautta alimmalla tasolla olevaan riviin

Pohjimmiltaan tietokantatasolla on aina jaettu lukitus, joka asetetaan aina, kun tietokantaan liitetään tapahtuma. Tietokantatason jaettu lukitus asetetaan estämään tietokannan pudottaminen tai tietokannan varmuuskopion palauttaminen käytössä olevaan tietokantaan. Kun esimerkiksi SELECT-lauseella luetaan joitakin tietoja, tietokantatasolle asetetaan jaettu lukko (S), taulu- ja sivutasolle asetetaan tarkoituksenmukainen jaettu lukko (IS) ja itse riville asetetaan jaettu lukko (S)

Jos kyseessä on DML-lause (esim. insert (lisää), update (päivitä), delete (poista)) tietokantatasolle asetetaan jaettu lukko (S), taulu- ja sivutasolle intent exclusive lock (IX) tai intent update lock (IU), ja riville yksinoikeus- tai päivityslukko (X tai U)

Lukitukset hankitaan aina ylhäältä alaspäin, sillä tällä tavoin SQL Server estää niin sanotun Race-ehdon syntymisen.

Nyt kun lukitustilat ja lukitushierarkia on selitetty, käsitellään tarkemmin lukitustiloja ja sitä, miten ne muuntuvat lukitushierarkiaksi.

Kaikkia lukitustiloja ei voida soveltaa kaikilla tasoilla.

Rivitasolla voidaan soveltaa seuraavia kolmea lukitustilaa:

• Yksinoikeus (X)
• Jaettu (S)
• Päivitystila (U)

Ymmärtääksesi näiden lukitustilojen yhteensopivuuden, katso seuraava taulukko:

✓ – Yhteensopiva ✗ – Yhteensopimaton

Taulukkotasolla, on olemassa viisi erilaista lukitustyyppiä:

• Eksklusiivinen (X)
• Jaettu (S)
• Tarkoitus eksklusiivinen (IX)
• Tarkoitus jaettu (IS)
• Jaettu tarkoituksen kanssa eksklusiivinen (SIX)

Näiden tilojen yhteensopivuus käy ilmi alla olevasta taulukosta

✗

✗

✓ – Yhteensopiva ✗ – Yhteensopimaton

Skeemalukko (Sch) on myös taulukkotason lukko, mutta se ei ole dataan liittyvä lukko

Jotta ymmärtäisit paremmin näiden lukitustyyppien yhteensopivuuden, katso tätä taulukkoa:

Lock escalation

Estääkseen tilanteen, jossa lukitus käyttää liikaa resursseja, SQL Server on ottanut käyttöön lock escalation -ominaisuuden.

Ilman eskalointia lukitukset saattavat vaatia huomattavan määrän muistiresursseja. Otetaan esimerkki, jossa lukitus pitäisi asettaa 30 000 tietoriville, joissa jokainen rivi on 500 tavun kokoinen, poisto-operaation suorittamiseksi. Ilman eskalointia tietokantaan asetetaan jaettu lukko (S), taulukkoon 1 tarkoituksellinen yksinoikeuslukko (IX), sivuihin 1 875 tarkoituksellista yksinoikeuslukkoa (IX) (8 kt:n sivulle mahtuu 16 500 tavun riviä, eli 1 875 sivua, joihin mahtuu 30 000 riviä) ja itse riveihin 30 000 yksinoikeuslukkoa (X). Koska kunkin lukon koko on 96 tavua, 31 877 lukitusta vie noin 3 megatavua muistia yhtä poisto-operaatiota varten. Suuren määrän operaatioiden suorittaminen rinnakkain saattaa vaatia huomattavia resursseja pelkästään sen varmistamiseksi, että lukituksenhallinta voi suorittaa operaation sujuvasti

Tällaisen tilanteen estämiseksi SQL Server käyttää lukituksen eskalointia. Tämä tarkoittaa sitä, että tilanteessa, jossa yhdelle tasolle hankitaan yli 5 000 lukitusta, SQL Server eskaloi nämä lukot yhdeksi taulukkotason lukoksi. Oletusarvoisesti SQL Server eskaloi aina suoraan taulukkotasolle, mikä tarkoittaa, että eskalointia sivutasolle ei tapahdu koskaan. Lukuisien rivi- ja sivulukkojen hankkimisen sijaan SQL Server eskaloituu taulukkotason yksinoikeuslukkoon (X)

Vaikka tämä vähentää resurssien tarvetta, taulukon yksinoikeuslukot (X) merkitsevät sitä, että yksikään muu transaktio ei pääse käyttämään lukittua taulukkoa ja kaikki kyselyt, jotka yrittävät käyttää kyseistä taulukkoa, estyvät. Näin ollen tämä vähentää järjestelmän yleiskustannuksia, mutta lisää samanaikaisuusriitojen todennäköisyyttä

Jotta eskaloitumista voitaisiin hallita, SQL Server 2008 R2:sta alkaen, LOCK_EXCALATION-vaihtoehto otetaan käyttöön osana ALTER TABLE -lausetta

Kukin näistä vaihtoehdoista on määritetty niin, että se sallii tietynlaisen hallinnan lukituksen eskalaatioprosessiin:

Table – Tämä on oletusvaihtoehto kaikille äskettäin luoduille taulukoille, sillä oletusarvoisesti SQL Server suorittaa aina lukituksen eskaloinnin taulukkotasolle, mikä koskee myös osioituja taulukoita

Auto – Tämä vaihtoehto mahdollistaa lukituksen eskaloinnin osiotasolle, kun taulukko on osioitu. Kun yhteen osioon on hankittu 5000 lukitusta, lock escalation hankkii yksinoikeuslukon (X) kyseiseen osioon, kun taas taulukko hankkii tarkoituksellisen yksinoikeuslukon (IX). Jos taulukkoa ei ole osioitu, lock escalation hankkii lukon taulukkotasolla (sama kuin Table-vaihtoehto).

Vaikka tämä näyttää erittäin hyödylliseltä vaihtoehdolta, sitä on käytettävä hyvin varovasti, koska se voi helposti aiheuttaa umpikujan. Tilanteessa, jossa meillä on kaksi transaktiota kahdessa osiossa, joissa yksinoikeuslukko (X) on hankittu, ja transaktiot yrittävät käyttää päivämäärää toisen transaktion käyttämästä osiosta, syntyy umpikuja

Siis, on erittäin tärkeää valvoa huolellisesti tietojen käyttökuviota, jos tämä vaihtoehto on käytössä, mikä ei ole helppoa, ja siksi tämä vaihtoehto ei ole SQL Serverin oletusasetuksissa

Disable – Tämä vaihtoehto poistaa lukituksen eskaloinnin kokonaan käytöstä taulukon osalta. Myös tätä vaihtoehtoa on käytettävä varovasti, jotta SQL Serverin lukituksenhallinta ei joutuisi käyttämään liikaa muistia

Kuten voidaan nähdä, lukituksen eskalointi voi olla haaste DBA:lle. Jos sovellussuunnittelu edellyttää yli 5000 rivin poistamista tai päivittämistä kerralla, ratkaisu lukkojen eskaloitumisen ja siitä aiheutuvien vaikutusten välttämiseksi on jakaa yksi transaktio kahdeksi tai useammaksi transaktioksi, joissa kussakin käsitellään alle 5000 riviä, sillä tällä tavoin lukkojen eskaloituminen voidaan välttää

Tiedon saaminen SQL Serverin aktiivisista lukoista

SQL Server tarjoaa Dynamics Management View (DMV) sys.dm_tran_locks, joka palauttaa tietoja tällä hetkellä käytössä olevista lukkojenhallintaresursseista, mikä tarkoittaa, että se näyttää kaikki transaktioiden hankkimat ”elävät” lukot. Lisätietoja tästä DMV:stä on artikkelissa sys.dm_tran_locks (Transact-SQL).

Tärkeimmät lukon tunnistamiseen käytettävät sarakkeet ovat resource_type, request_mode ja resource_description. Tarvittaessa voidaan vianmäärityksen aikana ottaa mukaan lisää sarakkeita lisäresurssin informaatiotietoina

Tässä on esimerkki kyselystä

Tämän kyselyn where-lauseketta käytetään suodattimena karsittavaan resurssin_tyyppiin. tuloksista yleensä ne jaetut lukot, jotka on hankittu tietokantaan, koska ne ovat aina olemassa tietokantatasolla

Lyhyt selitys tässä esitetyistä kolmesta sarakkeesta:

resource_type – Näyttää tietokantaresurssin, johon lukot on hankittu. Sarakkeessa voi näkyä jokin seuraavista arvoista: ALLOCATION_UNIT, APPLICATION, DATABASE, EXTENT, FILE, HOBT, METADATA, OBJECT, PAGE, KEY, RID

request_mode – näyttää lukitustilan, joka hankitaan resurssille

resource_description – näyttää resurssin lyhyen kuvauksen, ja sitä ei täytetä kaikille lukitustiloille. Useimmiten sarake sisältää rivin, sivun, objektin tai tiedoston id:n, Erikoistunut SQL Serverin auditointiin, vaatimustenmukaisuuden ja suorituskyvyn valvontaan.
Sotilasilmailun harrastaja ja kovan luokan mittakaavan lentokonemallintaja. Extreme-urheilun harrastaja; laskuvarjohyppääjä ja benjihyppykouluttaja. Aikoinaan tosissaan, nyt vain vapaa-ajan valokuvaaja
Katso kaikki viestit käyttäjältä Nikola Dimitrijevic