15

You're going to have to make your application not put the ORDER BY inside the subquery (maybe it has an option to not use a needless subquery in the first place). As you've already discovered, this syntax is not supported in SQL Server without TOP. And with TOP, unless you want to leave some rows out, using TOP 100 PERCENT is going to render the ORDER BY ...


10

Ensure you have a valid backup; hopefully it will be from prior to the corruption, but not so long ago that the data isn't useful. You should set this aside in case direct repair isn't possible and you need to recover data from the backup. The documentation explains what to do to correct the problem - you can try the REPAIR_REBUILD option, and if that doesn'...


9

SQL Server uses checkpoint, log sequence numbers, and the transaction log to guarantee that a full backup is restored to a transactionally-consistent state, as of a particular point in time. The exact point in time for a full database backup is the end of the data-reading portion of the backup process. To recover to an arbitrary point in time requires ...


7

You are right, the design allows inconsistencies, exactly what you notice. A ProjectRealm may be referring through Project to a company and through CompanyRealm to another company. This is not uncommon, it appears when there is a triangular or a "diamond" shape in the relationships: Realm Company \ / \ \ / \ ...


7

Which "same result" do we have in the example? Which reordering can we have to produce the same result? The opening quote is an incorrect statement of serializable isolation. The definition of the serializable isolation level in the SQL-92 standard contains the following text (emphasis mine): A serializable execution is defined to be an execution of the ...


7

