SQL Transactions & Locking Techniques

In this section we will discuss the OpenBase SQL syntax for transactions, and the concepts and principles for them.

videoicon3.png Video: Transactions & OpenBase Record Locking

Transaction Overview

Transactions provide a mechanism for managing updates in multi-user environments. This is especially important when client programs must complete a series of update, inserts and deletes independently from other users or risk leaving the database in an inconsistent state.

The START TRANSACTION command marks the beginning of a transaction, and a COMMIT finalizes changes. Inserts, updates and deletes performed between these commands may be undone if a problem is detected by the application. This undo feature is accomplished by sending the database a ROLLBACK command in the place of a COMMIT.

The server will automatically rollback a transaction if the server loses communication with the client during a transaction (for example, if the client crashes). This serves as a safeguard to ensure that the database is never left in an inconsistent state.

OpenBase transactions are interactive with applications, allowing applications to control the direction of the transaction based on return codes. Changes to the database become visible to the client immediately. Transactions can not include any schema modifying SQL, such as ALTER TABLE or CREATE TABLE statements. The reason is that these operations can not be rolled back in OpenBase.

A couple of other OpenBase specific commands are discussed in this chapter.

They are: LOCK RECORD, UNLOCK RECORD, FOR UPDATE, and WRITE TABLE. These commands can be used for a variety of purposes within transactions to ensure data integrity in a multi-user environment.

The examples used in this chapter show transaction commands being sent to the server using OpenISQL. However, the same SQL will work from your applications.

The OpenBase API includes functions for starting, rolling back and committing transactions for your convenience. However, the same thing can be accomplished by issuing the SQL described below.

Starting a Transaction

START TRANSACTION
Starting a new transaction is accomplished by sending the start transaction SQL to the database. You can do this from OpenISQL by typing the following:

openbase 1> START TRANSACTION openbase 2> go

Now you can perform a series of inserts, updates and deletes. Each one should be executed individually.

Committing Changes to the Database

COMMIT
When you are done making changes to the database, you need to COMMIT the transaction to make it final. The COMMIT command can be issued using OpenISQL as follows:

openbase 1> COMMIT openbase 2> go

COMMIT makes all changes final.

Aborting a Transaction

ROLLBACK
ROLLBACK provides a mechanism for aborting database changes since the last START TRANSACTION. The ROLLBACK command must be issued before committing the transaction. Here is an example using OpenISQL.

openbase 1> ROLLBACK openbase 2> go

While this sums up the basic transaction mechanism, there are some other features that you may want to take advantage of. They are described in the following sections.

Locking Options

WRITE TABLE
The WRITE TABLE command obtains wants-to-write locks on tables you wish to change. Other users are blocked from updating locked tables until the locks are released by a COMMIT or ROLLBACK from the lock owner. However, table locks do not block other users from reading table information (unless they first try to obtain a lock).

WRITE TABLE waits for other users to release locks before obtaining them and returning control to the client program.

WRITE TABLE solves a particular problem. You may want to select a value from a row and then update the value within the same transaction. Specifying which table you plan to write to in the beginning can also help prevent deadlocks. It will also ensure that the value you select will not change before you have a chance to update the table.

Here is an example of how it is used.

openbase 1> WRITE TABLE EMPLOYEE, DEPARTMENT openbase 2> go

FOR UPDATE
FOR UPDATE can be used instead of WRITE TABLE if you want to obtain locks on specific records instead of locking the whole table.

FOR UPDATE is used in conjunction with a select statement to mark all rows in the result. The select statement returns no result if any of the result rows have been marked by another user. Whether a record is locked or not will have no effect on the users ability to read the record, unless of course, they try to obtain their own FOR UPDATE.

FOR UPDATE may appear after each table specification in the FROM portion of select statements. Following is an example.

openbase 1> SELECT * FROM EMPLOYEE FOR UPDATE openbase 2> go

This example will mark all the records in the EMPLOYEE table. By constraining the search you can mark just the records you are interested in.

openbase1> select * from EMPLOYEE t0 FOR UPDATE
openbase2>where t0.FIRST_NAME ='Joe'
openbase3>go

Locks are automatically released when COMMIT, ROLLBACK or ROLLBACK LOCKS (which only releases the locks, not the transaction) are sent to the server.

LOCK RECORD / UNLOCK RECORD

LOCK RECORD is a simple way of marking a single row using its _rowid value. The same thing can be accomplished using FOR UPDATE (described above), but LOCK RECORD is faster and more efficient. LOCK RECORD returns an error if the record is already locked.

Note that LOCK RECORD does NOT prevent anyone from updating the record. It simply serves as an advisory lock. If another process tries to lock the same record an error would be generated.
The OpenBase API includes the markRow:ofTable: and markRow:ofTable:alreadyMarkedByUser: for performing this task from Objective-C. However, you can issue the SQL commands directly and obtain the same effect. Here is an example.

