Questions tagged [consistency]
The consistency tag has no usage guidance.
37
questions
0
votes
1
answer
68
views
Non-Repeatable Read vs Phantom Read - Real World Scenario
About the mentioned 2 Data concurrency problems below is what I understand.
Non-Repeatable Read - is where within same transaction running the same query twice returns 2 different values for the same ...
3
votes
1
answer
183
views
Table Consistency Errors
We have encountered database corruption / consistancy issue in one of our database tables, which I'd like to run past the community.
Disclaimer: This is a MSSQL database we (the DBA team) have ...
- 191
0
votes
1
answer
75
views
How repeatable read isolation level and others are implemented in distributed/replicated databases?
I'm studying distributed systems/DBs and I'm struggling understanding isolation levels when we talk about distributed systems.
Avoiding problems like dirty read, non-repeatable read, phantom reads, ...
- 1
0
votes
0
answers
52
views
Best Practice to Perform DBCC CHECKDB [duplicate]
We are looking for an optimal strategy to perform consistency checks (DBCC CHECKDB) in our environment.
Currently we run the following on each database once a day starting at 04:30 am (when workload ...
- 1
2
votes
1
answer
1k
views
When to use BEGIN DISTRIBUTED TRANSACTION instead of BEGIN TRANSACTION in SQL Server?
For this question lets assume there are two servers, ServerA and ServerB, and there is a linked server setup on ServerA to ServerB. Let's also assume I have REMOTE_PROC_TRANSACTIONS set to ON for both ...
- 30.6k
0
votes
1
answer
118
views
Choosing between NoSQL and Relational databases while having both flexibility and consistency requirements [closed]
I've never worked with databases professionally and only toyed with database coding using a small sqlite db for my own music collection. Now I'm asked to build an internal database system at work for ...
- 111
4
votes
3
answers
377
views
Inconsistency in Relational Databases
I generally get confused when the term consistency is used. NoSQL tutorials always refer to the reads whereas Relational tutorials refer to a consistent state (ensuring referential integrity ...
- 161
0
votes
1
answer
255
views
Consistency of data between two databases on different server
I have to guarantee consistency of data stored in two databases installed on different servers.
The first one is a MySQL database and the second is a SQL Server database.
Data properties are not the ...
- 109
1
vote
0
answers
69
views
The overhead of OCC validation phase
The validation phase of optimistic concurrency control has two derctions: one is backward validation, checking for conflicts with any previously validated transaction, the other is forward validation, ...
- 41
1
vote
3
answers
543
views
What is the difference between concurrency control in operating systems and in trasactional databases?
Recently I'm learning concurrency control techniques in transactional databases. However, I am so confused about the differences between concurrency control in operating systems and in transactional ...
- 41
2
votes
1
answer
550
views
CAP Theorem and MySQL
There's a CAP Theorem: R + W > N. Let's say I have W=1 (master), R = 6 (slaves), N = 6, so one slave can write and read. As the theorem says we could be sure in strong consistency (we always will ...
1
vote
1
answer
175
views
How to avoid errors in server log when conflict fails within a transaction before retry?
We have a table such as
create table pages (
id uuid primary key not null,
parent_id uuid not null references pages(id),
shortname text not null,
shorturl text not null,
constraint ...
0
votes
1
answer
38
views
How can I generate a consistent entity graph (say from 10 tables) from a database fetch
Suppose I have many tables in a database that need to be joined together to get a single consistent view of the entire entity graph. Am I forced to do this as a single query? I don't care about locks, ...
4
votes
1
answer
473
views
How the single SQL statement is isolated?
All the books I've seen so far, talking about transactions show scenarios with several SQL statements involved.
But what's about single statements? What level of isolation do they have? Is it ...
- 141
1
vote
1
answer
122
views
Optimize queries that need to be 100% ACID compliant but can tolerate latency in persistence
Introduction
I'm writing a trading program in C++. The program involves having user balances and orderbooks. The orderbook is basically a set of buy and sell orders. When submitting an order, say a ...
6
votes
3
answers
2k
views
Solving supertype-subtype relationship without sacrificing data consistency in a relational database
Trying to get better at designing databases, I noticed I'm always stuck trying to solve variations of the exact same problem.
Here is an example using common requirements:
An online store sells ...
- 61
2
votes
1
answer
63
views
Network Partitioning
My current understanding of the network-partitioning scenario, also mentioned in the CAP theorem, is that it's a scenario where the clients are not able to connect to some DB nodes even though they ...
1
vote
3
answers
210
views
Is it safe to insert the newest row with SELECT INTO?
Trying to keep rows consistent then referencing previous rows.
CREATE TABLE transactions (
id int PRIMARY KEY
GENERATED BY DEFAULT AS IDENTITY,
account_id bigint ...
0
votes
2
answers
630
views
Handling read conflict in cassandra?
Say we have three nodes(simple replica no sharding) i.e. N1,N2 and N3 with replication factor as 3.
As read and write request can go to any node, Say Request write request(to update the same ...
- 389
5
votes
1
answer
3k
views
Mongo CP, Cassandra AP?
I have read lot of articles on net but still confused why Mongo CP, Cassandra AP, RDBMS CA ?
Will explain my understanding and query along with that .
Mongo
Consider a scenario where I have one ...
- 389
2
votes
1
answer
1k
views
Does encapsulating a statement in a transaction include the triggers it fires?
I have an INSERT that will fire a TRIGGER which will UPDATE an attribute on another TABLE. I need to make sure that if the INSERT is successful and the UPDATE inside the TRIGGER isn't then the whole ...
- 123
1
vote
1
answer
2k
views
MS SQL DBCC CHECKDB page ID (1:xxx) contains an incorrect page ID
Fistly, yes I'm aware that there are two options 1) is to restore or 2) run DBCC with allow data loss. But I'd like to understand what exactly is corrupted and what possible data loss could be. From ...
- 111
1
vote
1
answer
688
views
Quorum read consistency in Cassandra
I'm new to distributed databases and there are a couple of things I couldn't figure out.
We're developing an application and need to select the right database,
this application needs strong ...
- 113
5
votes
1
answer
340
views
SQL Server backup versus third party snapshot based backup using VSS
Is it safe to assume that SQL Server instance or database level backup to a NAS share or tape using the SQL Server backup command (internally create .bak file) are transactionally consistent backups?
...
- 51
1
vote
0
answers
757
views
Database consistency errors [closed]
I am using SQL Server 2008 R2 and one of my databases has loads of consistency errors. When I try to query a particular record from a table it displays an error message similar to this
Msg 824, Level ...
- 19
4
votes
2
answers
4k
views
what is an Assert in Database world?
I searched for Assert and found general explanations:
Assertion (software development)
In computer programming, an assertion is a statement that a predicate
(Boolean-valued function, a true–...
- 2,318
1
vote
3
answers
1k
views
Replacing MYSQL table with data from another table - no downtime
I have two tables with identical structures:
Table1 and Table2
id INT PRIMARY KEY,
a TINYINT,
b TINYINT,
c TINYINT
Table1 is actively being used by queries from other sessions; Table2 is not ...
- 11
2
votes
1
answer
76
views
I cannot grasp the definition of serializable
Everybody says that
"Serializable" means that for any given set of concurrent transactions, there is a serial ordering of those transactions which produces the same result.
They also give an ...
8
votes
2
answers
2k
views
What is the difference between integrity and consistency?
Whether I read about CAP or ACID, I see that consistency is referred to ensure the DB integrity constraints. So, I do not understand, why two terms are used to refer the same thing or there is a ...
3
votes
1
answer
669
views
cleaning up failed repair db: path: /var/lib/docker/wesfgr/repair/_tmp_repairDatabase_0
We run MongoDB 3.0.6 with MMAP in Docker container (single node). The filesystem was full and I did a repair with other path.
2016-10-03T08:58:51.186+0200 I INDEX [initandlisten] building ...
- 2,318
5
votes
4
answers
41k
views
DBCC CHECKDB consistency-based I/O error
Running:
DBCC CHECKDB(DatabaseName) with NO_INFOMSGS
gives me the following error:
Msg 824, Level 24, State 2, Line 1
SQL Server detected a logical
consistency-based I/O error: incorrect ...
- 67
8
votes
1
answer
673
views
Data inconsistency prohibition if a table refers to another via two many-to-many relationships
I have following database design (by => foreign key constraints are depicted):
Company[id]
CompanyRealm[id, company_id=>Company.id]many2many
Project[id, company_id=>Company.id]
ProjectRealm[id, ...
- 183
2
votes
2
answers
1k
views
Transaction atomicity implies consistency
Transactions are said to need both atomicity and consistency. But, what is the point of consistency?
Once you ensure that you either update both accounts on money transfer, you are consistent. ...
1
vote
0
answers
46
views
Does the consistency part of the CAP theorem apply to updates and not first-time writes?
Using this definition of the CAP theorem:
Consistency All database clients see the same data, even with concurrent updates.
Availability All database clients are able to access some version of the ...
- 111
10
votes
2
answers
30k
views
Database Implementations of ORDER BY in a Subquery
I am using an application (MapServer - http://mapserver.org/) that wraps SQL statements, so that the ORDER BY statement is in the inner query. E.g.
SELECT * FROM (
SELECT ID, GEOM, Name
...
- 493
1
vote
0
answers
337
views
Pgpool load-balancing vs. consistency
In what ways is consistency weakened when using pgpool-II load-balancing on Postgresql servers running streaming replication?
Obviously with asynchronous replication you can insert a row on the ...
- 111
0
votes
1
answer
386
views
Dealing with Mysql Replication and Functions
In MySQL replication Master-Slave, is there anyway to deal with the functions:
LOAD_FILE()
UUID(), UUID_SHORT()
USER()
FOUND_ROWS()
SYSDATE()
GET_LOCK()
IS_FREE_LOCK()
IS_USED_LOCK()
MASTER_POS_WAIT(...
- 1,979