Questions[data-consistency](server)

Root DNS servers may be the most typical primary(not read-only) DNS servers. There are currently 13 IP addresses for root DNS servers; however, considering the IP anycast technology, more than 13 root DNS servers are deployed worldwide.

If someone wants to update a DNS record for all root DNS servers, is there some sync or replication mechanism between all these DNS servers?

More complex, if two people want to update the same DNS record simultaneously, how does DNS solve the potential conflict?

e.g., person A updated a DNS record r1 to r2 on a.root-servers.net.(deployed at region A), and at the same time, person B updated the same DNS record r1 to r3 on a.root-servers.net.(deployed at region B); moreover assuming there is significant network latency between region A and region B.

The second problem is similar to a database cluster in that there is more than one primary database server, and these primary servers sync data from all other primary servers.

I have an AlwaysOn cluster of SQL Server 2019, containing an Availability Group of 3 replicas in Synchronous mode. According to Microsoft documentation:

3. The secondary replica hardens the log and returns an acknowledgement to the primary replica.
4. On receiving the confirmation from the secondary replica, the primary replica finishes the commit processing and sends a confirmation message to the client.

This article goes into greater detail and explains that:

5. In the secondary replica, Log Receive gets the log records from the primary replica and writes to Log cache. This process is repeated on each secondary replica participating in synchronous-commit mode.
6. On each secondary replica, Redo thread exists, and it writes all changes mentioned in log records to the data page and index page. It flushes the log for hardening on secondary database log.
7. As stated earlier, in synchronous data commit, primary replica waits for the acknowledgement from the secondary replica. At this stage, secondary replica sends an acknowledgement that transaction hardening is completed on secondary.
8. Once Primary replica, receives an acknowledgement from the secondary replica, it sends the transaction completion message to the client.

So if I understand right: If I update a record via Primary replica successfully, this updated value should be immediately available for clients querying the Secondary replicas.

However, when I test this, this doesn't work so. I run a simple batch file, looking like this:

sqlcmd -E -S tcp:SQL-AG-Listener -d TestDB -Q "BEGIN TRANSACTION; UPDATE TestSyncTable SET CurrentTime='%currentTime%'; COMMIT TRANSACTION;"
sqlcmd -E -S tcp:SQL-Server01 -d TestDB -Q "SELECT * FROM TestSyncTable" -K ReadOnly
sqlcmd -E -S tcp:SQL-Server02 -d TestDB -Q "SELECT * FROM TestSyncTable" -K ReadOnly
sqlcmd -E -S tcp:SQL-Server03 -d TestDB -Q "SELECT * FROM TestSyncTable" -K ReadOnly

So I'm updating the CurrentTime field via the Primary replica (hosting the AG Listener) and then reading it right away via all three replicas. Each sqlcmd command is a separate client process, so it opens its own independent TCP connection.

And then I see something like this:

SQL-Server01: CurrentTime = 20:02:19.93
SQL-Server02: CurrentTime = 20:02:16.94
SQL-Server03: CurrentTime = 20:02:19.93

(Reformatted the output for better readability here)

As far as I've seen, the Primary replica always returns the updated value. And the Secondaries also do - but only some short delay.

So the question is: why? Shouldn't Synchronous mode guarantee that the result of reading operation is consistent with the writing one? If the Secondary replica sends acknowledgement only after its Redo thread updates the data page - then how can it be?

Thanks, Mucius.

Recently I've seen the root filesystem of a machine in a remote datacenter get remounted read-only, as a result of consistency issues.

On reboot, this error was shown:

UNEXPECTED INCONSISTENCY: RUN fsck MANUALLY (i.e., without -a or -p options)

After running fsck as suggested, and accepting the corrections manually with Y, the errors were corrected and the system is now fine.

Now, I think that it would be interesting if fsck was configured to run and repair everything automatically, since the only alternative in some cases (like this one) is going in person to the remote datacenter and attach a console to the affected machine.

My question is: why does fsck by default ask for manual intervention? How and when a correction performed by such program would be unsafe? Which are the cases when the sysadmin might want to leave a suggested correction aside for some time (to perform some other operations) or abort it alltogether?

I am expecting to have a long backup on a certain database and I was wondering if the application can access (read only? read/write?) it while it is being backed up.

If read/write access would be available then how will consistency be achieved? Probably only a snapshot of what was available at the time of the start of the backup will be included in the backup?

Or will the database be non-operational during backup?

What steps must one take to ensure that an otherwise defaultly-configured InnoDB server is truly ACID compliant? The InnoDB configuration page mentions that the hardware itself must be configured to honor fsync calls, i.e. disable any write-back caches.

This page mentions some other concerns, but may be conflating the binary log and the InnoDB log, and may be a bit out of date regarding default settings for MySQL 5.x.

Upon reading the binary log document page it would seem that the "sync_binlog=1" setting is not required for ACID properties in general, only for ACID properties vis a vis point-in-time recovery and replication.

So, is disabling write-back disk caching sufficient, or are there other settings that must be tweaked?