Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 130
  • Last Modified:

reading data from replication SQL database

Hi ,

I need to ask one of your expert that if there is possibility that DEAD LOCK condition happen on a replicated SQL server database?

I mean will the Database fall over due to dead lock condition and will interrupt the main production server?
0
ken hanse
Asked:
ken hanse
  • 3
  • 3
1 Solution
 
ZberteocCommented:
If it is a merge replication I think it could be possible. In it is a transnational replication then deadlocks that happen on the publisher(source) cannot cause interruptions on subscriber(target) database.
0
 
Chris LuttrellSenior Database ArchitectCommented:
Just from Google searches, Yes, deadlocks can happen.  Have only done done one way Publish/Subscribe myself.
0
 
ken hanseAuthor Commented:
Just to clarify the answers, if I read the data on replicated data source on my ETL, can the DEAD LOCK condition could  occurred in the sources database if I read/write happened in a same time frame?
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
ZberteocCommented:
What do you mean by "replicated data source"? Please use the correct terminology. In a replication process the source server/database is called publisher and the target server/database is called subscriber.

What ETL are you talking about? You use some third party process ETL on top of replication?
0
 
ken hanseAuthor Commented:
Hi Zberteoc,

I'm a ETL developer, I connect to the Data sources and bring the data into Data warehouse.

For the Data source, I use the replicated data source which is subscriber. My ETL connect to the subscriber.

Hope this will clear your answer.
0
 
ZberteocCommented:
SQL server is pretty smart and efficient when applies the transactions through replication but in a scenario when a large transaction is replicated, lots of rows, it will create locks on the subscriber. One work around is to use the NOLOCK hint in your ETL queries done against your data source.

SELECT ... from YouTable WITH(NOLOCK) ...
1
 
ken hanseAuthor Commented:
This is a best solution.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now