A konferenciára készülve próbáltam összerakni példákat, hogyan lehet megvalósítani upsert eljárásokat SQL Server 2000, 2005 és 2008 alapon. Ezekre nem volt idő a konfon, ezért most bemutatom őket.

A probléma egyszerű: jön fel a szerverre egy sornyi infó, azt kell beszúrni egy táblába, ha valamilyen szempont szerint nem létezik a sor, vagy ha létezik, frissíteni. Mindezt a konkurens hozzáférések ellen korrektül védve kell megvalósítani.

Az első megoldás a legkézenfekvőbb logikával működik, if exists-szel megnézzük, létezik-e a sor, ha nem insert, ha igen, update. Persze mindezt tranzakcióban végrehajtva. Ez azonban kevés, mert alap izolációs szinten a select elengedi a zárolásokat amint végzett, így lehet, hogy azt látjuk létezik a sor, de az update már nem találja azt ott, vagy fordítva, azt látjuk nem létezik, de az insert idejére már bekerül, így constraint violation-t kapunk (ha a megkülönböztető oszlop egyedi).

if OBJECT_ID('dbo.Client') is not null drop table dbo.Client
go

create table dbo.Client
(
  id int not null primary key,
  name nvarchar(100)
)
go

--1. solution
if OBJECT_ID('dbo.UpsertClient1') is not null drop proc dbo.UpsertClient1
go

create proc dbo.UpsertClient1
  @id int,
  @name nvarchar(100)
as
set xact_abort on
begin transaction

if exists(select * from dbo.Client with (updlock, serializable) where id = @id)
  update dbo.Client set name = @name where id = @id
else
  insert dbo.Client (id, name) values (@id, @name)

commit
go

A serializable hint arra való, hogy a kulcsot (id) zárolja a szerver, így még akkor is védve van a kulcshoz tartozó sor, ha a sor nem is létezik, másrészt a tranzakció végéig fenntartja a select lockját.
Az updlock azért kell, mert ha két select egyszerre nekiszalad ugyanannak a sornak, mindkettő shared lock-kal lezárolva a sort, akkor a következő insert vagy update által igényelt exclusive lock nem adható ki, mert a másik shared-et tart rajta. Ez deadlock, mert mindkettő a másik miatt nem tud továbbmenni. Amúgy ezt conversation deadlocknak hívják, mert a lock felkonvertálás (shared -> exlcusive) miatt jön elő. Az alapprobléma ugye az, hogy a szerver kiad két shared lockot is a sorra (pontosabban kulcsra). A shared lockok kompatibilisek, azaz egyszerre akárhány folyamatnak kiadhatók, hisz mi értelme korlátozni a párhuzamos olvasó szálak számát?
Ebben az esetben azonban az olvasás után írás jön, ami deadlockot okoz. A megoldás, hogy az updlock hinttel nem shared, hanem update lockot kérünk a kulcsra. Két update lock ___NEM___ kompatibilis. Azaz, ha az első folyamat megszerezte az update lockot a kulcsra, a másik a tranzakció végéig (a serializable miatt) várni fog, hogy if exists-elhessen. Így az első update-el vagy insertál ahogy kijön neki, a második pedig az előző végeredménye alapján dönthet az if existsben.

Ez az első példa, lesz még 4 másik megközelítést alkalmazó, ezeket a következő napokban publikálom. Lássuk a sebesség tesztelést.
A kiinduló táblába 100e sort raktam, 100e és 200e közötti id-kkel. Ezt a táblát minden teszt előtt újraépítem.

truncate table dbo.Client
insert dbo.Client with(tablock) (id, name)
select top 100000 SalesOrderDetailID + 100000, rowguid 
from AdventureWorks.Sales.SalesOrderDetail order by SalesOrderDetailID

A tesztek eredményét egy táblába gyűjtöm:

if OBJECT_ID('MeasureResults') is null
    create table MeasureResults (procid int, duration int)

Maga a teszt:

set @d = sysdatetime()
declare @i1 int = 0
while (@i1 < 100000) begin declare @j1 int = rand() * 200000 exec UpsertClient1 @j1, 'apple' set @i1 += 1 end insert MeasureResults values(1, datediff(s, @d, sysdatetime())) [/source] A 100e upsert átlagban 40e beszúrást és 160e update-et eredményez. Azaz egy olyan mintának felel meg a tesztünk, amiben 80% update és 20% insert van. Hogy ez realisztikus-e az üzleti követelmények döntik el, nem lehet tudni előre, tesznek ez jó. A teszteket 10x ismételtem meg minden eljárásra. Az eredmények egyszerűen állanak elő: [source='sql'] select procid, AVG(duration) dur from MeasureResults group by procid [/source] Tempdb-n futtatva a következő eredményeket kaptam (mint említettem, a maradék 4 eljárást a napokban publikálom): [source='c'] procid dur ----------- ----------- 1 6 2 5 3 6 4 7 5 5 [/source] Sima user adatbázisban: [source='c'] procid dur ----------- ----------- 1 152 2 161 3 164 4 64 5 186 [/source] Mit tud a 4-es megoldás? Meglepő, mennyivel gyorsabb a tempdb, mi? Van valakinek ötlete, miért? Nekem egy van, a tempdb-ben a logírás aszinkron, szemben a sima db-kell. Tapasztalati úton azt láttam, hogy a tempdb esetén alig ír a vinyó, sima adatbázisnál meg majd megveszik. Esetleg más tipp?

Could you hire me? Contact me if you like what I’ve done in this article and think I can create value for your company with my skills.