database design – How to model local exceptions to distributed tables

I am looking for possible ways to model a distributed table(s) and provide local exceptions to that table. Consider a situation where head office publishes a list of products/prices that is distributed to branches and they load this price list into their local database. Now sometimes these branches want to change the head office price to a local price. Clearly we can simply update the price in the local database, but then it gets lost when the next head office update is applied. We also only want to block that single column(s), not changes to other fields in the row.

EG. Head office send
| Name | Price |
|——|——-|
| ABC | $5 |
| DEF | $8 |

In one database (out of say 100) we want ABC to have a price of $3. And this $3 should continue to apply even as future updates from head office are received. Head office are not aware of this local exeception.

In the past we’ve done this in two ways. The first was to maintain a second LocalProducts table with the changes to be applied – then when the head office version was applied, any local changes would then be read from LocalProducts and replace the distributed value.
This works OK, but suffers as the table sizes go up.

The second method we tried was to duplicate the column and use coalesce
eg rather than select price where name='abc' we would use select coalesce(localprice,price) where name='abc' This works ok, but had the overhead that we had to change every SQL and remember to do it on future development too.

Are there any other better methods? I am looking for a database agnostic solution, the target databases are ODBC (90% sql/server), SQL-Lite or MS-Access. Access can be ignored if it helps, it is being actively phased out. Engine specific solutions are ok, we can detect the engine and use a different technique, but would prefer not to have a different technique for each engine.

Other notes

  • The data is distributed by the application, not built in replication. This means we can have code/logic during the loading phase
  • Multiple tables are require this feature, not just one as the example above
  • Some of the tables have a reasonable number of columns that could be involved, but typically not.
  • Most of the tables aren’t large, perhaps 50M rows in the biggest, and only 7 rows in the smallest case.

(please feel free to retag this question – not exactly sure which tags are relevant. And yes I have spent many hours over the years investigating this)

distributed computing – Large file uploading in chunks

How to upload large(mb/gb) of video files from client to app server?

The solution as of now I know is pretty simple and widely used is to break file in chunks at at client side and send http post request for each chunk.

Now if I take this to distribute environment, where a app server getting lot of parallel requests from different clients for different chunks or may be same user uploading different files from different endpoints(Browser/app etc.), So how does server know which chunk is for which file?Since server has not generated any filename or fileId on itself ,it has to rely on client to provide this, so does that mean client has to provide a file name and then server will merge all the chunks with same file name per user basis?Does that mean that a user cannot upload two videos with same name from his/her account?

The app server will here generate metadata for each chunk and put the chunk to s3

Map this use case to youtube upload.

domain driven design – How to handle changes to local and global representation of an Entity in a distributed environment?

I’m a developer for a retail enterprise IT department, and am currently stuck on the following scenario:

Department A is responsible for a CRUD service that’s used for handling stores – so when our company wants to open a new physical location, it goes through there.

I’m in department B, which amongst other things is responsible for a geo information system. We are of course interested in e.g. location data of stores.
But to us, a store is one of several entities which are basically a point on a map, though each kind of point also has its own special attributes.
So I’d like to integrate this store data into a more generalized data model (E.g. there is an abstract parent point table with coordinates and point-type, and then the point-specific attributes would be held in child tables).

So I was thinking about the following:
Dept. A publishes changes to stores to a message queue, we consume that event and integrate this store data to our data model.
If our users want to make changes to store attributes through our map application (for example move it, since this is still the planning phase), we’d call the service of dept. A.

What I’m stuck on in my thinking: What if my users want to extend the store data with certain attributes that are of no interest to Dept. A?
So if I want to change the “base-data” through our front-end I’d have to call Service A, but if there’s changes to these “local” attributes, it’d have to go through service B that we’d have to provide?

I’m thinking there might be DDD concepts that help with this, but I’m not sure how to word this scenario to get the right results.

graphs – “Equality” problem in distributed computation

I recently started learning about distributed computation on graphs (not to be confused with parallel computation with threads).