I think the difference is that the two terms refer to different things: Consistency refers to Transactions (it's the C in ACID). Integrity refers to Databases. A transaction is said to be consistent if it keeps the integrity of the database (i.e. if it transforms the database from a valid state to another valid state).


6

CAP and ACID are discussing different things, though both use the C for Consistency. Read John Huggs's post: https://www.voltdb.com/blog/disambiguating-acid-and-cap ACID is defining what it takes to make a what is normally described as making a "true transaction" fulfilling the ACID goals. In actuality, it seems that the C for Consistency is more for the ...


6

Databases have two concurrency requirements. One is the very short term physical management of memory blocks as they are referenced. These are known as latches in the DB world and can be implemented using mutexes and the like. The concern here is for the stability of a block of memory while a worker thread is accessing it. The second controls the validity ...


4

To directly answer your questions: "Do relational databases ensure consistency in reads?" A: In short, when the ACID principals, and specifically the Consistency part, were defined, this was long before distributed data was a concept for relational databases. So the Consistency from that standpoint was automatic because there's only a single ...


3

In addition to what Michael Green has pointed out in his excellent answer, you should also be aware of optimistic concurrency which is an application-level technique in a database that is used to guard against two users (or processes) attempting to modify the same piece of data. The technique is used when there is a low, but non-zero chance of two updates ...


3

C stands for eventual consistent. Correct ? Consistency in the CAP theorem is referring to strong consistency where every read receives the most recent write or an error. By default MongoDB drivers direct all reads & writes to the primary of a replica set, which is strongly consistent. The CAP theorem asserts that a distributed system must choose ...


3

I'll try to answer only 1: When the data is distributed across multiple servers (ex 1 master- n slaves configuration) Do relational databases ensure consistency in reads? I mean a committed read is immediately available to be read by other transactions? I suspect not given network is involved, making Relational DBs as eventual consistent DBs. This depends ...


3

To answer your questions directly: "Since we'll have different sheet structures, is NoSQL a better option than a relational database?" A. Probably not, unless each of those sheet structures are very tightly related as one application that they'd all be stored in the same few tables. Otherwise it sounds like you just have a series of structured ...


2

It looks like your namespace file is still corrupt, you get similar errors when you hit the max number of collections for a database in MMAPv1. To try to fix this up and get a database that is in a more consistent state you can try recreating your collections in a new database. If you have auth enabled, you will need to do this in the admin database - note ...


2

Here's a great article on recovering a faulty db: www.sqlservercentral.com/articles/Corruption/65804/ Firstly it advises things like: don't panic, don't restart the server, don't run a DBCC CHECKDB(DB_NAME, REPAIR_ALLOW_DATA_LOSS) Then takes you through common fixes to common errors in the database, such as: - Inaccurate space metadata - Corruption only ...


2

As per the definitions you've quoted in your question description, assertions are logical tests that trigger exceptions on failure. These are used within most automated testing suites as well as at runtime. In test suites assertions confirm that the results of a function call or data update are as expected and are often set up as macros indicating the ...


2

I have zero to none experience in the world of NoSQL so I'll add some perspective from a RDBM:s side of view. I believe assertions where introduced in SQL92, the following is an example from: https://mariadb.com/kb/en/sql-99/create-assertion-statement/ CREATE ASSERTION constraint_1 CHECK ((SELECT AVG(column_1) FROM Table_1 >40) NOT DEFERRABLE; ...


2

The effects of a trigger 'belong' to the statement that triggered it, so everything will be rolled back together (with or without explicit transactions): > create table a(x int); > create table b(y int check(y > 0)); > insert into b values(1); > create function t() returns trigger as $$ begin update b set y = -1; return null; end; ...


2

One common way is to add a classifier that is "inherited" like: CREATE TABLE products ( product_id ... NOT NULL PRIMARY KEY , ... , product_type ... NOT NULL , UNIQUE (product_id, products_type) , CHECK (product_type IN ('food', 'furniture')) ); product_type would typically be a code of some kind. Might be a foreign key to a "lookup&...


2

With having QUORUM for both read & write requrests you achieve strong consistency per following equation R + W > N, where R - read replica count, W - write replica count, and N - replication factor. If you write with QUORUM, then 2 nodes will get fresh data, and when you read with QUORUM, then one node may have the old data, and one will have fresh ...


2

Your question seems to be logically inconsistent, as tolerating a latency in persistence and "Actually I can afford to lose transactions that are in memory only" by definition violates the "D" in "ACID". If you really want to be just "ACI" compliant, you can turn off "synchronous_commit". Note that this means you could report to the traders that their ...


2

I think it depends on whether you want to rely on the linked server configuration option 'Enable Promotion of Distributed Transaction' or whether you want to be sure that your transaction can run only if it's distributed.


1

Michael green and Joel Brown have given excellent answers that cover most of the ground. Let me add my two cents. If concurrency control is basically about preventing phantom updates, the question arises about what level of granularity is relevant for the data containers being protected. For an OS, the user data being protected from phantom updates boils ...


1

Maybe I'm simplifying the requirement, but to satisfy the requirement and your concern is to simply set the required columns in the subtype to NOT NULL. When a new PRODUCT row of food is created, a new FOOD row will also be created, and if no info is provided required for FOOD, then the database will reject the transaction.


1

What you are proposing is not really CAP Theorem unless the cluster is totally a share-nothing architecture. Please note what CAP Theorem is from my old post from Jan 01, 2013 : Consistency in ACID and CAP theorem, are they the same? CAP Consistency (All Nodes Have Same Data via Eventual Consistency) Availability Partition-Tolerance : system continues to ...


1

You have two unique constraints, and you say that the one on shortname causes an error only rarely. So use INSERT ... ON CONFLICT ON (shorturl) DO NOTHING; Then check if you got an error and how many rows were inserted. if you got an error, prompt the user for a new shortname if no row was inserted, generate a new shorturl Then you will only get error ...


1

Is it possible to do this in multiple queries but still realize the same level of consistency as having performed these individual selects as a single query? It depends on the RDBMS. In SQL Server you can use a transaction with SNAPSHOT transaction isolation level to get a consistent view of the database across multiple queries. The isolation level Oracle ...


1

For SQL Server: But what's about single statements? In SQL Server, there is 4 transaction isolation levels (in pessimistic locking model). Default transaction isolation level is Read Committed, and locks are placed on a statement level. If you have a transaction that has 2 statements inside it that retrieve same data, and in the middle of that ...


1

I think you are correct. We need at least two distinct subnet to talk about network partitioning. But if your 10 nodes are in the same subnet, what is the point ? If your network is down, you loose your cluster ! Best practice would be to have 5 nodes in one datacenter, 5 nodes in another data center.


1

Mostly yes, but with small correction. You can get the stale data if they aren't replicated yet to another node (replication happens almost at the same time, but still could be delayed because of garbage collection, etc.). You can get strong consistency by tuning consistency levels... The comment is about point 3c - Cassandra's partitions are replicated to ...


Only top voted, non community-wiki answers of a minimum length are eligible