Talk:ISDBDatabase::BeginTransaction

When/why should this be used, and even more importantly, when should it NOT be used?

What happens if you are unable to 'commit' the transaction (due to script error or crash, etc) and MM keeps running? or when the whole app crashes?

From what I've read on the forum you should use it when you want to do different database queries at once (for better performance). If you have only one query at a time to do, you certainly don't need this. If the transaction isn't committed (e.g. due to MM crash), then the query won't be executed (I think). When the script crashes, the queries will only be committed when another action does the commit. However, in practice there will always first be a normal query, or a BeginTransaction command, which will probably void the command you started in your own script, or even result in an error. Anyone else has more info on this? -- Steegy 15:28, 15 April 2008 (EDT)

here is an example of how I've started using transaction/commit

  
  SDB.Database.Commit
  SDB.Database.BeginTransaction
  Set Iter = SDB.Database.OpenSQL(Qry)
  SDB.ProcessMessages
  Do While NOT Iter.EOF
    ' do whatever processing you need to do
    Iter.Next
    SDB.ProcessMessages
  Loop
  SDB.Database.Commit

I do this to avoid those sql errors 'can not open tranaction while another is in progress' or whatever it was. I commit any pending transactions, open my transaction, then commmit it after the loop. Note however, I am not really making database changes, but I think the above is sequence is even more important for when doing actual db updates.

I don't know if the above is the right way or not, but it seems to prevent the transaction errors and I have not seen any evidence it hurts anything. It seems to let multiple scripts and/or tag updates happen together without causing errors. --Teknojnky 13:47, 17 April 2008 (EDT)

Notice though that calling SDB.ProcessMessages too much (certainly in a fast small loop) can do more harm than good. If you *really really* need to use it in a fast loop, then only do it each X iterations (e.g. by using a counter and a conditional SDB.ProcessMessages that also resets your counter).

Dim Cnt : Cnt = 0
Do While SomethingIsTrue
    ' do whatever short processing you need to do
    If Cnt = 300 Then
        SDB.ProcessMessages
        Cnt = 0
    Else
        Cnt = Cnt + 1
    End If
Loop

That way (depending on X), SDB.ProcessMessages is only called e.g. each 500 ms instead of each 10 ms. -- Steegy 19:12, 17 April 2008 (EDT)

Hmm, I am a big advocate of plenty of SDB.ProcessMessages, and while this may slow down the script somewhat, it keeps the UI and other tasks moving as smoothly as possible. This is really evident with the spectrum displays, without SDB.ProcessMessages, the display freezes and/or gets really jerky. I consider MM like how windows 3.1 was, in that it is a 'cooperative' multi-tasking system instead of a 'premptive' one.. ProcMess gives control back to allow other processing making scripts appear to run much smoother. I do agree that you don't need or want them everywhere, but they are particularly effective in loops and near queries (which also freeze up the ui, but can't do much about that other than giving ProcMess a chance to process anything that got blocked during the query).