I have seen as a side note in a few lower bound proofs, a reference that says the proof could be shorter using the “linear lower bound theorem for the Equality problem”, but I couldn’t find the statement or proof of this theorem.

I would like to know a few things:

  • What exactly is this “Equality” problem? (how is it formulated?)
  • Where can I find a proof for its lower bound? (or if the proof is short enough, I would be glad if you could add it here)

Thanks!

Is Go’s concurrency model suitable for distributed systems and for microservices?

In Programming Distributed Computing Systems:

7.3.4 Distribution

Distributed computing is inherently concurrent. However, distribution
aspects go far beyond concurrency. Of particular importance from a
modeling perspective is the capability to reason about the location
(and potential co-location) of concurrent com- putations, the
heterogeneous cost of interaction, the security aspects of interaction
across multiple locations, and the potential for partial failures.

The concurrent computing models that we have studied offer diverse
levels of abstraction and therefore support reasoning about
distributed computation to different degrees.

In the π calculus, there is no explicit representation of locations.
Processes can arbitrarily and uniformly interact with any other
processes, as long as they share a channel name. Therefore, from a
modeling perspective, it is not directly possible to distinguish
between the interaction of two processes in the same processor and the
interaction of two processes in two computers across two different
continents. Therefore, in an executable realization of the π calculus,
a channel abstraction may be implemented as a single machine register
in the simplest case, or it may require a network socket
implementation requiring the coordination of multiple servers for
interprocess communication. Since channels are first-class communicable
entities and process communication topology is thus dynamic, this
so-called process “mobility” may require significantly expensive
channel implementation strategies. Furthermore, truly synchronous
communication is impossible to implement among processors in
geographically distant computers.

In the actor model, sequential computation is modeled using the λ
calculus, making the distributed interaction between actors explicit
through a network, mod- eled as a multiset of messages en route. The
state encapsulation and asynchronous communication aspects of the
actor model (see section 7.1) make it better suited to model and
implement distributed computing systems. However, there is no explicit
notion of actor location, and therefore, communication between actors
in the same processor and actors across the planet is uniformly
modeled.

If I am correct that

  • Go has an implementation of pi calculus,
  • Go is recommended for distributed systems and for microservices

my questions are:

  • Is it Go’s implementation of pi calculus that is recommended for distributed systems and for microservices ?

  • If yes, why, given the quote saying pi calculus is not good for distributed computing?

Thanks.

CU22 installation on Distributed Always ON – SQL Server 2017

We’re planniing to install SQL 2017 – CU22 on Distributed Always On environment without taking any downtime. Can you please review below steps and confirm if need to follow any additional steps to avoid downtime and impact on business.

DC1:

dc1-db1 (primary)

dc1-db2 (secondary)

WSFC Name: wsfcdc1

DC2:

dc2-db1 (secondary)

dc2-db2 (secondary)

WSFC name: wsfcdc2

dc2:

  1. change failover mode to manual on dc2-db1
  2. install CU22 on dc2-db1
  3. reboot dc2-db1
  4. install CU22 on dc2-db2
  5. reboot dc2-db2
  6. Change failover mode to automatic

dc1:

  1. chnage failover mode to manual on dc1-db1
  2. install CU22 on dc1-db1
  3. reboot dc1-db1
  4. install CU22 on dc1-db2
  5. reboot dc1-db2
  6. perform manual failover from dc1-db2 to dc1-db1
  7. Change failover mode to automatic

Thanks in advance.

Database consistency for a distributed system

I’m working on a phone call monitoring project. The aim is to have one row in the calls table for each ongoing call. Each call instance may be updated by many different servers/threads as more information is gathered about the call. Each call instance (a row) is mapped to a Java object. When a server/thread needs to update a call, it will read the call, apply the update, and then write the call back to the database.

This obviously introduces consistency problems, if a separate update U2 writes in between the read and write of another update U1, then when U1 is written, it writes stale data.

My current idea to fix this is to capture the changes for an update. When an update happens, it will read a “last updated” time. Then, the changes are applied and written back to the database conditionally (only written if last updated is equivalent to what was read). If the “last updated” time has changed, then a retry is executed, re-reading the data, applying changes, and attempting another write. This will retry the write 3-5 times before giving up.