The following example marks record 50500 in the EMPLOYEE table.

openbase 1> LOCK RECORD 50500 EMPLOYEE openbase 2> go

The next example releases the mark on record 50500.

openbase 1> UNLOCK RECORD 50500 EMPLOYEE openbase 2> go

Any marks you obtain during a transaction, whether by LOCK RECORD or FOR UPDATE, will be removed when you execute the COMMIT, ROLLBACK or ROLLBACK LOCKS commands.

Dealing With Deadlocks

Deadlocks, and avoiding them, is one of the challenges of database server design. It is important to understand what the problem areas are so we can all avoid them.

This section details the different types of deadlocks which are common in database applications. The aim is to provide some information on the purpose of transactions and how they are often misused as an undo mechanism for applications.

DEADLOCKS TYPE #1 — server side deadlock

Server side deadlocks can be very complex to detect. However, in OpenBase have built an algorithm which detects when clients are deadlocked. In this case an error is returned for one of the clients so that other clients can continue.

Start with Session A:

-----------------------------
start transaction
go
update table A set col1 = "foo" where _rowid = 1

Switch to Session B:

-----------------------------
start transaction
go
update table B set col1 = "foo" where _rowid = 1
go
update table A set col1 = "foo2" where _rowid = 1
go

Note: Session B is now waiting for session A to commit.

Switch back to Session A:

-----------------------------------
update table B set col1 = "foo" where _rowid = 1 go
ANSWER: update failed because deadlock was detected

ACTION TAKEN: Session A is notified that a "Deadlock has been detected" and Session B is allowed to continue. Session A must issue a rollback when the error is returned.

DEADLOCKS TYPE #2 — client side deadlock

With this type of deadlock the problem is on the client side. What happens is that a single-threaded application opens two connections to the database and performs the same actions as shown above. Session A & B in this case represent two connections.
Start with Session A:

----------------------------
start transaction
go
update table A set col1 = "foo" where _rowid = 1

Switch to Session B:

---------------------------
start transaction
go
update table B set col1 = "foo" where _rowid = 1
go
update table A set col1 = "foo2" where _rowid = 1
go

The last update will cause Session B to wait for Session A to either commit or rollback. The problem is that if session A and B are in the same program and same thread of execution, then the thread will never be able to continue A because it is waiting for B. Thus we would have a deadlock.

DEADLOCKS TYPE #3 — client side - undo mechanism

This isn't actually a deadlock, but it appears to be one. It is caused sometimes when transactions are mistaken for an undo mechanism. They are not. I will make a case for this in a moment. First I will describe how this problem shows up.

When using transactions and performing database changes it is important that clients do not pause to ask users for input in the middle of making changes. So, a application designer may be tempted to start a transaction, perform an update, ask users for input, perform another update. Then you might ask the users if the changes can be accepted and COMMIT or ROLLBACK depending on their answer.

The problem is that depending on the type of transactions you choose to use this will generally result in other users being blocked from making changes while the transaction is open and changes are pending. This isn't a deadlock but it sure does look like one to the user who is waiting.

Note: START PASSIVE TRANSACTION is a way around this if you have already built your application this way and it is inconvenient to change. This is a special transaction mode that uses a different strategy for serialization by making all the changes at once on the commit. This means that the updates, inserts and deletes are not reflected while the transaction is taking place — only after the commit.

WHY TRANSACTIONS ARE NOT AN UNDO MECHANISM
The purpose of a transaction is to make a series of changes atomic and serializable. In other words, it allows a set of changes or tests to take place so that requests or changes from other users do not create inconsistencies in the database.

Here is an example:
Let's say I have a simple checking account program which records the checks I write. My program starts a transaction, inserts the payment, performs a select to confirm that my account is not negative, and then either performs a commit or rollback. So the goal is to keep the account from being negative.

So, some would say that we should allow two transactions to update a table at the same time. So, let's say transaction #1 starts, inserts a payment, performs a select to make sure the account is not negative. Meanwhile transaction #2 starts, inserts another payment and also tests to see if the account is negative. Neither of the transactions can see the other's changes so they report that the account is positive. They both are allowed to commit. This violates the rules because now the account could be negative.

Some database vendors offer a special transaction modes that allows two transactions to operate on tables at the same time. But in order for the transactions to be serializable, one transaction always fails at the commit. This mode really offers the illusion that nothing is being blocked when in fact the unknowing client can have their changes thrown out because another transaction got there first. This is how all the databases work.

Even transaction modes which appear as having acceptable behavior for a multi-user undo are in fact not suitable for this purpose. Whether the strategy is to force transactions to be serialized (as is the default in OpenBase) or force conflicting transactions to fail, you still inconvenience the user. Instead, the application designer should cache changes on the client side and then perform all changes, tests and commits in one breath. This keeps problems to a minimum.

Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License