Solved

read_commited_snapshot and update transactions

Posted on 2010-11-15
5
576 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
  • 3
5 Comments
 
LVL 1

Accepted Solution

by:
Bogdan_X earned 500 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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Row insertion failed. Array 5 48
SQL Query 2 34
SQL server 2014 replication error 20 41
SQL 2012 clustering 9 13
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

831 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