Creating Table within a Transaction

Sep 28, 2011 at 4:26 PM

I followed heavily example  in ManagedEsent Stock Example.

It demonstrates how to create a table with columns and indexes

               using (var transaction = new Transaction(session))
                    // A newly created table is opened exclusively. This is necessary to add
                    // a primary index to the table (a primary index can only be added if the table
                    // is empty and opened exclusively). Columns and indexes can be added to a 
                    // table which is opened normally.
                    // The other way to create a table is to use JetCreateTableColumnIndex to
                    // add all columns and indexes with one call.
                    JET_TABLEID tableid;
                    Api.JetCreateTable(session, dbid, TableName, 16, 100, out tableid);
                    CreateColumnsAndIndexes(session, tableid);
                    Api.JetCloseTable(session, tableid);

                    // Lazily commit the transaction. Normally committing a transaction forces the
                    // associated log records to be flushed to disk, so the commit has to wait for
                    // the I/O to complete. Using the LazyFlush option means that the log records
                    // are kept in memory and will be flushed later. This will preserve transaction
                    // atomicity (all operations in the transaction will either happen or be rolled
                    // back) but will not preserve durability (a crash after the commit call may
                    // result in the transaction updates being lost). Lazy transaction commits are
                    // considerably faster though, as they don't have to wait for an I/O.
CreateColumnsAndIndexes call has its own transaction. When I tried replicating example 
I found that it all appeared to work, but then table would not exist after I restart the application.
I had to remove the outer transaction forcing table to be created immediately. 
I was able to keep transaction inside CreateColumnsAndIndexes call.
My solution makes sense to me based on my SQL Server experience.
Oct 4, 2011 at 4:43 PM

In your case you probably didn't commit the outer transaction. Transactions don't commit by default.