Microsoft SQL Server AG Synchronous Mirroring is not synchronous We were upgrading to SQL Server 2012 and decided we wanted to divorce ourselves from replication we were using for Reporting services on Node 2.  AG and mirroring looked like a good solution -- and we went with synchronous.  It was also decided that app's would write to node 1, and read from node 2 to minimize contention on the data.

One app was written to insert the data, and then they check immediately on node 2 whether the data they inserted is present.

Now from my understanding, synchronous mirroring on AG should receive the request, send the data to node 2 -- node 2 commits, then tells node 1 its done and then node 1 returns to the user..

What occurred was not that at all -- they sent the data, did a retrieval from node 2 -- data did not match. Error.

I checked and double checked settings, and had another DBA who had been using AG's and synchronous mirroring look the stuff over -- and our best conclusion is that from what we are seeing Microsoft's 'synchronous' mirroring is NOT synchronous.  Has anybody else seen this?
This is completely expected. I've talked about this before but I'll recap it for you.

There could be multiples reason you are seeing this behavior. The two that most people are confused or inaccurate of how it works are how availability groups synchronize data and how queries on readable secondary replicas work.

Please note the below is ONLY pertinent for SQL 2012 and 2014, and to some extent 2016.

How Data Synchronization Works (brief overview)

There are two types of replica synchronization, synchronous and asynchronous. The way the data synchronization happens in both is exactly the same. The way that SQL Server behaves, though, is different. When synchronous is used we wait for the data to be HARDENED on the secondary replicas. This means it only needs to be acknowledged that it was successfully written to the log, not that the log block shave been successfully redone. Asynchronous does not wait for the status message and just continues.

Thus it is entirely working properly, however there seems to be a misunderstanding as to how it works. AGs ship log blocks, not transactions, thus the entire transaction may not be shipped together and may not even be redone yet.

Querying Readable Secondary Replicas

When you run a query on a secondary replica, the read committed (default) isolation level is silently mapped to snapshot isolation under the covers... whether or not you have SI or RCSI enabled for the database.

Since snapshot works by row versioning and is consistent from the beginning of the transaction, you may not be able to see new data as it comes in per how snapshot isolation works. This is entirely working as intended.

The other point to keep in mind is that just because you have the acknowledgement that the data was hardened to the log on the synchronous secondary replica does not mean that the REDO thread has redone those log blocks yet. Thus, just because you have an acknowledge on the primary doesn't mean REDO has completed on it, only that it has been hardened to the log. Additionally, if your redo thread is blocked it may take a long time (or never) to not be blocked and thus your redo queue size will grow.

In the end I can totally expect the behavior you are witnessing, however it doesn't mean that it isn't synchronous. It just means it doesn't necessarily work the way it was believe or thought to. Hopefully this clears the confusion.