This is a gap in my understanding; I hope someone can help me understand it better. Sometimes an ABL client acquires a shared schema lock; sometimes not. So the success of online schema changes seems hit-and-miss and I'd like to be able to definitively understand it.
I'm trying to help our developers automate the rollout of new builds and the accompanying schema changes to dev and QA databases. Of course, when a user already holds the shared schema lock, the schema update fails.
I've searched the docs, PEG, and KB and come up relatively empty. Is there a list in a document or in someone's brain of known circumstances or code patterns that will cause a client to acquire the schema lock? (Apart from actually updating the schema...) Thanks.
To add to what Gus described.
Running code that references any table in a database (including via dynamic programming) will cause the client to hold a shared schema lock for as long that program is running/active.
Most schema changes are considered offline, that is, you can't have anyone running any code while you make the schema changes. The exception is if you are doing an online schema change, which only covers a very limited set of changes. You have to select the appropriate option when loading the .df and that will allow you to do the following while users are running code:
- add a new table
- add a new field to an existing table
- add a new index
For those 3 cases, existing r-code will continue to run (since 10.1C or so). Other schema changes that cause the table CRC to change will prevent r-code that access those tables to run and will need to be recompiled.
Compiled r-code (4GL bytecode) contains details of the record layouts, data types, etc. that are derived from the schema tables when a program is compiled. For example, a reference to a field by name in a record results in r-ccode that means “extract field n” at runtime. This is so that the name does not have to be looked up over and over again when the program is executing.
If the schema changes after the program is compiled, it wont work anymore. The table and index crc's take care of detecting that.
The shared schema lock prevents (most) schema changes when a compiled program is executing.
It might have been better to have individual schema locks for each table, but that would require acquiring more (maybe many more) locks before a program could start executing and that would increase time for the run statement to actually begin executing a procedure.
DO TRANSACTION: CREATE _Db. DELETE _Db. MESSAGE "Schema Locked" VIEW-AS ALERT-BOX. END.
Any schema table can be used instead of _Db.
Schema lock is created by an internal "trigger" for undate of the schema tables.
I missed that the question was about a /shared/ schema lock. Sorry.
Does it exist in the recent Progress versions?
promon/Activity: Resource Queues or _Resrc VST has only "Schema Lock" and its requests are zero in production environment.
Compiled r-code (4GL bytecode) contains details of the record layouts, data types, etc. that are derived from the schema tables when a program is compiled. For example, a reference to a field by name in a record results in r-ccode that means “extract field n” at runtime. This is so that the name does not have to be looked up over and over again when the program is executing.
If the schema changes after the program is compiled, it wont work anymore. The table and index crc's take care of detecting that.
The shared schema lock prevents (most) schema changes when a compiled program is executing.
It might have been better to have individual schema locks for each table, but that would require acquiring more (maybe many more) locks before a program could start executing and that would increase time for the run statement to actually begin executing a procedure.
To add to what Gus described.
Running code that references any table in a database (including via dynamic programming) will cause the client to hold a shared schema lock for as long that program is running/active.
Most schema changes are considered offline, that is, you can't have anyone running any code while you make the schema changes. The exception is if you are doing an online schema change, which only covers a very limited set of changes. You have to select the appropriate option when loading the .df and that will allow you to do the following while users are running code:
- add a new table
- add a new field to an existing table
- add a new index
For those 3 cases, existing r-code will continue to run (since 10.1C or so). Other schema changes that cause the table CRC to change will prevent r-code that access those tables to run and will need to be recompiled.
So if I am following this correctly... any running program that touches a table in the db (meta-schema and VSTs included...) is going to prevent interactive use of the data dictionary.
Yes, that is the way it has been.
Try executing this line of code in the progress session before you open the data dictionary.
SESSION:SCHEMA-CHANGE = "NEW OBJECTS".
You should now be able to use the dictionary interactively for permitted operations.
Tom
In my case I was more focused on how I could possibly avoid causing the problem for others. It seems like the meta-schema and VST' definitions are not going to change (you have to use proutil -C update* for that) and so, logically, references to those might reasonably be exempt. But I guess the decision was very "all or nothing" and so even those objects trigger the schema lock.
Thanks for the details responses everyone. It seems that my takeaway is that I should be able to load .dfs for additive schema changes without disconnecting users, but I might have to disconnect users to commit destructive schema changes.
The above is interesting but I feel like there's more subtlety to this. Based on what is written above, any reference to schema objects in a .p should cause the client running (or even compiling?) that .p to acquire and hold a schema lock, for some period of time.
find first customer. pause.
While this code runs the schema lock is held. The same applies for a read of a meta-schema table, e.g. _file. Without the pause there is no schema lock evident, so it is obtained and released quickly. But when I look at a QA database with users running applications, almost all of them have a schema share lock. Can someone explain the duration that the locks are held?
I note that the lock on the _db record doesn't show in _lock. Apart from screen-scraping promon R&D 1 19, is there another way to determine held schema locks?
The shared schema lock is held for the duration of the program which has the reference to a db table. Or, more correctly, it is held until all programs that have a reference to a db table have terminated.
There is currently no VST for viewing schema locks.
It appears the shared schema lock applies only to references to real tables and not VSTs.
find first _tablestat. pause. **** No schema locks outstanding.
There isn't any distinction on the type of table you are referencing. If you pause that program, you should be blocked trying to make schema changes that require an exclusive schema lock.
That's so weird. I just ran that code and saw no schema locks; now I do.
Obviously I did something wrong. I must have been looking at the wrong promon session or something.
> The shared schema lock is held for the duration of the program which has the reference to a db table.
In other words: the shared schema lock is created when a session loads an r-code initial segment with db reference:
PAUSE. IF FALSE THEN CAN-FIND(FIRST _tablestat). RCODE-INFO:FILE-NAME = PROGRAM-NAME(1). DISPLAY RCODE-INFO:DB-REFERENCES.