Thursday, May 5, 2011

Working with SQLite in AIR: Part 12

Today we will learn about transactions.

Usually each command is executed separately, one at a time, and it is suitable for many situations. But sometimes you might need to group more than one commands and execute them as a whole.

When could this be needed? Imagine, that you have two commands, where the first command adds an item to a table, and the second command reads the id of the just added item and adds this id as an item to another table. Yes, you could still do this the old fashioned way - execute the first command first, then execute the second one. But if you do that, there is a chance of an error happening and you need to prepare for unforseen consequences.

For example, say, your first command is successfully executed - the item is inserted into the table. The second command is not successfully executed - gives an error, no id is inserted into the table. Congratulations, your database is now broken.

To prevent this from happening, we can use transactions. This way, we can group more than one commands together, execute them and then see if there are any errors. If there are - we can undo everything we did in this step, if everything's OK - we can proceed to apply the command executions.

To do that, first of all, call the begin() method of your SQLConnection object.

After that, you can execute your SQL commands using this SQLConnection object as usual, one after another.

If any error happens - you can call the method rollback() of your SQLConnection object to undo it all.

If everything's alright - you can call the commit() method of the SQLConnection object to apply your executions.

Now let's take a closer look at that. When you call the begin() method, all the next SQLStatements are executed in the memory - no changes to your database are made (yet), the transaction continues, until you call either rollback() or commit() method. Rollback() cancels all the commands, and no changes are made to the database, commit() applies all the commands, changes are made on the hard drive.

Additionally, when transactions are in process - you block the database from everything else. For example, if you have more than one pieces of code that make changes to the same table - there could be problems if they would try to interact with the database at the same time - some data can be lost. Using transactions, though, guarantees that this will not happen.

The only thing you need to do to start blocking - call the begin() method. While a transaction is in process, all the other processes can only read the database (or can't access it at all). Moreover, you can postpone the blocking of the database until the moment you are really making changes to the database. This depends on the SQLTransactionLockType.

The SQLTransactionLockType is passed as a parameter of the begin() method, possible values are: SQLTransactionLockType.DEFERRED, SQLTransactionLockType.EXCLUSIVE and SQLTransactionLockType.IMMEDIATE.

When DEFERRED is chosen - the database is blocked once the first read or write operation is executed. When it is set to EXCLUSIVE - the database is blocked immediately and does not allow any other processes to write or even read the data. Finally, when the value is IMMEDIATE - the database is immediately blocked and the other processes can't write any data, but they can still read it.

That's all for now. Now we know enough to start building our own AIR application that uses SQLite databases. We'll do that next time.

Thanks for reading!

Related:

Working with SQLite in AIR: Part 1
Working with SQLite in AIR: Part 2
Working with SQLite in AIR: Part 3
Working with SQLite in AIR: Part 4
Working with SQLite in AIR: Part 5
Working with SQLite in AIR: Part 6
Working with SQLite in AIR: Part 7
Working with SQLite in AIR: Part 8
Working with SQLite in AIR: Part 9
Working with SQLite in AIR: Part 10
Working with SQLite in AIR: Part 11

5 comments:

Anonymous said...

Nice blog!!! :D

(found you on Newgrounds :)

Lukasz 'Severiaan' Grela said...

Hi, regarding transactions, worth to mention is the issue with default method params and how it affects it's executing.

http://greladesign.com/blog/2011/01/05/air-and-sqlite-transaction-issues/

willinimmer said...

How can multiple AIR applications access one single SQLite database? (everthing is offline at the desktop)

Curtis J. Morley said...

What about setting up multiple tables in the same db. Can this be done using the begin command? Can you provide code for the above explanation? It is great but I have a hard time visualizing it.

Kirill Poletaev said...

You can set up multiple tables by simply sending multiple SQL queries. You don't need to use the begin command, although it will still work if you do.

You can read my next tutorial series to see how the code is used in practice.

Post a Comment