Why is there a delay before my replica DB sees inserts?

Question

I am using SQL Server 2005 Standard Edition. I have a primary DB and a read-only replica with Transaction replication configured. My understanding is that with transactional replication that the 'INSERT' statement would not return until

1. The row is inserted into the primary, and
2. The row was completely replicated to the replica DB

However, I am experiencing a problem where we do an insert, it returns (I know this because I am using an identity PK column, and I have my ID value). I then run a SELECT statement on the replica DB where I attempt to lookup the row by ID, but it does not yet exist. If I wait a few more seconds everything is fine. Is my understanding of transactional replication off? Is there a way to make it work the way I am thinking?

Answer 1

Your understanding is incorrect.

Transactional replication can have significant latency between transactions committing on the publication database and being applied to the subscription database(s).

Firstly the log records for the committed transaction are harvested from the transaction log of the publication database (by the log reader agent job) and (basically) put into the distribution database. Then the distribution agent job pushes or pulls the changes from the distribution database to the subscription database.

Depending on the workloads on these 3 machines (if they're separate) and the network bandwidth between them, the latency may be seconds, minutes, or longer.

You're thinking of synchronous database mirroring - where transactions cannot commit on the principal until all log records for it have been hardened on the mirror database's log drive. Transactional replication is an asynchronous process and is working exactly as it should.

Hope this helps.

Answer 2

I think you are expecting synchronous (mirroring) behavior from replication.

Replication behavior is asynchronous and your subscriber may not see changes immediately.

See: http://msdn.microsoft.com/en-us/library/ms151706.aspx

Edit: Specifically this document talks of achieveing at best minimal latency under certain configurations, not synchronous commits.