{"id":513,"date":"2008-05-15T12:12:58","date_gmt":"2008-05-15T11:12:58","guid":{"rendered":"http:\/\/soci.hu\/blog\/?p=513"},"modified":"2008-05-15T12:12:58","modified_gmt":"2008-05-15T11:12:58","slug":"sql-server-upsert-variaciok-2","status":"publish","type":"post","link":"https:\/\/soci.hu\/blog\/index.php\/2008\/05\/15\/sql-server-upsert-variaciok-2\/","title":{"rendered":"SQL Server UPSERT vari\u00e1ci\u00f3k 2."},"content":{"rendered":"<p>Folytat\u00e1sk\u00e9nt mutatok egy m\u00e1sodik nekifut\u00e1st az <a href=\"http:\/\/soci.hu\/blog\/index.php\/2008\/05\/14\/sql-server-upsert-variaciok-1\/\">el\u0151z\u0151 r\u00e9sz<\/a> probl\u00e9m\u00e1j\u00e1ra. Ebben elker\u00fcltem a felesleges if exists-et, amely miatt egyszer\u0171bb lett az elj\u00e1r\u00e1s. Azt v\u00e1rtam egy\u00e9bk\u00e9nt, hogy gyorsabb is lesz, de nem, hisz most is k\u00e9tszer futunk neki a t\u00e1bl\u00e1nak. <\/p>\n<pre class=\"brush: sql; title: ; notranslate\" title=\"\">\r\ncreate proc dbo.UpsertClient2\r\n  @id int,\r\n  @name nvarchar(100)\r\nas\r\nset xact_abort on\r\nbegin transaction\r\n\r\nupdate dbo.Client with (serializable) set name = @name where id = @id\r\n\r\nif (@@rowcount = 0)\r\n  insert dbo.Client (id, name) values (@id, @name)\r\n\r\ncommit\r\n<\/pre>\n<p>Az el\u0151z\u0151 megold\u00e1shoz k\u00e9pest itt nem kellett updlock hint, hisz az update elve azt haszn\u00e1l az olvas\u00e1si f\u00e1zisban (m\u00e1sk\u00e9nt a sima update-ek is \u00e1lland\u00f3an deadlockoln\u00e1nak, pont ez\u00e9rt van k\u00fcl\u00f6n update lock).<br \/>\nA serializable az el\u0151z\u0151 r\u00e9szben r\u00e9szletezett okok miatt kell, azaz meg kell fogni az update-elend\u0151 kulcsot, ha l\u00e9tezik a sor, ha nem, hogy a k\u00f6vetkez\u0151 insertnek &#8220;meg legyen \u00e1gyazva a hely&#8221;.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Folytat\u00e1sk\u00e9nt mutatok egy m\u00e1sodik nekifut\u00e1st az el\u0151z\u0151 r\u00e9sz probl\u00e9m\u00e1j\u00e1ra. Ebben elker\u00fcltem a felesleges if exists-et, amely miatt egyszer\u0171bb lett az elj\u00e1r\u00e1s. Azt&#8230;<\/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-513","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\/513","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=513"}],"version-history":[{"count":0,"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/posts\/513\/revisions"}],"wp:attachment":[{"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/media?parent=513"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/categories?post=513"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/tags?post=513"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}