5

Here's a very basic question about replication.

If you set up your database with master-slave replication where writes go to the master and reads go to the slaves, doesn't that mean that newly-written data won't necessarily show up in a read until some time later?

For example, suppose a user posts a comment on your website (a write: INSERT INTO comment ...) and then refreshes the page (a read: SELECT * FROM comment ...). The read goes to a slave where the INSERT statement hasn't been replicated yet, so the list of comments comes back without the new one in it. That would effectively violate the "Durability" part of ACID, at least temporarily.

Is that how it actually works? Or is there some way to ensure that users will always be able to read what they just wrote?

gesgsklw
  • 741
  • 1
  • 7
  • 13
  • I don't think you have the right idea for master->slave replication. The slave will almost always be in synch with the slave and when an INSERT is made to the master database, the slave will also contain that data when in the INSERT is made. – ryekayo Apr 18 '16 at 23:29
  • Which DBMS are you talking about? And what kind of replication did you configure? –  Apr 19 '16 at 06:34

1 Answers1

2

Master->Slave replication databases work where the master database gets written to and the Slave is read. When an INSERT is made into the master database, the slave will contain that information given that the master->slave are configured and in synch. So for example, if I wrote:

INSERT into TEST_TABLE (col1, col2) values ("test", 123);

If I went to the slave database and queried:

SELECT col1, col2 FROM TEST_TABLE;

I will see the values I inserted into the master database. I would recommend looking at the documentation for the flavor of SQL you are using for more details on master->slave replication. As mentioned in the comments, it does matter what flavor of SQL you are using as well as the configuration you have to enable to frequency of replication to occur.

ryekayo
  • 2,341
  • 3
  • 23
  • 51
  • 1
    I was about to post a similar answer, so I will just comment here instead. As mentioned, it does depend on what DBMS you are using, and more importantly, how you have your replication set up. There should be a setting indicating the replication frequency, usually a matter of seconds. So depending upon what you have it set to, it should be something like 5-10 seconds unless you need it different depending upon your environment and needs. TLDR: It's up to you how fast your data is replicated. – gmiley Apr 18 '16 at 23:37
  • Good catch on that one.. :) – ryekayo Apr 18 '16 at 23:38
  • I know that eventually the slaves contain the same data as the master. I want to know what happens during the time after the data has been written to the master but before it gets replicated to the slaves. – gesgsklw Apr 19 '16 at 01:30
  • I think that all depends on the configuration you have set for the database. You should check with the documentation for the flavor of SQL you use – ryekayo Apr 19 '16 at 03:16