Home Snapshot on, still deadlocks in SQL Server
Reply: 0

Snapshot on, still deadlocks in SQL Server

user44141
1#
user44141 Published in September 21, 2018, 8:04 am

I enabled snapshots in my SQL Server 2016 database, 16 Core CPU, 43 GB memory.

ALTER DATABASE MyDatabase
SET ALLOW_SNAPSHOT_ISOLATION ON

ALTER DATABASE MyDatabase
SET READ_COMMITTED_SNAPSHOT ON

After that, I got deadlock on my database

Transaction (Process ID 69) was deadlocked on lock | communication buffer resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

I checked the error line number -Delete operation on table

Tables has 7882884 rows with two columns.

Also my stored procedure is running with a begin / end trans block where my table is written

Regards, Manish

share|improve this question
  • My Delete operation is delete from tablename where id=@id – MANISH Nov 18 '17 at 10:08
  • error log-Error Number-1205, ErrorSeverity-13- Error State-78, Maxdop=10,cost threshold for parallelism=10 i have total 16 Cpu and SQL Server Memoery-43GB – MANISH Nov 18 '17 at 15:42
  • READ_COMMITTED_SNAPSHOT can help avoid deadlocks when readers block writers and visa-versa. However, it will not avoid deadlocks between writers. I suggest query and index tuning to ensure only data needed by the delete query is touched. Check the system_health Extended Event session to get the XML deadlock graph event, which will have details of the queries involved. – Dan Guzman Nov 18 '17 at 19:24

active oldest votes

Your Answer

StackExchange.ifUsing("editor", function () { StackExchange.using("externalEditor", function () { StackExchange.using("snippets", function () { StackExchange.snippets.init(); }); }); }, "code-snippets"); StackExchange.ready(function() { var channelOptions = { tags: "".split(" "), id: "1" }; initTagRenderer("".split(" "), "".split(" "), channelOptions); StackExchange.using("externalEditor", function() { // Have to fire editor after snippets, if snippets enabled if (StackExchange.settings.snippets.snippetsEnabled) { StackExchange.using("snippets", function() { createEditor(); }); } else { createEditor(); } }); function createEditor() { StackExchange.prepareEditor({ heartbeatType: 'answer', convertImagesToLinks: true, noModals: false, showLowRepImageUploadWarning: true, reputationToPostImages: 10, bindNavPrevention: true, postfix: "", onDemand: true, discardSelector: ".discard-answer" ,immediatelyShowMarkdownHelp:true }); } });
 
StackExchange.ready( function () { StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f47364786%2fsnapshot-on-still-deadlocks-in-sql-server%23new-answer', 'question_page'); } );

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Browse other questions tagged sql-server sql-server-2016 or ask your own question.

StackExchange.ready(function(){$.get('/posts/47364786/ivc/6832');});
StackExchange.ready(function () { StackExchange.responsiveness.addSwitcher(); }) (function(i, s, o, g, r, a, m) { i['GoogleAnalyticsObject'] = r; i[r] = i[r] || function() { (i[r].q = i[r].q || []).push(arguments) }, i[r].l = 1 * new Date(); a = s.createElement(o), m = s.getElementsByTagName(o)[0]; a.async = 1; a.src = g; m.parentNode.insertBefore(a, m); })(window, document, 'script', 'https://www.google-analytics.com/analytics.js', 'ga'); StackExchange.ready(function () { StackExchange.ga.init({ sendTitles: true, tracker: window.ga, trackingCodes: [ 'UA-108242619-1' ] }); StackExchange.ga.setDimension('dimension2', '|sql-server|sql-server-2016|'); StackExchange.ga.setDimension('dimension3', 'Questions/Show'); StackExchange.ga.trackPageView(); }); /**/ var _qevents = _qevents || [], _comscore = _comscore || []; (function() { var ssl = 'https:' == document.location.protocol, s = document.getElementsByTagName('script')[0], qc = document.createElement('script'); qc.async = true; qc.src = (ssl ? 'https://secure' : 'http://edge') + '.quantserve.com/quant.js'; s.parentNode.insertBefore(qc, s); _qevents.push({ qacct: "p-c1rF4kxgLUzNc" }); /**/ var sc = document.createElement('script'); sc.async = true; sc.src = (ssl ? 'https://sb' : 'http://b') + '.scorecardresearch.com/beacon.js'; s.parentNode.insertBefore(sc, s); _comscore.push({ c1: "2", c2: "17440561" }); })();
You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.309829 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO