Google I/O 2010 About SQLite Transactions

Question

Since this is a very rich conference I'm watching it once again, and at minute 24:17 I noticed Virgil said

Also, use transactions when you use SQLite, not only will they preserve the data integrity, but they will increase the performance of your database operations

EDIT: What exactly does he means with "use transactions", does he means to tell us to use BEGIN TRANSACTION statement, or is he referred to something else?

If the first one is it then:

1. does that mean we should use SQLiteDatabase#rawQuery() method to write raw SQL statements instead of the provided SQLiteDatabase#query() method?

2. what is the difference between it and using a SELECT statement and a TRANSACTION statement?

Answer 1

Simple example to explain you the need for database transactions and use of prepared statements etc.

While inserting large number of records i.e thousands of records or so, we run into the problem of “insert speed”. The usual insert command in Android is slow, so we can use a transaction and prepared statement.

In our case, we use INSERT OR REPLACE INTO on the insert query since we want to update a row if it already exists, based on the trigger (INDEX) created.

If you’re using INSERT OR REPLACE INTO command, you have to create a trigger. This SQL trigger is executed after the table has been created (see DatabaseHandler.java below)

Another important factor in speeding up your insert is using prepared statements.

You can find the example below :

MainActivity.java – contains the AsyncTask that will be executed to insert larger number of data to db when the user clicks a button.

public class MainActivity extends Activity {


final String TAG = "MainActivity.java";
EditText editTextRecordNum;
TextView tvStatus;

@Override
protected void onCreate(Bundle savedInstanceState) {

    super.onCreate(savedInstanceState);
    setContentView(R.layout.activity_main);

    View.OnClickListener handler = new View.OnClickListener() {
        public void onClick(View v) {

            switch (v.getId()) {

            case R.id.buttonNormalInsert:
                new AsyncInsertData("normal").execute();
                break;
            case R.id.buttonFastInsert:
                new AsyncInsertData("fast").execute();
                break;
            }
        }
    };

    // EditText for entering desired number of records to be inserted
    editTextRecordNum = (EditText) findViewById(R.id.editTextRecordNum);

    // Button for normal and fast insert
    findViewById(R.id.buttonNormalInsert).setOnClickListener(handler);
    findViewById(R.id.buttonFastInsert).setOnClickListener(handler);

    // status TextView
    tvStatus = (TextView) findViewById(R.id.textViewStatus);

}

// we used AsyncTask so it won't block the UI thread during inserts.
class AsyncInsertData extends AsyncTask<String, String, String> {

    DatabaseHandler databaseHandler;
    String type;
    long timeElapsed;

    protected AsyncInsertData(String type){
        this.type  = type;
        this.databaseHandler = new DatabaseHandler(MainActivity.this);
    }

    // @type - can be 'normal' or 'fast'
    @Override
    protected void onPreExecute() {
        super.onPreExecute();
        tvStatus.setText("Inserting " + editTextRecordNum.getText() + " records...");
    }

    @Override
    protected String doInBackground(String... aurl) {

        try {

            // get number of records to be inserted
            int insertCount = Integer.parseInt(editTextRecordNum.getText().toString());

            // empty the table
            databaseHandler.deleteRecords();

            // keep track of execution time
            long lStartTime = System.nanoTime();

            if (type.equals("normal")) {
                databaseHandler.insertNormal(insertCount);
            } else {
                databaseHandler.insertFast(insertCount);
            }

            // execution finised
            long lEndTime = System.nanoTime();

            // display execution time
            timeElapsed = lEndTime - lStartTime;

        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    protected void onPostExecute(String unused) {
        tvStatus.setText("Done inserting " + databaseHandler.countRecords() + " records. Time elapsed: " + timeElapsed / 1000000 + " ms."); 
    }

}

}

DatabaseHandler.java – handles the database operations such as table creation, emptying the database, counting database records and the inserting data using a loop.

public class DatabaseHandler extends SQLiteOpenHelper {


// for our logs
public static final String TAG = "DatabaseHandler.java";

// database version
private static final int DATABASE_VERSION = 7;

// database name
protected static final String DATABASE_NAME = "DatabaseName";

// table details
public String tableName = "locations";
public String fieldObjectId = "id";
public String fieldObjectName = "name";
public String fieldObjectDescription = "description";

// constructor
public DatabaseHandler(Context context) {
    super(context, DATABASE_NAME, null, DATABASE_VERSION);
}

// creating table
@Override
public void onCreate(SQLiteDatabase db) {

    String sql = "";

    sql += "CREATE TABLE " + tableName;
    sql += " ( ";
    sql += fieldObjectId + " INTEGER PRIMARY KEY AUTOINCREMENT, ";
    sql += fieldObjectName + " TEXT, ";
    sql += fieldObjectDescription + " TEXT ";
    sql += " ) ";

    db.execSQL(sql);

    // create the index for our INSERT OR REPLACE INTO statement.
    // this acts as the WHERE name="name input" AND description="description input"
    // if that WHERE clause is true, I mean, it finds the same name and description in the database,
    // it will be REPLACEd. 
    // ELSE, what's in the database will remain and the input will be INSERTed (new record)
    String INDEX = "CREATE UNIQUE INDEX locations_index ON "
                    + tableName + " (name, description)";

    db.execSQL(INDEX);
}


// When upgrading the database, it will drop the current table and recreate.
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

    String sql = "DROP TABLE IF EXISTS " + tableName;
    db.execSQL(sql);

