A konferenci\u00e1ra k\u00e9sz\u00fclve pr\u00f3b\u00e1ltam \u00f6sszerakni p\u00e9ld\u00e1kat, hogyan lehet megval\u00f3s\u00edtani upsert elj\u00e1r\u00e1sokat SQL Server 2000, 2005 \u00e9s 2008 alapon. Ezekre nem volt id\u0151 a konfon, ez\u00e9rt most bemutatom \u0151ket.<\/p>\n
A probl\u00e9ma egyszer\u0171: j\u00f6n fel a szerverre egy sornyi inf\u00f3, azt kell besz\u00farni egy t\u00e1bl\u00e1ba, ha valamilyen szempont szerint nem l\u00e9tezik a sor, vagy ha l\u00e9tezik, friss\u00edteni. Mindezt a konkurens hozz\u00e1f\u00e9r\u00e9sek ellen korrekt\u00fcl v\u00e9dve kell megval\u00f3s\u00edtani.<\/p>\n
Az els\u0151 megold\u00e1s a legk\u00e9zenfekv\u0151bb logik\u00e1val m\u0171k\u00f6dik, if exists-szel megn\u00e9zz\u00fck, l\u00e9tezik-e a sor, ha nem insert, ha igen, update. Persze mindezt tranzakci\u00f3ban v\u00e9grehajtva. Ez azonban kev\u00e9s, mert alap izol\u00e1ci\u00f3s szinten a select elengedi a z\u00e1rol\u00e1sokat amint v\u00e9gzett, \u00edgy lehet, hogy azt l\u00e1tjuk l\u00e9tezik a sor, de az update m\u00e1r nem tal\u00e1lja azt ott, vagy ford\u00edtva, azt l\u00e1tjuk nem l\u00e9tezik, de az insert idej\u00e9re m\u00e1r beker\u00fcl, \u00edgy constraint violation-t kapunk (ha a megk\u00fcl\u00f6nb\u00f6ztet\u0151 oszlop egyedi).<\/p>\n
\r\nif OBJECT_ID('dbo.Client') is not null drop table dbo.Client\r\ngo\r\n\r\ncreate table dbo.Client\r\n(\r\n id int not null primary key,\r\n name nvarchar(100)\r\n)\r\ngo\r\n\r\n--1. solution\r\nif OBJECT_ID('dbo.UpsertClient1') is not null drop proc dbo.UpsertClient1\r\ngo\r\n\r\ncreate proc dbo.UpsertClient1\r\n @id int,\r\n @name nvarchar(100)\r\nas\r\nset xact_abort on\r\nbegin transaction\r\n\r\nif exists(select * from dbo.Client with (updlock, serializable) where id = @id)\r\n update dbo.Client set name = @name where id = @id\r\nelse\r\n insert dbo.Client (id, name) values (@id, @name)\r\n\r\ncommit\r\ngo\r\n<\/pre>\nA serializable hint arra val\u00f3, hogy a kulcsot (id) z\u00e1rolja a szerver, \u00edgy m\u00e9g akkor is v\u00e9dve van a kulcshoz tartoz\u00f3 sor, ha a sor nem is l\u00e9tezik, m\u00e1sr\u00e9szt a tranzakci\u00f3 v\u00e9g\u00e9ig fenntartja a select lockj\u00e1t.
\nAz updlock az\u00e9rt kell, mert ha k\u00e9t select egyszerre nekiszalad ugyanannak a sornak, mindkett\u0151 shared lock-kal lez\u00e1rolva a sort, akkor a k\u00f6vetkez\u0151 insert vagy update \u00e1ltal ig\u00e9nyelt exclusive lock nem adhat\u00f3 ki, mert a m\u00e1sik shared-et tart rajta. Ez deadlock, mert mindkett\u0151 a m\u00e1sik miatt nem tud tov\u00e1bbmenni. Am\u00fagy ezt conversation deadlocknak h\u00edvj\u00e1k, mert a lock felkonvert\u00e1l\u00e1s (shared -> exlcusive) miatt j\u00f6n el\u0151. Az alapprobl\u00e9ma ugye az, hogy a szerver kiad k\u00e9t shared lockot is a sorra (pontosabban kulcsra). A shared lockok kompatibilisek<\/a>, azaz egyszerre ak\u00e1rh\u00e1ny folyamatnak kiadhat\u00f3k, hisz mi \u00e9rtelme korl\u00e1tozni a p\u00e1rhuzamos olvas\u00f3 sz\u00e1lak sz\u00e1m\u00e1t?
\nEbben az esetben azonban az olvas\u00e1s ut\u00e1n \u00edr\u00e1s j\u00f6n, ami deadlockot okoz. A megold\u00e1s, hogy az updlock hinttel nem shared, hanem update lockot k\u00e9r\u00fcnk a kulcsra. K\u00e9t update lock ___NEM___ kompatibilis. Azaz, ha az els\u0151 folyamat megszerezte az update lockot a kulcsra, a m\u00e1sik a tranzakci\u00f3 v\u00e9g\u00e9ig (a serializable miatt) v\u00e1rni fog, hogy if exists-elhessen. \u00cdgy az els\u0151 update-el vagy insert\u00e1l ahogy kij\u00f6n neki, a m\u00e1sodik pedig az el\u0151z\u0151 v\u00e9geredm\u00e9nye alapj\u00e1n d\u00f6nthet az if existsben.<\/p>\nEz az els\u0151 p\u00e9lda, lesz m\u00e9g 4 m\u00e1sik megk\u00f6zel\u00edt\u00e9st alkalmaz\u00f3, ezeket a k\u00f6vetkez\u0151 napokban publik\u00e1lom. L\u00e1ssuk a sebess\u00e9g tesztel\u00e9st.
\nA kiindul\u00f3 t\u00e1bl\u00e1ba 100e sort raktam, 100e \u00e9s 200e k\u00f6z\u00f6tti id-kkel. Ezt a t\u00e1bl\u00e1t minden teszt el\u0151tt \u00fajra\u00e9p\u00edtem.<\/p>\n
\r\ntruncate table dbo.Client\r\ninsert dbo.Client with(tablock) (id, name)\r\nselect top 100000 SalesOrderDetailID + 100000, rowguid \r\nfrom AdventureWorks.Sales.SalesOrderDetail order by SalesOrderDetailID\r\n<\/pre>\nA tesztek eredm\u00e9ny\u00e9t egy t\u00e1bl\u00e1ba gy\u0171jt\u00f6m:<\/p>\n
\r\nif OBJECT_ID('MeasureResults') is null\r\n create table MeasureResults (procid int, duration int)\r\n<\/pre>\nMaga a teszt:<\/p>\n
set @d = sysdatetime()
\ndeclare @i1 int = 0
\nwhile (@i1 < 100000)\nbegin\n declare @j1 int = rand() * 200000\n exec UpsertClient1 @j1, 'apple'\n set @i1 += 1\nend\n\ninsert MeasureResults values(1, datediff(s, @d, sysdatetime()))\n[\/source]\n\nA 100e upsert \u00e1tlagban 40e besz\u00far\u00e1st \u00e9s 160e update-et eredm\u00e9nyez. Azaz egy olyan mint\u00e1nak felel meg a teszt\u00fcnk, amiben 80% update \u00e9s 20% insert van. Hogy ez realisztikus-e az \u00fczleti k\u00f6vetelm\u00e9nyek d\u00f6ntik el, nem lehet tudni el\u0151re, tesznek ez j\u00f3.\n\nA teszteket 10x ism\u00e9teltem meg minden elj\u00e1r\u00e1sra. Az eredm\u00e9nyek egyszer\u0171en \u00e1llanak el\u0151:\n[source='sql']\nselect procid, AVG(duration) dur from MeasureResults \ngroup by procid\n[\/source]\n\nTempdb-n futtatva a k\u00f6vetkez\u0151 eredm\u00e9nyeket kaptam (mint eml\u00edtettem, a marad\u00e9k 4 elj\u00e1r\u00e1st a napokban publik\u00e1lom):\n\n[source='c']\nprocid dur\n----------- -----------\n1 6\n2 5\n3 6\n4 7\n5 5\n[\/source]\n\nSima user adatb\u00e1zisban:\n[source='c']\nprocid dur\n----------- -----------\n1 152\n2 161\n3 164\n4 64\n5 186\n[\/source]\n\nMit tud a 4-es megold\u00e1s?\nMeglep\u0151, mennyivel gyorsabb a tempdb, mi? Van valakinek \u00f6tlete, mi\u00e9rt? Nekem egy van, a tempdb-ben a log\u00edr\u00e1s aszinkron, szemben a sima db-kell. Tapasztalati \u00faton azt l\u00e1ttam, hogy a tempdb eset\u00e9n alig \u00edr a viny\u00f3, sima adatb\u00e1zisn\u00e1l meg majd megveszik. Esetleg m\u00e1s tipp?\n<\/p>\n","protected":false},"excerpt":{"rendered":"
A konferenci\u00e1ra k\u00e9sz\u00fclve pr\u00f3b\u00e1ltam \u00f6sszerakni p\u00e9ld\u00e1kat, hogyan lehet megval\u00f3s\u00edtani upsert elj\u00e1r\u00e1sokat SQL Server 2000, 2005 \u00e9s 2008 alapon. Ezekre nem volt id\u0151…<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[6,4,30,21,58],"tags":[],"class_list":["post-512","post","type-post","status-publish","format-standard","hentry","category-adatbazisok","category-szakmai-elet","category-sql-server","category-sql-server-2005","category-sql-server-2008"],"_links":{"self":[{"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/posts\/512","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/comments?post=512"}],"version-history":[{"count":0,"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/posts\/512\/revisions"}],"wp:attachment":[{"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/media?parent=512"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/categories?post=512"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/tags?post=512"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}