This has some drawbacks in regards to performance, but is the best I was able to come up with. There is also the rare yet possible problem of retries executing out of order (across different updates). I’m hoping to make all changes independent of the order they are executed in, and I know that order shouldn’t be relied on. A good example of this is call status. Say update U1 sets a call status to ongoing and U2 sets it to finished. If U1 fails, U2 completes successfully, and then U1 completes on a retry, the status will be incorrectly set to ongoing. One option is only allow ongoing to be set when the call status isn’t finished, but this isn’t always clear of all fields and feels a bit messy. There are always options to try to update columns individually, come up with some sort of merging policy, or something else.

My primary goals are consistency and performance. While these criteria are somewhat contradictory, NoSQL seems like a decent tradeoff for my goal (my decision is described more below).

My questions:

  • Is there a better way to achieve this goal? It feels sloppy to have every change get recorded as deltas and executed in a retry loop, but I’m not sure if there’s a better way to do it.
  • I’m currently using NoSQL. Data loss is tolerable, so replication is disabled. I am using conditional transactions to implement updates. Is this the right technology for the job? With replication disabled, I expect that to help considerably in addressing the performance hits introduced by transactions. Scylla/Cassandra’s ring hashing seems to be an efficient way to distribute the data, especially with the added benefit of handling call data close to its geographic location. I’ve also considered Redis, but decided against it as the added database i/o isn’t significant, as well as Redis’s (somewhat?) poor distributed performance.

It seems like locks would work well in this scenario, but I’m not sure if NoSQL/Scylla supports locks. Should I switch to something that does?

Appreciate the help, will edit with any information if needed. Thanks!

security – How can users verify a game distributed peer-to-peer has not been modified maliciously?

I’m interested in p2p software distribution, and I’m also directly including a modding interface into my game, which is built on my own custom engine.

I was working with a folder inside of the Roaming folder to store the data (resource, saves… etc), and I quickly figured out a malicious programmer could easily modify my program and make or erase (at least) folders.

How would one prevent this kind of modification, without owning the whole distribution process?

Do you really have to trust the provider/source to trust the software? Or could you trust software on the client side using some hash or such?

The only solution I can think of would be having a website on which I put a hash of my game, and any user that gets an instance of my game can verify it was not modified by hashing it and comparing it with the one on my website. But it is definetely not user-friendly at all…

Why is it considered hard to maintain strong consistency in a distributed system?

Why can’t you just use strongly consistent reads for all your DB reads, with retries on 500 responses? According to CAP theorem increasing consistency should probably lower availability, but can’t the decreased availability (increased 500 responses) be handled fairly easily using retries? (assuming you are fine with a small percentage of queries taking a bit longer due to retries)

Using DynamoDB as an example, but this can be generalized to any noSQL cloud offering – It also seems like DDB with on demand scaling will simply increase your read capacity units (RCU) used if you turn on strong consistency, incurring a higher cost ($) but keeping the same latency on db queries, so it seems like the negative is only higher cost. It seems like you can just keep vertical scaling the DB’s processing power to meet your needs. Is it actually plausible that with a noSQL cloud database with a high traffic level, you cannot just throw enough money at it, and it could hit some scaling limit to make strongly consistent reads slower?

And then with regard to the entire question generalized to distributed systems, what does it actually mean for a distributed system to be ‘strongly consistent.’ I’ve heard this used to describe systems before but I don’t actually know it means beyond ‘all DB interactions being strongly consistent.’


My second question might be more basic level but necessary to understand the cost of providing consistency, but why do consistent read issues actually occur, ie. why does stale data occur (in single queries like read/write, not transactions with multiple reads/writes per transaction)?

From what I understand, with decreasing probability any time after a write occurs, it’s possible for one reader to read correct data, then a second reader to read stale data AFTER the first reader reads correct data (correct me if this isn’t actually true, but my understanding was it is). Why does this happen? Doesn’t a read just involve a read from some location on the disk?