Hot answers tagged

18

They are not really the same, because of the scope of the data ACID Atomicity Consistency : All Applied Data Changes Provide Consistent View of Data For All DB Connections Isolation Durability CAP Consistency (All Nodes Have Same Data via Eventual Consistency) Availability Partition-Tolerance : system continues to operate despite arbitrary message loss or ...


13

1. Does the trigger follow the relational database's ACID principle? Is there any chance an insert might be committed but the trigger fail? This question is partly answered in a related question you linked to. Trigger code is executed in the same transactional context as the DML statement that caused it to fire, preserving the Atomic part of the ACID ...


8

My simple RAID 10 array running on old hardware with 300GB SAS disks can handle 200-300 inserts per second without any trouble; this is with SQL Server running on a VM, with a lot of other VMs running simultaneously. With just a consumer grade SSD, you can expect 3,000 to 5,000 or more 4K I/Os per second. What exactly is your question?


8

Content of the book The author analyzes the ACID concept from a very theoretical point of view. The essential part might be the first sentence of the last paragraph of the chapter: Overall, then, we conclude that the transaction concept is important more from a pragmatic point of view than it is from a theoretical one. Another important part is his ...


7

If you use InnoDB or a similar storage engine then it should be ACID compliant (ref: https://en.wikipedia.org/wiki/InnoDB). myISAM, the old default and still very commonly used, most definitely isn't ACID compliant. If you mix the two (you might find simpler table types perform better and are acceptable for volatile data that can and will be reproduced again,...


6

Specific issues with the query PostgreSQL defaults to READ COMMITTED isolation, and it looks like you're not using anything different. In READ COMMITTED each statement gets its own snapshot. It cannot see uncommitted changes from other transactions; it's as if they simply haven't happened. Now, imagine you run this simultaneously in three sessions, on a ...


5

Are non-unique indexes / indices covered under the Consistency clause in aCid? Yes. Any violation of that would be considered a bug in PostgreSQL. The docs you quoted are cases where postgres might have to temporarily scan the heap instead of doing an index-only scan or otherwise do extra work to get a consistent result. For example, both BRIN and GIN ...


5

@@ERROR is reset after every statement. The error from your attempt to alter the first procedure is no longer detectable via @@ERROR after you successfully altered the second procedure. Here is an even simpler repro: SELECT 1/0; GO SELECT @@ERROR; -- 8134 However if I put a successful statement in between: SELECT 1/0; GO SELECT 1/1; GO SELECT @@ERROR; -- ...


4

According to Page 418 Paragraph 3 of MySQL 5.0 Certification Study Guide the following commands can break a transaction ALTER TABLE BEGIN CREATE INDEX DROP DATABASE DROP INDEX DROP TABLE RENAME TABLE TRUNCATE TABLE LOCK TABLES UNLOCK TABLES SET AUTOCOMMIT = 1 START TRANSACTION As long as you do not any of these commands inside the transaction, the ...


4

First, this UPDATE is buggy: UPDATE "public"."Users" SET balance=(NEW.amount+currentBalance); because it updates the entire "Users" table, which is obviously not what's wanted. Let's assume that a WHERE clause is added and the code becomes: DECLARE currentBalance INTEGER; BEGIN currentBalance = (SELECT balance FROM "public"."Users" WHERE id=NEW."UserId"...


3

No I don't think permitting Phantom Writes in Repeatable Read satisfies any ACID compliance. See this blog entry for more information.


3

That's the whole idea of MVCC - Multi-Version-Concurrency-Control. Whenever data is being modified while there are other active transactions reading it, or have read it previously (depending on the reader transaction's isolation level), a copy of the pre-modified data is set aside for these transactions so as not to block them. This copy is kept until all ...


3

This This essentially means that if you have a forum with 100 posts per second... you can't handle that with such a setup. Is simply incorrect. What you're missing is that multiple users can enqueue changes in each log flush. So while each log flush takes, say 10ms, it can harden dozens or hundreds of separate, concurrent transactions. Analogy: A ...


3

CAP theorem: specifies that a distributed system can provide two services (ex. Availability and Partition tolerance) but never three. If for example, a service provides Availability and Partitioning it can never ensure Consistency, not immediately, thus Eventual Consistency is used, which allows the infrastructure to flux between inconsistency and ...


3

From a logging point of view 1 and 2 will be about the same as in both cases you are doing all the deletes within a single transaction. There will be lots of locking and probably blocking while the delete is being run. #3 is a single transaction per batch, so users won't be impacted very much and you'll have lots of small transactions in the transaction ...


2

The Consistency part of ACID has to do with the state of data before, during and after a transaction. When it comes to referential integrity, foreign keys should be properly connected, with no keys missing. This means that every operation surrounding cascading rules, triggers, and constraints must be all-or-nothing (go from start to finish, or wind all the ...


2

Yes, it is possible and this may or may not be desirable. The database theory has a concept of isolation, which is about transaction visibility to other processes. MySql's documentation about set transaction has a discussion about product specific syntax.


2

Now what I find difficult is, say while the actual commit process occurs for T1, say T2 reads (while the write by T1 has started but not ended). How is this case handled? It doesn't have to be handled because this step doesn't really exist. You're making the assumption that transactions modify blocks exclusively in memory, until there is a commit, at ...


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 ...


1

serial is defined as such in PostgreSQL documentation: The data types smallserial, serial and bigserial are not true types, but merely a notational convenience for creating unique identifier columns (similar to the AUTO_INCREMENT property supported by some other databases). They also provide this warning: Because smallserial, serial and bigserial are ...


1

It is because an uncommitted transaction by definition did not have all of the logs written out, yet the dirty pages related to the log may have been flushed to disk. Thus, we must undo those changes. In order to undo the changes, we have to redo first.


1

I'm quite sure that the rollback was made by automatic Windows 7 System Restore after the power failure. This restored the mysql data folder. I cannot understand how a OS restore can move files from other applications even becuse I was using a custom path for mysql-data: c:\mysqldata and not the standard c:\programData\Mysql\.... My solution is to disabled ...


1

There is lot of debate on this whether MySQL really and completely follow ACID properties , and everyone have their own opinion . As per MySQL doc https://dev.mysql.com/doc/refman/5.6/en/mysql-acid.html MySQL with Innodb engines closely follow ACID property .But that is their view . But we should not forget that with the newly versions , MySQL has improved a ...


1

Transaction atomicity implies consistency This is not true. A transaction that adds an order for a nonexistent customer is atomic, but makes data inconsistent. To follow your example, a consistent bank transaction must guarantee that the sum of all debits and credits is exactly 0. An atomic transaction must only guarantee that either all necessary tables, ...


1

As Aaron has explained, @@ERROR needs to be checked after each ALTER PROCEDURE. There is no avoiding that, but as for using a transaction, you could replace it with a different approach, if you are open to suggestions on that. You could manipulate the SET NOEXEC setting to achieve the same result you are using a transaction for: either both procedures ...


1

Please provide some sizes. For example, how big would BIG be when it is time to remove the rows? How big are the other tables? MEMORY is not necessarily better than InnoDB. It may be slower because of table vs row locking. It may interfere with overall performance because of taking RAM away from the buffer_pool for BIG, thereby slowing down other things....


1

The image in this article is used quite a bit around the internet in various different forms but it highlights which systems fall in each category of CAP So your are basically asking which horizontally scalable systems don't rely on eventual consistency. To which MongoDB, HBase and many more would be a good answer.


1

Also LOOP if exception handler is out of loop and transaction scope is with in loop even then mysql breaks transaction upon exception, better declare exception handler if any at same scope transaction was declared.


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