how lock table and .BI files entries are maintained for previous transaction...

[TomBascom]

In another thread Rajat asked:

how lock table and .BI files entries are maintained for previous transaction. Either every current transaction delete the previous entry or append it (I thinks transaction deletes previous entry, please confirm).

Both.

After a transaction commits there might be some left over record locks if your application is sloppy about that sort of thing. This is one of the reasons for the recommended best practices around scoping records and transactions -- you don't want the record to have larger scope because then you might have record locks that you do not expect to see.

In memory Progress maintains data structures for locks and transactions -- these will be reused once a transaction commits or a lock is no longer needed.

On disk Progress writes "notes" to the BI file (and to the AI file -- you are, of course, using after-imaging because, as we know, all responsible DBAs always enable after-imaging). These notes are appended to the BI and AI files -- they are time-ordered and written sequentially.

Unless something goes wrong they will never be needed again. They are only there just in case a crash occurs and partial transactions need to be backed out or if roll forward recovery is needed.

 

[Rajat Sharma]

Tom, thanks for replying and creating new thread for this.

By this post, I could understand the existence of transactions in memory and in disk as well. I have a few concern regarding the same:

1. I know, if transaction is too large (millions of records updated in a single transaction) then it would be difficult to manage .BI, .AI and lock table entries but here, isn’t it much difficult to manage memory prior to disk files (.AI, .BI, Lock Table). Because I thing before writing data to disk (before transaction ends), memory is having a huge transaction entries (in the form of data structure) and could cause a big problem of memory full or slow performance. Please confirm.

2. If in between, transaction halts (due to xyz reasons) then the previous data structure would also be removed from memory (I guess), but how this data structure is removed (Is there a garbage collector or something for doing this job).

Please suggest.

Thanks & Regards!
Rajat.

 

[TomBascom]

At a high level it's not really all that difficult. But "the devil is in the details"

Progress is optimistic -- the db assumes that transactions will succeed and that it is unlikely that a transaction will need to be backed out ("unlikely" <> "unimportant"). So rather than hold everything in memory, notes are written to the bi file as the transaction proceeds -- not all at the end. For performance reasons those IO ops are buffered in memory and the actual write is (hopefully) executed by an asynchronous "helper" process -- the BIW. The -bibufs parameter controls how many of these buffers are available. (If PROMON (or ProTop) show "empty bi buffer waits" then -bibufs is too small and should be increased.)

To mange record locks the db server allocates a fixed size (virtual) table whose size is defined by the -L startup parameter. If more than -L locks are requested an error is written to the .lg file and the session that tried to allocate one too many will crash. (Since the lock table is shared by all users the unfortunate session might not be one which actually uses an unreasonable number of locks -- it might just be an unlucky session that came along at the wrong moment...)

The db server also maintains a table of active transactions -- also fixed size, one per connection so -n tells you how big that is. The _TRANSACTION table just keeps track of interesting information like the transaction id, start time and state of the transaction. It does not contain any of the "notes" needed to undo or redo a transaction.

The detailed algorithms used to efficiently manage memory and disk IO are occasionally discussed by the engine crew at PUG Challenge. For instance:

http://pugchallenge.org/downloads2013/321_Database_IO.pptx
http://pugchallenge.org/downloads2014/2517_Recovery_Notes_v11.pptx

 

[Rob Fitzpatrick]

The lock table, which is called _Lock, is a Virtual System Table (VST). It is not a disk file. All VSTs are memory resident only, on the database server. They exist only while the database is open (i.e. online).

Don't think of it as being a data structure that is created or destroyed based on transaction activity. Remember that a transaction is client-specific, but there may be many users connected to a database, each of which may or may not be in a transaction at any given time. _Lock is created when the database is opened and its size (number of records) is determined by the value of the -L startup parameter. The default is 8192 records. As clients obtain or request locks, data is added to available entries in the lock table to represent those locks. When the lock is released, the data is removed from those _Lock records, but _Lock itself continues to exist.

Remember also that locks are not related solely to transactions.

for each customer:
  display customer.name.
  display transaction.
pause.
end.

Consider this code, run against the sports2000 database. It does not involve any transactions, but it does lock records. Run this code then check the lock table entries in promon while the code is paused; you will see a share lock on the customer table.

 

[Rob Fitzpatrick]

This is another good discussion from PUG Challenge 2013 on the inner workings of the BI file, database checkpoints, and crash recovery.
slides: http://pugchallenge.org/downloads2013/224_bi_checkpoints_crashes_v03.pdf
audio: http://pugchallenge.org/downloads2013/audio/224_bi_checkpoints_crashes_v03.mp3

 

[Rajat Sharma]

Thanks Tom, Rob,

These attachments are important and interesting for me.

Thanks & Regards!
Rajat.