START TRANSACTION; INSERT INTO Tab1 ( Num ) VALUES ( 5 ); UPDATE Tab1 SET Num = 6 WHERE Num = 5; DELETE FROM Tab1 WHERE Num = 6; COMMIT;
When a transaction occurs, we want to record statement of that transaction as quickly as possible. 1) We write to files that are called "Write Ahead Log" (WAL). These files are just temporary notes. 2) We write down only essential data. 3) We write sequentially onto disk. That is faster. 4) We buffer these notes in memory and we write them to disk in batches. 5) We do not recalculate indexes of any table.
The WAL usually stores operations, not the final table pages. It doesn't store any data. WAL files are binary files.
UPDATE Products SET Color = 'Red' WHERE ID = 1007
If we are afraid that we will forgot something from our memory, we will write it down. That is the idea behind WAL files.
"The best memory is not as good as a bad pen."
WALs are used when the system becomes corrupted and crash? After we fix the system, we can read from the WAL about: a) Transactions that are complete and are not written to a table. We can write them after the system is repaired, from the WAL notes. This is how we provide durability. These transactions will not be lost. b) Transactions that are incomplete in the WAL will be discarded. We don't want incomplete transactions.
Normal tables
For normal tables, we will create WAL record the fastest we can.
When the database is ready to write to a table, then it will transfer changes from the RAM into database.
After we successfully recorded our transaction, we can delete our WAL file, we don't need it any more.
Unlogged Tables
Unlogged Tables are just like normal database tables, except they are not using WAL. Transaction exists only in RAM memory, until it is written to a table.
Unlogged tables are almost like normal tables: 1) They are written to disk. 2) After normal shutdown of a system, content of unlogged tables will be preserved ( not in MonetDB, but MonetDB is exception ). 3) Content of these tables is available to all of the users, just like for normal tables.
The only difference is in the case of the system crash. After the crash, content of normal tables will be restored to a consistent state by using WAL. Unlogged table will be truncated during the recovery. Server can not guarantee consistency of unlogged tables without WAL, so it will delete their content. This is why unlogged tables should be used only for temporary and re-creatable data.
Writing to unlogged tables can be several times faster then writing to normal tables. Without WAL, we can write much faster, because we are writing only to RAM memory. We are sacrificing reliability of unlogged tables for better write performance.
Unlogged Tables in MonetDB
I will login as administrator. I will change current schema to "voc".
This is how we create unlogged table in MonetDB. CREATE UNLOGGED TABLE UnLogTab ( Number INTEGER ); INSERT INTO UnLogTab ( Number ) VALUES ( 1 ); SELECT * FROM UnLogTab;
If we quit our session, and we log in again, we will still be able to use our unlogged table. quit mclient -u monetdb -d voc SELECT * FROM voc.UnLogTab;
Both the data and the table structure will be preserved. Unlogged table can last through several sessions.
I will create another session as a "voc" user.
mclient -u voc -d voc –password voc
This other session will also be able to read and modify UnLogTab table. UPDATE UnLogTab SET Number = 2; SELECT * FROM UnLogTab;
Sample Table
I will create one normal sample table in the "voc" schema, from the admin session: CREATE TABLE voc.Tab1 ( Letter VARCHAR(10) ); INSERT INTO voc.Tab1 VALUES ( 'A' ); SELECT * FROM voc.Tab1;
What Happens After the Crash?
I will exit both sessions from above. We are now in the shell. The number of mserver5 process is 1335. pgrep mserver5 --process 1335 I will kill this process. kill -9 1335
We have now violently closed our server.
I will again start my "voc" database and I will log in as administrator. monetdb start voc mclient -u monetdb -d voc
If we now read from our table, our table will be empty. Because of crash, MonetDB truncated this table.
SELECT * FROM voc.UnLogTab;
But, if we read from the sample table, everything will be fine: SELECT * FROM voc.Tab1;
Difference Between MonetDB and Some Other Servers
I will again add some data to UnLogTab: INSERT INTO voc.UnLogTab ( Number ) VALUES ( 1 ); SELECT * FROM voc.UnLogTab;
This time I will restart my server gracefully. quit monetdb stop voc monetdb start voc mclient -u monetdb -d voc --password monetdb
Our UnLogTab will be empty. This is opposite from some other servers, where regular restart doesn't remove the content of the unlogged tables. In MonetDB, the content will be lost. SELECT * FROM voc.UnLogTab;
ALTER TABLE Tab1 SET UNLOGGED; ALTER TABLE UnLogTab SET LOGGED;
Unlike some other databases, MonetDB doesn't have ability to transform unlogged tables to logged tables and vice versa. These statements will not work.
System Tables
We can find information about unlogged tables in the sys.tables and sys.statistics.
SELECT * FROM sys.tables WHERE name = 'unlogtab';
SELECT * FROM sys.statistics WHERE table = 'unlogtab';
In the system table sys.table_types we can see that unlogged tables are of the type 7.
