And So I realize that autocommit commits every sql statement, but do updates towards the database go straight to the disk or will they stick to cache until flushed?

I recognize it's determined by the database implementation.

Does auto-commit mean a) every statement is really a complete transaction Also it goes right to disk or b) every statement is really a complete transaction also it might have to go to cache where it will likely be flushed later or it might go right to disk

Clarification could be great.

Thanks ahead of time, jbu

Auto-commit only denotes that every statement is within its very own transaction which commits immediately. This really is as opposed to the "normal" mode, in which you must clearly BEGIN a transaction after which COMMIT when you are done (usually after several claims).

The saying "auto-commit" is not related to disk access or caching. Being an implementation detail, most databases will email disk on commit in order to avoid loss of data, but this is not mandatory within the spec.

For ARIES-based methods, carrying out a transaction involves logging all modifications made within that transaction. Changes are flushed immediately to logfile, although not always to datafile (that's determined by the implementation). That's enough to make sure that the alterations could be retrieved in case of failing. So, (b).

Commit provides no guarantee that something continues to be written to disk, that your transaction continues to be completed and also the changes are actually visible with other customers.

Permanent doesn't always mean written to disk (i.e. durable)... Even when a "commit" waits for that transaction to accomplish could be set up with a few databases.

For instance, Oracle 10gR2 has several commit modes, including IMMEDIATE,WAIT,BATCH,NOWAIT. BATCH will queue the buffer the alterations and also the author will write the alterations to disk at some future time. NOWAIT will return immediately regardless of I/O.

The precise behavior of commmit is extremely database specific and may frequently be set up based on your tolerance for loss of data.

It is dependent around the DBMS you are using. For instance, Firebird has it as being a choice in configuration file. Should you turn Forced Creates on, the alterations go straight to the disk. Otherwise they're posted towards the filesystem, and also the actual write time is dependent around the operating-system caching.

When the database transaction is stated to become Acidity, then your D (sturdiness) necessitates that the transaction committed should survive the crash soon after the effective commit. For single server database, which means it's around the disk (disk commit). For many modern multi-server databases, additionally, it may implies that the transaction is distributed to a number of servers (network commit, that are typically considerably faster than disk), underneath the assumption that the prospect of multiple server crash simultaneously is a lot more compact.

You can't really guarantee that commits are atomic, so modern databases use two-phase or three phase commit methods. See Atomic Commit