    onCreate(db);
}

// insert data using transaction and prepared statement
public void insertFast(int insertCount) {

    // you can use INSERT only
    String sql = "INSERT OR REPLACE INTO " + tableName + " ( name, description ) VALUES ( ?, ? )";

    SQLiteDatabase db = this.getWritableDatabase();

    /*
     * According to the docs http://developer.android.com/reference/android/database/sqlite/SQLiteDatabase.html
     * Writers should use beginTransactionNonExclusive() or beginTransactionWithListenerNonExclusive(SQLiteTransactionListener) 
     * to start a transaction. Non-exclusive mode allows database file to be in readable by other threads executing queries.
     */
    db.beginTransactionNonExclusive();
    // db.beginTransaction();

    SQLiteStatement stmt = db.compileStatement(sql);

    for(int x=1; x<=insertCount; x++){

        stmt.bindString(1, "Name # " + x);
        stmt.bindString(2, "Description # " + x);

        stmt.execute();
        stmt.clearBindings();

    }

    db.setTransactionSuccessful();
    db.endTransaction();

    db.close();
}

// inserts the record without using transaction and prepare statement
public void insertNormal(int insertCount){
    try{

        SQLiteDatabase db = this.getWritableDatabase();

        for(int x=1; x<=insertCount; x++){

            ContentValues values = new ContentValues();
            values.put(fieldObjectName, "Name # " + x);
            values.put(fieldObjectDescription, "Description # " + x);

            db.insert(tableName, null, values);

        }

        db.close();

    }catch(Exception e){
        e.printStackTrace();
    } 
}

// deletes all records
public void deleteRecords(){

    SQLiteDatabase db = this.getWritableDatabase();
    db.execSQL("delete from "+ tableName);
    db.close();
}

// count records
public int countRecords(){

    SQLiteDatabase db = this.getWritableDatabase();

    Cursor cursor = db.rawQuery("SELECT count(*) from " + tableName, null);
    cursor.moveToFirst();

    int recCount = cursor.getInt(0);

    cursor.close();
    db.close();

    return recCount;
}

}

activity_main.xml – the layout so we can enter the desired number of records to be inserted, choose whether we want it to be a ‘normal’ or ‘fast’ insert, and the status of the operation.

<RelativeLayout xmlns:android="http://schemas.android.com/apk/res/android"
xmlns:tools="http://schemas.android.com/tools"
android:layout_width="match_parent"
android:layout_height="match_parent"
android:paddingBottom="@dimen/activity_vertical_margin"
android:paddingLeft="@dimen/activity_horizontal_margin"
android:paddingRight="@dimen/activity_horizontal_margin"
android:paddingTop="@dimen/activity_vertical_margin"
tools:context=".MainActivity" >

<EditText
    android:id="@+id/editTextRecordNum"
    android:layout_width="match_parent"
    android:layout_height="wrap_content"
    android:layout_alignParentLeft="true"
    android:layout_alignParentTop="true"
    android:inputType="number"
    android:singleLine="true"
    android:ems="10" >

    <requestFocus />
</EditText>

<Button
    android:id="@+id/buttonNormalInsert"
    android:layout_width="wrap_content"
    android:layout_height="wrap_content"
    android:layout_alignLeft="@+id/editTextRecordNum"
    android:layout_below="@+id/editTextRecordNum"
    android:text="Normal Insert" />

<Button
    android:id="@+id/buttonFastInsert"
    android:layout_width="wrap_content"
    android:layout_height="wrap_content"
    android:layout_alignBaseline="@+id/buttonNormalInsert"
    android:layout_alignBottom="@+id/buttonNormalInsert"
    android:layout_toRightOf="@+id/buttonNormalInsert"
    android:text="Fast Insert" />

<TextView
    android:id="@+id/textViewStatus"
    android:layout_width="wrap_content"
    android:layout_height="wrap_content"
    android:layout_alignLeft="@+id/buttonNormalInsert"
    android:layout_below="@+id/buttonNormalInsert"
    android:padding="10dp"
    android:text="Status" />

Answer 2

According to the SQLITE documentation:

A transactional database is one in which all changes and queries appear to be Atomic, Consistent, Isolated, and Durable (ACID). SQLite implements serializable transactions that are atomic, consistent, isolated, and durable, even if the transaction is interrupted by a program crash, an operating system crash, or a power failure to the computer.

In android usual insert is slow, so you have to use a transaction when you want to insert a lots of data. When you want to insert thousands of records into the database, inserting each record takes a lot of time and valuable resources. In such scenarios, batch insert or updates can speed up the process.

Here an example how to use transactions in android:

database.beginTransaction();
// or use  use beginTransactionNonExclusive() or beginTransactionWithListenerNonExclusive(SQLiteTransactionListener)
// to start a transaction. Non-exclusive mode allows database file to be in readable by other threads executing queries.
database.beginTransactionNonExclusive();
    try {
        String sql = "Insert or Replace into Students (student_number, age, phone) values(?,?,?)";
        SQLiteStatement compileStatement = database.compileStatement(sql);
        for(int i = 0; i < studentList.size(); i++) {
            compileStatement.bindString(1, studentList.get(i).student_numerb());
            compileStatement.bindString(2, studentList.get(i).age());
            compileStatement.bindString(3, studentList.get(i).phone());
            compileStatement.execute();
            database.setTransactionSuccessful();
        } catch(Exception e){
            e.printStackTrace();
        } finally {
            database.endTransaction();
        }