Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

read_commited_snapshot and update transactions

Posted on 2010-11-15
5
Medium Priority
?
586 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
4 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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
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.
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…

782 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