Talk:ACID

DBMS Only?
ACID is IMHO a property of transactions in general, not necessarily in the context of DBMS. This should at least be mentioned. —Preceding unsigned comment added by 193.135.254.161 (talk) 09:19, 14 January 2010 (UTC)

Proposal
I propose that the ACID article, being a disambiguation page, should mention both acid as in acidic chemicals, and also maybe mention that it is a nickname of LSD.

These are all fairly common uses of the word ACID, and thus should be reflected in the article.

I think that the above comment is outdated (as there is an "acid" and not "ACID" disambugation page). Someone please confirm this.

you mean "Acid", this one is about chemicals I think everything's ok as it is..

I agree with the last statement, leave it as it is. When you see uppercase letters in a dictionary or encyclopedia, it means it is an acronym and it stands for something else. I think it is in the correct context.


 * I agree. A disambiguation page should not contain detailed info on one definition.Most of the contents of this page should be moved to ACID (database).
 * &mdash;gorgan_almighty 13:24, 8 December 2005 (UTC)


 * This is not the disambiguation page. acid and ACID are two completely different articles.  Wikipedia is case sensitive.  If a user types ACID in all caps, it is most likely that the user is looking for this page. --  k a i n a w &trade; 13:24, 14 January 2010 (UTC)

Consistency
If I remember correctly the "Consistency" in ACID refers to the fact that the Database must ensure that the order of the queries must be consistent: The article mentions consistency as "beign compliant with the constraints of the DB". Of course it is!. Constraints of the DB are unrelated with transactions! And of course transactions could not break them!
 * Imagine a transacction A starts on time T
 * Another unrelated query B starts at T+1
 * If the unrelated query B tries to access some of the values beign modified by the A transaction, the database will wait till the transaction to finish, and give the data modified to the B query. If fact, modern databases will run the B query, wait for A, if A affects data readed by B, rollback B and repeat that query again.
 * The result is that if B goes after A, the operations will be always the same, no mather if B was in T+1 when the trasaction A was still running or T+200. The virtual time for all the modifications that the transaction A makes is T, so any T+1 query will see that modifications in place.


 * That is not true. Consistency has nothing to do with the order of transactions.  It has to do with the consistency of the database.  A "consistent state" is a state in which the rules of the database, such as field values, foreign keys, and the like, are all met.  So, if I interleave two transactions and have them run at the same time, the end result is consistent if and only if the database is in a consistent state when each transaction commits. It is not required that it be in an identical state that would result from running one transaction before the other transaction. --  k a i n a w &trade; 20:45, 26 February 2009 (UTC)


 * I believe this part can be dropped, the person making the first comment did not quite know what he was talking about in the first place. Transactions are of course related to constraints and of course transactions can break constraints (in some DBMS's) and that is why ACID is not guaranteed in every case, and that is why it was defined and required to be enforced. Yourbane (talk) 11:45, 19 August 2011 (UTC)

Allocated CPU time
"... it may have used up its allocated CPU time." - This doesn't seem to make too much sense. CPU time is not allocated, it's used on demand. - intgr 05:09, 18 September 2005 (UTC)


 * You're assuming a computer/OS can't be designed in a way that does allocate time in that way. You can imagine a company building a renderfarm and rents out computer time that would take advantage of something like that. For a real world example, the wolfram integrator (http://integrals.wolfram.com/index.jsp) is designed to timeout if it performs an integral that takes too long. Just replace the integrator with something that uses transactions and it makes sense. TomJF 04:58, 25 December 2005 (UTC)

If ACID is a property of transactions in the face of concurrency, then Consistency needs to be defined in that context. In particular, offering a transaction a consistent view of the database means protecting the transaction from phantom reads. If a transaction reads data, and then reads the same data again, it should be given the same result as before, barring any updates by the transaction itself. A violation of this law means that the DBMS is giving the transaction a time varying view of the data, and that's not consistent with itself.

This has almost nothing to do with conformance to the constraints defined for the database. The word "consistency" might also be used in that context, but that is not what consistency refers to in the context of ACID transactions. Indeed, ACID transactions can be defined for non-relational systems that employ transactions, and conformance to constraints could be totally different in non-relational systems.

Joe Thursday (talk) 11:11, 1 October 2010 (UTC)


 * Correct me if I'm wrong but this is another 'Implementation' discussion, which I believe has no place in a principle /axiom article, cleanup ? Yourbane (talk) 11:44, 19 August 2011 (UTC)

Disambig
I subst'd the disambig link and changed it to point directly to the disambig. Figured I should mention it here. - Corbin Simpson 18:02, 29 January 2006 (UTC)

ACID -> AID? ACD?
Hi all, I recall reading somewhere that one of the properties (atomic, consistent, isolated, durable) was implied by the others, something like "if a transaction is atomic and consistent then it must have had the appearance of being isolated". Or perhaps if it's A and I then it must appear C. Summat like that anyway, but I never found that quote again, nor anyone saying anything similar. Can anyone clarify? ta - jan Water pepper (talk) 22:54, 22 April 2008 (UTC)


 * They do not imply one another. For example, I can have a transaction that is atomic.  That means that once it starts, it must finish or all the work must be undone.  It can be consistent.  That means that the database must be consistent when the transaction commits.  However, it may not be isolated.  The system may be messy and allow other transactions to access the data that my transaction is working on - changing it midway through.  Here's an example: I want to read A, then read B, and then write the sum to C.  So, it is atomic.  Once I start reading A, I will guarantee that I read B and put the sum it C or I'll undo anything I've done.  It is also consistent.  In the end, C will have a number it in and that meets the requirements of the database.  Now, I read A and it is 5.  Then, I go to read B (which has a 2), but someone sneaks in real quick and changes it to 3.  So, I end up reading a 3.  I put the sum, 8, in C.  For the record, it is durable.  After I commit, that 8 stays in C.  You can see that it met ACD, but did not meet I.  A similar example is possible for every other property. --  k a i n a w &trade; 20:51, 26 February 2009 (UTC)


 * Doesn't make sense, sorry. 'Atomic' means a transaction logically happens in one step (or not at all). If it was atomic but other transactions could 'change it midway through' then it's not atomic because 'midway through' implies that the supposedly atomic action could be decomposed into parts between which some outside interference could occur. Even if this interpretation was wrong, the 'Isolated' part should explicity ensure such interference doesn't happen, hence implying Consistent.
 * I believe Durability is irrelevant here; it's a requirement tangential to the rest hence let's ignore it.
 * So I don't buy it. It seems to me AI->C or AC->I and there's a whiff of redundancy. Thinking about it I'm not sure exactly what some of the terms are - Atomic I'm pretty sure about, but Consistent? consistent with what? Isolated? from other transactions but in what sense is this isolation above and beyond that apparently ensure by Atomic alone? I am more sure now of redundancy, but even less sure of what each component of ACI(D) means.
 * One day I'll have time to read Gray & Reuter or Vossen & Weikum, until then, thanks for your feedback
 * Water pepper (talk) 09:13, 2 March 2009 (UTC)


 * And today's world prize for stupidity goes to me. What do the terms mean? Well, I'll find them in the wiki page which I'm commenting on.
 * Consistency refers to a transaction not violating a DBs constraints. So It's irrelevant to this discussion, leaving only the question of how A & I relate, and whether one implies the other. I now think that when Isolation is defined as "Isolation: Even though transactions execute concurrently, it appers to each transaction, T, that others executed either before T or after T, but not both" - (Gray & Reuter, inside back cover, clearer IMO than the wiki page) I must assume that I implies A at least, and possibly the converse (though less sure).
 * Water pepper (talk) 09:43, 2 March 2009 (UTC)


 * You are correct that atomic means that all the operations must go through or none at all. It does not, however, refer to the data.  It only refers to the operations.  So, if a transaction is "read the value of A; write the value of A to B;", it only means that both operations will take place.  It does not imply that nothing will occur from another transaction between the read and write functions.  You are making a very common assumption that atomicity implies mutual exclusion - mainly because 90% of the time we combine atomicity with mutual exclusion.  In this case, we only mean atomicity.  In being atomic, this function does not exclude any other transaction from reading or writing to A and B while this transaction is operating.  As far as isolation goes, when this transaction commits, the result of B should be the same as if this transaction ran without any other transactions running.  So, it is completely different than atomicity. --  k a i n a w &trade; 22:33, 3 March 2009 (UTC)


 * kainaw, that is a very, very odd interpretation of Atomic, and one I am having trouble buying into. I don't see how one can sustain a view that database actions can be divorced from the data they operate on. The wiki page sez "Atomicity states that database #modifications# must follow an 'all or nothing' rule" - (my ## emphasis). Not #actions# but #modifications#, which imply actions+data. Gray & Reuter's definition of Atomic is "Atomicity: A transaction's changes to the state are atomic: either all happen or none happen. These changes include database changes, messages, and actions on transducers" (Gray & Reuter, inside back cover). It say "changes to the state" which implies data + actions. I don't believe you can separate the two. Anyway, regardless of that I still feel that my core point above, that Isolated implies Atomic, still stands (until someone shows otherwise).Water pepper (talk) 14:54, 4 March 2009 (UTC)


 * Please ask this on the computer science section of WP:RD. I can repeat over and over that in transaction management, atomic means nothing more than "all or nothing".  It means other things in other areas of computer science, but not in transaction management.  Perhaps if you get the same answer repeated by others, you will see that you are reading too much into the definitions you quoted.  They do not claim that data is isolated for each transaction.  The only claim that any modification/state change that is made implies that all modifications/state changes will be made. --  k a i n a w &trade; 20:02, 5 March 2009 (UTC)


 * See the line near the end of the article that explains 2PL is normally used to guarantee isolation. It does not say atomicity.  That is because atomicity has to do with the functions.  Isolation has to do with the data. Youth in Asia (talk) 14:06, 6 March 2009 (UTC)


 * 2PC is for distributed transactions. I'm talking about the simpler sort for the moment, the non-distributed bog-standard transaction. And while you're here, can you explain with reference to the above definition by Gray & Reuter of Atomic, which talks explicitly about state changes (ie. *data* changes), how you can claim that atomicity is only about functions (by which I presume you mean operations). Anyway how can you refer to all-or-nothing functions/operations without considering their changeing of some state - after all, without state changes how can the effect of any operation be other than null, always? And if a set of actions A is all-or-nothing on a state but that state is potentially being mutated unpredictably by other processes as A runs then what guarantees can A possibly make? None AFAICS.  —Preceding unsigned comment added by 78.151.135.219 (talk) 15:47, 6 March 2009 (UTC)


 * The quote you gave means exactly what it says. Altering the state means that something has changed in the database, for better or worse.  It does not imply that the state change was intended or even that it the database will remain consistent.  You are assuming correctness and consistency when all that is said is state change. Youth in Asia (talk) 16:45, 6 March 2009 (UTC)


 * (Please see bottom of this discussion thread)Water pepper (talk) 14:57, 12 March 2009 (UTC)

Ah! foolish me! I do believe I've been trolled and totally had! Well the question of whether I -> A  will have to wait for another day and someone knowledgeable. Thanks for the diversion, fruitless though it was. Water pepper (talk) 19:12, 6 March 2009 (UTC)


 * Note that Youth mentioned 2PL. You replied with a comment about 2PC.  2PL is not 2PC.  2PL is an abbreviation for two phase locking.  2PL is used extensively on centralized databases.  In fact, it is used in pretty much every database engine currently being used - especially ones that run on one server in a completely non-distributed environment.  The point of 2PL is to ensure isolation because 2PL guarantees serializability.  In other words, if you use 2PL, you are guaranteed that the result of multiple transactions running at the same time will be the same as at least one serialized ordering of those transactions - which is the definition of isolation.  Youth was pointing out that the article clearly stated that 2PL was used to guarantee isolation, not atomicity.  You replied by thoroughly misunderstanding his statement and then accusing him (and me?) of being a troll and lacking knowledge.  I would be very amazed if anyone will take the time to try and explain this to you further as you have shown how you will respond. --  k a i n a w &trade; 22:38, 6 March 2009 (UTC)


 * Yup, my mistake. Then again had he used the phrase that appears in the page rather than the abbreviation, which doesn't, I might not have got confused. But that's just a triviality; I did misunderstand. I recognise what you call 2PL as Simultaneous Lock Acquisition (let's call it SLA) and it most certainly isn't used "in pretty much every database engine currently being used". It isn't used in in MS SQL Server 6.5/7/2000 and I'd be very surprised if it was used in oracle (though I don't know). Basically, full isolation for transactions is done by acquiring locks as they progress and in consequence they deadlock occasionally. Deadlock is detected by scanning the lock WaitFor graph for cycles then aborting & rolling back a transaction. You can, and I have, sometimes force SLA by manual hints but it's rare and only applicable in very simple or special cases. I really, really doubt it happens in general (or is even possible) and I'd question that claim on the wiki page. But back to my original question; now you've established a mechanism for Isolation, does not this isolation imply Atomicity? Because if you have I then apparently you have (the effect of) A, so I->A and so ACID -> CID (and there's still my question as to whether A->I also). Because that's what I've been at all along. But feel free not to answer because by past standards I'm not going to get too much except diversions, and yes, I'm afraid you do appear to be lacking knowledge. —Preceding unsigned comment added by 78.151.158.196 (talk) 00:30, 7 March 2009 (UTC)


 * (paragraph removed)Water pepper (talk) 14:57, 12 March 2009 (UTC)


 * Kainaw responded privately and in detail. It is clear that he knows the subject in much greater depth than I do, or than I gave him credit for. I take back everything I said here that stated or implied otherwise.Water pepper (talk) 14:57, 12 March 2009 (UTC)


 * That's well and good, but it would have been nice for either one of you to share the highlights of this private conversation, as the comments here do not provide a convincing case that any one of the components of ACID do not imply the other. I am not saying that any one component does imply the other, only that the discussion here does a poor job of defending the claim that there is no implication, and such an explanation is necessary since the above claim that there is an implication does sound plausible without closer inspection. I will provide some examples. I apologize that I'm not used to this doing this on Wikepedia and so the following is just a series of semi-colon delimited actions. A does not imply I: Assume transactions T1 and T2 are both attempting the same operation of reading M, writing M+1, reading N, writing N+5, where M equals 3 and N equals 10 before executing T1 and T2, and that our implementation guarantees A but not I; normally we expect that executing both transactions should result in a net M+2=5 and N+10=20; if A and not I, then we do not even need to pay attention to what is happening to M and we can prove I to be violated by watching only N (M is used below for other logic), and we could have the following use case (T1 reads N (10), T2 reads N (10) (violates I but not A), T1 adds - 10+5 = 15, T2 adds - 10+5 = 15 (violates I but not A), T1 writes N=15, T2 writes N=15 (does not violate A); both transactions commit successfully; the fact that we assert that the implementation enforces A is irrelevant in this specific use case since both transactions did complete successfully, but we assert it nonetheless, and indeed each transaction Tn was atomic; I was violated. A does not imply I. Next, I does not imply A: assume the same scenario we used for A does not imply I, except that we are using an implementation which guarantees I but does not guarantee A this time; if I and not A, then we could have the following use case (T1 reads M (3), T2 tries to read M but cannot because that would violate I so instead it waits, T1 adds to M - 3+1 = 4, T1 writes M = 4 successfully, T1 reads N but fails, T1 aborts but M=4 persists since we have not guaranteed A, now T2 is allowed to proceed and reads M (4), adds M - 4+1=5, writes M=5 successfully, reads N (10, because T1 did not successfully add to N), adds N - 10+5=15, writes N=15 successfully. We are left with a state of M=5, N=15 instead of M=5, N=20, and we don't have the M=4, N=15 we would normally expect if 1 of the transactions succeeded and the other failed. I was successfully guaranteed but A was not, therefore I does not imply A. Next, C and I does not imply A - for this one assume same situation as before except we also have a value X which equals 25 before T1 and T2 and there is a rule that M+N<X, and our implementation guarantees I and C but not A: I refer back to the same proof for "I does not imply A" and, from that use case we can trivially see that I is enforced by the same logic; and since M+N = 20, 20 < 25, C is also enforced (the transaction is not aborted because of C, so our C guarantee is upheld); and again referring back to "I does not imply A" we can see that, by the same logic, A is not enforced in this situation; therefore, I and C were enforced but A was not, therefore, I and C does not imply A. Next, A and C does not imply I; assume the same initial schema, values, and transaction actions as above, except this time our implementation guarantees A and C but not I; we have the following use case, (T1 reads M (3), T2 reads M (3) [this is allowed because I is not guaranteed], T1 adds - 3+1=4, T2 adds - 3+1=4, T1 writes M=4, T2 writes M=4, T1 reads N (10), T1 adds - 10+5=15, T1 writes N=15 successfully, T2 reads N (15), T2 adds - 15+5=20, T2 writes N=20 successfully; on every successful write the rule M+NI, I-/->A, IC-/->A, AC-/->I. This is getting long enough and taking long enough that I will forego discussing D. -Aaron  67.246.97.194 (talk) 15:42, 24 August 2016 (UTC)


 * Same question as for other parts, can we delete/drop/summarize some of the old discussions here in order to make the talk page more usable ? Yourbane (talk) 11:42, 19 August 2011 (UTC)

Databases That Conform To ACID Principles?
Should there be a list near the end of the article of products that conform? —Preceding unsigned comment added by 218.215.25.240 (talk) 06:16, 25 October 2008 (UTC)


 * The list would be much longer than the article itself. It would be rather pointless. --  k a i n a w &trade; 20:47, 26 February 2009 (UTC)


 * I doubt this. There are few databases out there that are truly ACID compliant, and even less that have any significant presence in the RDBMS space.  In fact I think the list would consist entirely of Oracle, MSSQL, PostGreSQL, and Sybase.  PostGreSQL would be debatable on the presence/penetration side as well.  Many databases claim to be ACID compliant but in fact are not, such as MySQL. — Preceding unsigned comment added by 173.9.86.65 (talk)


 * Honestly, that barely scratches the surface of ACID databases; off the top of my head I can recall Berkeley DB, SQLite, Ingres, Firebird. PostgreSQL is no small player anymore. And yes, MySQL's InnoDB storage engine is ACID; what sources do you have to claim otherwise? -- intgr [talk] 08:09, 18 September 2009 (UTC)


 * MySQL is a funny one. People who want to claim it is a tiny petty little thing ONLY consider the non-InnoDB version to justify their claims.  Then, when you point out that the InooDB version of MySQL is ACID compliant, has foreign keys, and all that stuff, they say "Oh, I wasn't talking about that." --  k a i n a w &trade; 13:17, 18 September 2009 (UTC)


 * It's much funnier than that, InnoDB IS NOT ACID COMPLIANT... and anyone pretending the opposite neads to either learn more about how InnoDB handles triggers/ keys / cascades or learn what exactly is ACID. Yourbane (talk) 11:41, 19 August 2011 (UTC)


 * If that's true, fix the InnoDB wikipedia page (or at least bring it up on the talk page there). 67.207.113.90 (talk) 20:35, 16 May 2012 (UTC)


 * Just curious whether recent document/JSON no-SQL databases like MongoDB and CouchDB are ACID. David Spector (talk) 11:34, 14 August 2018 (UTC)

Network environment
I have removed the following sentence altogether:

''It is difficult to guarantee ACID properties in a network environment. Network connections might fail, or two users might want to use the same part of the database at the same time.''

It is definitely a complex matter to implement transactions in a DBMS. If database queries are arriving via a network, or via applications running on the same computer, makes little fundamental difference. I wonder if the original author was thinking of distributed or replicated data. The following sentence mentioned two phase commit would suggest that. If so, the sentence should talk about distributed data, not a vague "network environment". If my assumption is correct, it would be better to expand the discussion of two phase commit a little rather than bring back the sentence I've deleted.

Paul Foxworthy (talk) 03:54, 27 September 2009 (UTC)


 * Correct. I wrote that after working on information for federated databases.  By "network environment", I am not referring to a single database with network capabilities.  I am referring to a "database" spread over a network in multiple repositories. --  k a i n a w &trade; 11:45, 27 September 2009 (UTC)


 * I re-added and hopefully clarified the statement so that it's not ambiguous anymore. It's an quick introduction to distributed transactions so I personally find it appropriate. Does this sound good to you? -- intgr [talk] 12:51, 28 September 2009 (UTC)


 * Two phase commit is not sufhichent to solve this problime. See Two_Generals'_Problem and Byzantine_fault_tolerance. It is my understanding the the only way for more then one node to agree on a state is the Paxos_algorithm thou there are ways even in a distributed environment use a single nodes to get simmeler effects. For instance you could put transaction numbers on all your records keeping one copy of the record for each transaction then have a single node that tracks the current transaction. In this way you can atomically update the data on all nodes ( if operations are ordered correctly ) with out the need for distrubeted consensus. Sorta MVCC for clusters. ( Sorry about the spelling. Spell check in google chorme sucks and so dose my spelling.)Wikiwikimoore (talk) 22:03, 11 January 2010 (UTC)


 * You are drifting from distributed databases to distributed database administration. Two-phase commit handles the problem of having one transaction commit while another is attempting to commit and causing a problem.  It has nothing to do with how the administration is structured.  It could be a single centralized administration that gives permission to commit to one transaction at a time.  If you go into distributed administration, then you need to figure out how to manage who can commit and when.  Quorum is a simple solution to the problem which is rather fault tolerant - and it even handles the Byzantine problem if the consensus is higher than 1. --  k a i n a w &trade; 05:24, 12 January 2010 (UTC)


 * Would it not be reasonable to drop all information related to "two-phase commit" as it's merely one solution enabling ACID, rather than a principle inherent to ACID ? Looks to me as specialized as the information on 'this dbms' or 'that dbms' or WAL or ... Implementations are interesting but overall irrelevant to the ACID principles. Yourbane (talk) 11:39, 19 August 2011 (UTC)

Relaxing the isolation property?
"One implementation relaxes the isolation property..." Does it really? From reading the page on snapshot isolation, it sounds as if the isolation requirement is not loosened at all. Snapshot isolation is just a means of implementing isolation without forcing total serialization. —Preceding unsigned comment added by 24.238.213.117 (talk) 19:26, 3 June 2010 (UTC)

Integrity constraint enforcement
The article states that: "In the previous example, one rule was a requirement that A + B = 100; most database systems would not allow such a rule to be specified, and so would have no responsibility to enforce it." This is not true. While MySQL cannot enforce check constraints, PostgreSQL, Oracle Database, and Microsoft SQL Server all can. Under PostgreSQL, with the following table definition: attempting to insert a row in violation of the A + B = 100 constraint results in an error:

INSERT INTO acidtest VALUES (10, 10); ERROR: new row for relation "acidtest" violates check constraint "sum"

What should be done with this statement in the article? D RAGON 280 ( T ALK / C ONTRIBS ) 12:43, 30 March 2011 (UTC)


 * I suggest going back to a much earlier version that explains how that validation rule is either met or the update is reverted. Right now, someone has decided that it is more important to express limited knowledge of databases than to explain the concepts of ACID. -- k a i n a w &trade; 12:49, 30 March 2011 (UTC)
 * I've restored the content in "Consistency failure" before [ this edit]. I'll clean it up later.  D RAGON 280  ( T ALK / C ONTRIBS ) 17:32, 30 March 2011 (UTC)
 * Even SQLite honors the check constraint:

D RAGON 280 ( T ALK / C ONTRIBS ) 17:59, 30 March 2011 (UTC)

I believe this is definitely better, we're talking ACID, not 'whatever dbms you wanna talk about' and that's clear, is it possible to remove old discussions on which consensus has clearly been reached ? or move them to a history section ? Yourbane (talk) 11:36, 19 August 2011 (UTC)

Write ahead logging
The term write ahead logging is used, but pre image logging is described. WAL writes the new (changed) data to a logfile which is copied to the database after the last previous read transaction ends. — Preceding unsigned comment added by 212.152.157.50 (talk) 07:43, 25 January 2013 (UTC)

Error in example?
The main page gives this example: and says that if 10 is subtracted from A, then at that point the check constraint is false. As there is no comma in the example before the word CHECK, the constraint is a column constraint, and I don't believe it's legal to reference another column. (The example doesn't reference any SQL standard.) If a comma is inserted, it becomes a table constraint, and therefore any attempt to change A without also changing B would fail. Therefore, I don't think the example illustrates what it was intended to. To my knowledge, check constraints are always in force (unless something is done to turn them off), and I don't think transaction management of any kind is required to help them do their job. Rochkind (talk) 16:16, 9 May 2013 (UTC)

See Also suggestion
Seems like this tree is a bush--an article about 'transactions' could also reference 'structure'. Both ACID and Database normalization seem to be on par meta-concepts. Xtian~enwiki (talk) 19:04, 14 April 2020 (UTC)