Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

read_commited_snapshot and update transactions

Posted on 2010-11-15
5
Medium Priority
?
582 Views
Last Modified: 2012-05-10
Hello,

Can the option read_commited_snapshot allow two transactions  to make an update on the same table but on the different lines?

Thanks regards

Bibi
0
Comment
Question by:bibi92
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
5 Comments
 
LVL 1

Accepted Solution

by:
Bogdan_X earned 2000 total points
ID: 34143202
Yes it can, but you can still get deadlocks - you should also check the clustered key for that table, so you don't update the same key / page.
0
 

Author Comment

by:bibi92
ID: 34143661
Exactly, I still get deadlocks, how can I resolve them? Can you explain me check the clustered key for that table, so you don't update the same key / page.
Thanks

bibi
0
 

Author Comment

by:bibi92
ID: 34143680
herewith the query :
Update TEST_ENV_ENT.TEST_ENV_ENT.ENT_LIGNE_FACT
Set  LDV_TECH_DATE_SUPP=CURRENT_TIMESTAMP
Where Not Exists (Select 'X'
From TEST_ENV_ENT.dbo.I$_ENT_LIGNE_FACT_REG_650 S
Where TEST_ENV_ENT.TEST_ENV_ENT.ENT_LIGNE_FACT.AGE_CODE = S.AGE_CODE
And TEST_ENV_ENT.TEST_ENV_ENT.ENT_LIGNE_FACT.DEV_CODE = S.DEV_CODE
And TEST_ENV_ENT.TEST_ENV_ENT.ENT_LIGNE_FACT.LDV_NUM_INTERNE = S.LDV_NUM_INTERNE
)
And  LDV_TECH_DATE_SUPP Is Null
And  REG_CODE = SubString('REG_650', 5, 3)
 
Error :
1205 : 40001 : com.microsoft.sqlserver.jdbc.SQLServerException: Transaction (Process ID 86) was deadlocked on lock | communication buffer resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

Thanks
bibi
0
 

Author Closing Comment

by:bibi92
ID: 34148644
Thanks bibi
0

Featured Post

Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

715 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question