Transactions can be a very complex subject and we'll only touch it a little deeper here then we've done so far. We'll look at why transactions are needed and how Omnis implements transactions cross platform. We'll then look into the short comings of what we've done with transactions so far and see how we can improve on them.
Now keep in mind, not all databases that we can use with Omnis support transactions. Our DAM object has a property called $allowtransactions that will tell us if the DAM thinks if transaction support is available. I generally make it a rule not to build applications based on databases that don't support transactions. It simply gives me to many headaches :D But you can't always avoid this.
So what is a transaction? Well a transaction is a mechanism that ensures that any action that happens with in that transaction is seen as a single unit of work. That means that until you finish (commit) the transaction all actions that have happened within that transaction can be undone if something goes wrong. The transaction always either succeeds or fails in its entirety.
An example, let's say that you've created a window where you can enter orders into your system. Your order consists of an order header record that contains an order number, the link to the customer and the total amount for that order, and you have a number of order detail records that specify the individual items that make up this order and their price.
When you save this order you want to save the order in its completeness. If you safe the header but there is a problem saving the details, the data in your database suddenly is inconsistent. You have an order header stating you sold $100 worth of goods, but if only half of the details are saved, the details only add up to $50.
A transaction lets you protect this. If you start a transaction before you save any changes to the database, then insert/update your order header, then insert/update/delete your order details and finally commit the transaction you will be certain the database will not make those changes permanent until they have all happened. If anything happens in between, you can simply do a rollback and all changes since the start of the transaction are undone.
A transaction is also rolled back if something unexpected happens such as when the database connection is lost, or your application crashes or your customer trips over his/her power cables.
You can be sure you're changes are not applied until you execute your commit and that certainly safes a few nightmares and helps keep your database consistent.
Note that some database platforms require you to always start and commit a transaction to execute a query. Oracle I believe automatically starts a new transaction when you commit the last transaction. If you forget to execute a commit your transaction stays open and when you later close your application, all your changes will be lost (hurrah). Omnis luckily helps you there (autocommit).
Sybase ASE, which I have the most experience with mostly does not require a transaction to run queries, any query run when no transaction is started is automatically committed.
To support transactions Omnis has a number of different transaction modes that are important to look at first. You can change/check the transaction mode through the $transactionmode property of the DAM object. It has the following values:
- kSessionTranAutomatic, this leaves it up to Omnis, Omnis will automatically start a transaction for each query executed if the platform requires this, and commit the transaction afterwards (similar to the old "autocommit on" in Classic). This means you do not need to, and I believe can not, start your own transactions.
- kSessionTranManual, Omnis now lets you do your own thing and Omnis will not automatically start or commit a transaction. You now need to call $begin, $commit and $rollback to do session control (this is similar to the old "autocommit off" in Classic). So now you have to do your own transaction control.
- kSessionTranServer, basically means hands off to Omnis. This means that you take over from Omnis and do everything yourself by executing the right SQL statements on the database. There is a big difference between kSessionTranManual and Oracle is a good example here. Note that I just mentioned that Oracle automatically starts a new transaction when you do a commit? That means that if you execute a "begin transaction" SQL command you actually get a nested transaction. That in turn means that you need to execute two commit statements to commit your transaction, one for you own transaction, and one for the Oracle one! So you really need to know the ins and outs of your platform to properly handle this mode, but it does give you the most flexibility
There is an important difference between platforms to know about though. Again looking at the difference between Oracle and Sybase they handle locking differently when another user tries to select the data. If we look at the default behaviour of these platforms we'll see that with Oracle, when a user is running an order rapport, and another user is changing an order at that same time, the rapport will be run as if no changes have happened. Oracle will see that the other user has locked the record(s) and simply retrieve the data as it was before the user started modifying the record(s).
Sybase on the other hand will wait until the user modifying the record is finished and either commits or rolls back his/her transaction. This means that the user running the order rapport will have to wait a little longer but after waiting he/she will get the rapport including the change made by the other user (personally I like Oracle's approach better).
You can do, what is often referred to as, a dirty read if you really want to. Again the different platforms will have different ways of implementing this and different rules apply (which is why I generally keep my hands off of it). A dirty read lets you select locked data in the database. That means that I can run my rapport and I actually get my results including the changes the other user has made even if a possibility exists that that user may rollback his/her transaction. On Sybase this is the only way to get my rapport without waiting for the other user to finish, but if this is your reason, I suggest you go back to the drawing board, you're doing something wrong already. I must add to this that there are some viable situations where you may want to do this. For instance in a search window you really don't care that you might get a result once in a while that may change again before you use it. But our order rapport example is one where I would never use dirty reads simply because it could make my order rapport incorrect if I read only part of the changes a user is making while my rapport is running (my order header amounts may not add up to my order detail amounts!).
Note: there are some databases out there that either allow a default to be set to, or always perform dirty reads.
When two users try to update the same record the first user will lock that record and the second user will always have to wait until the first user has finished. As you may remember we used this nice little fact to protect our auto numbering approach in tBaseCounted.$getNextValue().
Finally, I can lock records on selects but this is usually not done by default, again different databases implement this with slightly different SQL syntax. Any select on a table will result in the record being retrieved being locked. I generally don't use it.
So what about nested transactions? What are they and why do I need them? Well lets start by saying that the way Omnis implements transactions they are not supported by default, you will have to create support for them. Look at our tBaseCounted.$getNextValue() method. It starts a transaction, updates our counter table, gets the result and commits the transaction. Since there is only one update we're not really using our transaction to safeguard multiple changes here, but we're using the locking side effect from transactions to safeguard giving out unique values.
However, now I want to implement my order saving logic. My order saving logic looks like this:
1) get the next value for the order header
2) insert the order header
3) get the next value for the order detail
4) save the order detail
5) repeat 3 and 4 for each order detail
So what if I start a transaction before I execute these commands? Well we then go to step one, and in step one, uhm, I get a begin transaction? But I already have one! Hmmm.. Well we don't support nested transactions so it gets ignored, then it gets my next value, and it commits.. Oh oh.... That's the end of our transaction, right then and there.
The rest of our save thus happens outside of my transaction, if somewhere down the line a problem happens, well bad luck, there is no more transaction and its not going to rollback the way I expect it to.
Nested transactions allow me to do this, whenever a begin transaction is encountered I nest the transaction. This means that when a commit is encountered, it doesn't actually commit anything, it just goes back one transaction, until finally there are an equal amount of commits as there where begins and then my unit of work actually gets committed. If anywhere between the first begin and where I am right now there is a rollback, all changes are undone regardless of any previous commits.
Okay, now there is a catch here. Again different platforms implement nesting differently, some don't support it. Most will rollback all nested transactions on a rollback so you have to be careful that after a rollback, you know that you are no longer in a transaction. You don't need to do an equal number of rollbacks to begins.
We do not want to assume anything about the way that our platform would handle this, so we'll have to handle this within Omnis. How can we implement this?
class variable oDatabase.cNestCountList (kList)
instance variable oDatabase.iNestIdx (kLongInt)
oDatabase.$construct
----
; Start in automatic mode (should be the default)
Calculate $cinst.$transactionmode as kSessionTranAutomatic
; When we copy our database connection object around we share the same database connection
; But we do not share the same instance variables as a copy is a new instance
; So if we did our nesting count with an instance variable we're in trouble.
; Class variables are shared amongst instances so we need to use those but we do
; Want a counter per database connection. This is the way we work around that :)
If cNestCountList.$cols.$count=0
Do cNestCountList.$cols.$add("NestCount",kInteger,kLongint)
End If
Do cNestCountList.$add(0)
Calculate iNestIdx as cNestCountList.$linecount
; iNestIdx is copied to each copy of our database connection and thus they all point to
; the same entry in our counting list!
Class variables are shared amongst all instances of our object however simply using a nested counter directly doesn't work either. If we can assume we only have one database connection or we never start transactions in parallel on different database connections I guess we could but I find that assumption very dangerous. That's why I keep a list. Every time I instantiate a connection object it adds a line to this list and the instance variable iNestIdx points to this line in the list. Every time the connection instance gets copied it copies that same iNestIdx value and thus points to the same line in my class list. Each line in my class list thus uniquely represents a database connection.
(note: with object references we should be able to fix this as well and just use instance variables, which is much nicer, but I am not sure if object references work with $sessionobject and haven't experimented enough with it to answer that)
oDatabase.$begin()
----
If cNestCountList.[iNestIdx].NestCount=0
; Set manual transaction mode (autocommit off).
Calculate $cinst.$transactionmode as kSessionTranManual
; No transaction started, so we start one!
Do $inherited.$begin()
Send to trace log (Diagnostic message) {oDatabase.$begin(): Transaction started}
End If
Calculate cNestCountList.[iNestIdx].NestCount as cNestCountList.[iNestIdx].NestCount+1
Send to trace log (Diagnostic message) {oDatabase.$begin(): Transaction nest level is now [cNestCountList.[iNestIdx].NestCount]}
Quit method kTrue
Notice the use of trace log with diagnostic message turned on. This means these message do not get logged to the trace log until you rightclick in the trace log and select "Log diagnostic messages". The trace log is a great tool for checking if your code runs correctly. In this case it is always important to check if your save logic correctly handles nested transactions.
Also notice the way the classlist is used for keeping the nested transaction count.
oDatabase.$commit()
----
If cNestCountList.[iNestIdx].NestCount>0 ;; Still transactions open?
Calculate cNestCountList.[iNestIdx].NestCount as cNestCountList.[iNestIdx].NestCount-1
Send to trace log (Diagnostic message) {oDatabase.$commit(): Transaction nest level is now [cNestCountList.[iNestIdx].NestCount]}
If cNestCountList.[iNestIdx].NestCount=0
Do $inherited.$commit()
; Return to automatic mode...
Calculate $cinst.$transactionmode as kSessionTranAutomatic
Send to trace log (Diagnostic message) {oDatabase.$commit(): Successful commit}
End If
Quit method kTrue
Else
Send to trace log (Diagnostic message) {oDatabase.$commit(): Warning: Commit without a transaction!}
Calculate iLastError as "Warning: Commit without a transaction!"
Quit method kFalse ;; Very likely we've already had an auto commit but we may still want to inform the user!
End If
If we get a commit when there is no transaction open, we'll log a warning.
oDatabase.$rollback()
----
; $rollback() Rollback session transaction
If cNestCountList.[iNestIdx].NestCount>0 ;; Is there a transaction open?
Calculate cNestCountList.[iNestIdx].NestCount as 0
Send to trace log (Diagnostic message) {oDatabase.$rollback(): Transaction nest level is now [cNestCountList.[iNestIdx].NestCount]}
Do $inherited.$rollback()
Send to trace log (Diagnostic message) {oDatabase.$rollback(): Successful rollback}
; Go back to automatic mode!
Calculate $cinst.$transactionmode as kSessionTranAutomatic
Quit method kTrue
Else
; Could be that we already rolled back the transaction.
Send to trace log (Diagnostic message) {oDatabase.$rollback(): Warning: Rollback without a transaction!}
Quit method kTrue ;; We do return true because most likely we just encounter a rollback from a lower nest level.
End If
This last bit is very important because it allows us to do some defensive programming. When I use transactions within an object I can not assume that we're going to use this transaction within another transaction or not. I will always end any begin with a commit or rollback. This means that if I nest transactions I will have an equal number of commits+rollbacks (a combination is possible) even if I do not need all the rollbacks. But it makes my code safe, I don't need to make any assumptions about the code calling my method or about any method I call from my method that also use transactions.
; Always structure transaction handling like this:
Do $cinst.$sessionobject.$begin() ;; using $cinst.$sessionobject as an example but could also be any other connection instance.
; Perform any changes, calls whatever but do not escape from the code!
; We should always reach this point, every $begin has its $commit or $rollback!
if tmpSuccess ;; on success we commit
Do $cinst.$sessionobject.$commit()
Quit method kTrue
else ;; on failure we rollback
Do $cinst.$sessionobject.$rollback()
Quit method kFalse
end if
I've also added a $$saveDetail method that we don't really need right now but I've introduced them for later. This handles saving of any detail records so if we ever use our wBaseRecordBrowser for an order header/order detail window we can handle the saving of order details in this.
Finally I've added a $$detailInTransaction method that specifies whether saving the details should be part of the main transaction or not. With our order header/detail example this should be answered with a "true" but for saving customers with for instance address details we may return a false allowing the customer to be created but the address information to fail.
Some of the other methods have been adjusted to take these changes into account. Also note I did a small fix in tBaseCounted.$loadPrev() and in tBase.$newRecord().
Our new transaction handling we've placed here:
wBaseRecordBrowser.$evSave()
----
If iRecordRow.$validateRecord()
Calculate tmpSuccess as kTrue ;; Assume we'll be successful
Calculate tmpNewRecord as kFalse ;; Assume this is an existing record
; Begin a transaction on our global database connection, this one is used by our record row aswell!
Do gDatabase.$begin()
If iRecordRow.C1>0
If not(iRecordRow.$update(iRecordRow))
Calculate tmpErrorMsg as iRecordRow.$GetLastError()
Calculate tmpSuccess as kFalse
End If
Else
If not(iRecordRow.$insert())
Calculate tmpErrorMsg as iRecordRow.$GetLastError()
Calculate tmpSuccess as kFalse
Else
Calculate tmpNewRecord as kTrue
End If
End If
If tmpSuccess ;; Only save details if our main record was saved
If $cinst.$$detailInTransaction()
If not($cinst.$$saveDetail(tmpErrorMsg))
If tmpNewRecord
Calculate iRecordRow.C1 as 0 ;; Reset the ID, with the rollback it will be undone...
End If
Calculate tmpSuccess as kFalse
End If
End If
End If
If tmpSuccess
Do gDatabase.$commit()
; Save of details should be outside of our transaction?
If not($cinst.$$detailInTransaction())
If not($cinst.$$saveDetail(tmpErrorMsg))
OK message Error {[tmpErrorMsg]}
Quit method kFalse
End If
End If
Do $cinst.$setState(1)
Quit method kTrue
Else
; Always rollback before informing the user or the records will be locked until
; the user returns from his/her coffee break!
; Note, its possible we've already rolled back, actually we should assume this,
; but better safe then sorry..
Do gDatabase.$rollback()
OK message Error {[tmpErrorMsg]}
Quit method kFalse
End If
Else
OK message Error {[iRecordRow.$getLastError()]}
Quit method kFalse
End If
Note that we do not give any feedback to the user until we've done our commit or our rollback.
Open up the trace log (Tools->Trace log) and enabled logging of diagnostic messages.
Now try changing or creating some records with one of our two windows. We should see in our trace log that the transaction is started, nested and eventually committed.
Lets have some fun with this. Edit wContact and override its $$saveDetail. Change its code to:
wContact.$$saveDetail(pErrorText)
----
Calculate pErrorText as "We're just testing.."
Quit method kFalseBefore you begin, do a select * from Counter using an SQL client tool for your database to see the current contents of the Counter table.
Now try creating a new contact record. First we should see in our trace log that it will start a new transaction, it will nest a transaction (for out $getNextValue) it will commit that transaction, now it creates the contact record but we don't see that, finally it rollback the transaction due to our simulated error.
If we execute our selection in our SQL client we should see that nothing in this table has changed. If we cancel our editing and we browse through the records in our table, we should not be able to find our contact record. It is as if we've never tried to save our contact.
Now lets have some more fun, lets do something we should never ever do!
wContact.$$saveDetail(pErrorText)
----
OK message Keeping our transaction open {Go drink some coffee and make your collegues very upset....}
Quit method kTrueOur transaction is still open, our Counter table record should now be locked, our newly inserted record is also still locked (though nobody will notice that most likely).
Now try and do your select in your SQL client again. Three possibilities here:
1) you get results but you should notice that your counter has not increased (Oracle, etc.)
2) you're now waiting.. and waiting... and waiting.. (Sybase ASE, MSSQL, etc.)
3) your database defaults to dirty reads and you can see the change after all (Sybase Anywhere as it turns out)
Press OK in your OK message.
Only after you press OK and the changes are committed then either your other connection should be unblocked and you should see the end results, or you can rerun the query and now see the table having been updated.
If you start the application twice (start Studio twice) and logon to the same database, and you insert a contact with one of the applications but do not press the OK button, and you then try to insert a contact with the other. Or if you try and edit the same record, you should also notice that one application waits for the other.
This is also why you should never put an OK message within a transaction :)
Finally, deadlocks, the achillis heel of transactions. Take the above example where one user is waiting for the other user to finish his or her changes. What if the other user is also waiting for the first? Then they are waiting for each other and they can wait till the end of time. This is called a deadlock.
Now in our little application so far deadlocks are not very likely, our transactions are far to simple. But take our order header/detail example and we have introduced a risk, well at least with Sybase ASE in page locking mode :D Here it is possible that a page on the counter table is locked that contains both the counter for the header and detail table. If user one updates an order header, and user two insert a new order header (and thus updates the counter) then it is possible that the insert from user #2 is locked by the update of user #1 (page locking, the insert page may be the update page!) and user #1 gets locked by user #2 as he/she tries to add a detail to the order and thus needs access to the already locked page of the counter.
Luckily Sybase these days supports row locking as do most major database vendors and this would work fine. But as transactions become more complex and effect more records, the risk of deadlocking increases.
Two important rules to follow:
- Always insert/update your tables in the same order. Don't create your header and then your details, but in your update first update your details and then your header.
- Try and keep transactions small, don't overdo it. For instance, if you make a quick entry window where a new customer can be entered together with the first order he/she places it makes little sense to put that entire action in a transaction. When the customer is created, commit that, if the order creation fails, only rollback the order creation, then treat the customer as a returning customer from that point onwards.
----
Disclaimer: I'm sure some of the listers will recognise their ideas in this tutorial in one form or another. Some ideas are truly mine, some are inspired by what I've learned in over 10 years of conferencing and reading the list. It would be an impossible task for me to list the many many people who I owe thanks to. I'm sure you know who you are my friends. I take only credit for taking the time to put this series together. Use the code you find in this tutorials and the library/libraries attached freely and as you see fit. I take no responsibility and will not be liable for any damages resulting directly or indirectly from using the information I present here.
No comments:
Post a Comment