Hi There.

Seasons greetings and I hope you can help.

Delphi 2010, Windows 7, ElevatedDB 2.18

Problem
------------

I want to lock all the tables in my database as exclusive before doing a schema update and other processing and I'm getting

    ElevateDB Error #300 Cannot lock the table USERS in the schema Default for shared access

when I try to run ExecSQL on one of the tables. It's just me, working on one PC within the one application.

Overview
------------
Basically, the desired sequence of events is as follows

database tables open, shared

close tables

reopen with exclusive access

processing including schema and data changes

close tables

reopen with shared access.

We have quite involved code for opening databases that performs a number of application functions as well as the purely database functions so it may seem a bit repetitious. The sequence of events needs to work as is. We'd use it for other functions that may require exclusive access in the future.

Code:
--------

Let's assume a TEDBTable tbl, a TEDBSession session and a TEDBQuery query and that tbl.tablename is USERS

There's a lot more code in between the start and finish but I think the key bits are as follows:

Starting with a session connected and the table open non-exclusive.

session.connected:=false;

tbl.close;

tbl.exclusive:=true;

session.connected:=false;

session.sessionname:=<newsessionname>;
session.LocalEncryptionPassword := GetPassword;
session.LoginUser := GetUserName;
session.LoginPassword := GetPassword;
session.LocalConfigPath := GetEnginePath;
session.LocalTempTablesPath := GetEnginePath+'\temp';
query.sessionname:=<same name as session.sessionname>;
tbl.active:=true;
session.Connected := true;

query.sql is set up as UPDATE USERS SET DATELU = DATE ‘2014-12-22, TIMELU = ’09:54:37’ … WHERE ID = ‘… <I don’t expect precise details are important>

Query.ExecSQL;
Jeff Gallant
Technical Manager
Computing for Labour
United Kingdom

Jeff


My guess is that you're trying to alter the ElevateDB USERS table not a local one. If I'm right then either this

session.LocalConfigPath := GetEnginePath;

is your problem. What does GetEnginePath return?

Or You don't have UseLocalSessionEngineSettings set to true.

Roy Lambert

Hi Roy

No, it's a table in our database. This works fine when the table's opened non-exclusive.

I've simplified it slightly. We use the some of this code in all of our applications. For any given database, we keep a Tstringlist of the database's tables where the objects point to objects that themselves contain a TEDBTable component and we iterate through them setting all the necessary parameters and, for any given application.

GetEnginePath returns the path to the Engine configuration tables for that user's installation of that particular application ie the engine's configPath.

I'm obviously not setting something up right but I don't understand why or under what circumstances ExecSQL would need  non-exclusive access where my application is the only one accessing the database.

Best wishes

Jeff  

Roy Lambert wrote:

Jeff


My guess is that you're trying to alter the ElevateDB USERS table not a local one. If I'm right then either this

session.LocalConfigPath := GetEnginePath;

is your problem. What does GetEnginePath return?

Or You don't have UseLocalSessionEngineSettings set to true.

Roy Lambert
Jeff Gallant
Technical Manager
Computing for Labour
United Kingdom

Sorry, the should be a comma, not paragraph break to read '... and, for any given application, GetEnginePath ...'

Jeff Gallant wrote:

Hi Roy

No, it's a table in our database. This works fine when the table's opened non-exclusive.

I've simplified it slightly. We use the some of this code in all of our applications. For any given database, we keep a Tstringlist of the database's tables where the objects point to objects that themselves contain a TEDBTable component and we iterate through them setting all the necessary parameters and, for any given application.

GetEnginePath returns the path to the Engine configuration tables for that user's installation of that particular application ie the engine's configPath.

I'm obviously not setting something up right but I don't understand why or under what circumstances ExecSQL would need  non-exclusive access where my application is the only one accessing the database.

Best wishes

Jeff  

Roy Lambert wrote:

Jeff


My guess is that you're trying to alter the ElevateDB USERS table not a local one. If I'm right then either this

session.LocalConfigPath := GetEnginePath;

is your problem. What does GetEnginePath return?

Or You don't have UseLocalSessionEngineSettings set to true.

Roy Lambert
Jeff Gallant
Technical Manager
Computing for Labour
United Kingdom
Jeff Gallant
Technical Manager
Computing for Labour
United Kingdom

Jeff


I think I've spotted it. You open the table in exclusive mode

tbl.exclusive:=true;
...
...
tbl.active:=true;

and then try and alter it with a query - ie another instance of the table. It fails, quite correctly in my opinion. What you'll need to do is update the table using table methods not a query.


Roy Lambert

On 12/22/2014 5:18 AM, Jeff Gallant wrote:
> when I try to run ExecSQL on one of the tables. It's just me, working on one PC within the one application.

You seem to have the table open in exclusive mode when running the query
- AFAIK exclusive does not mean "exclusive to session or app" it really
means nobody but this component (i.e. TEDBTable) can open it while i
have it open exclusively.

> tbl.exclusive:=true;
<snip>
> tbl.active:=true;

Assuming this succeeded then tbl now has an exclusive lock on the table.

> query.sql is set up as UPDATE USERS SET DATELU = DATE ‘2014-12-22, TIMELU = ’09:54:37’ … WHERE ID = ‘… <I don’t expect precise details are important>
> Query.ExecSQL;

Assuming tbl also refers to table USERS then query cannot get access
since your tbl has exclusive open already.


Raul

Raul and Roy

Thank you both for your explanations.

Actually, this update is not important to me in terms of exclusive access, it's "Alter Table" SQL further on in the process that really requires exclusive access (I embarked on this because it threw an exception because it couldn't get it)..

Am I right in deducing, from what you've each said, that I need to close the tbl object for the Alter table sql to succeed rather than opening it exclusive?

Alternatively, is there another way to give exclusive access to tables or the whole database so that only one instance of the application  can access it?

We are working in file sharing rather than client-server mode but there may be other users accessing other databases through the same engine configuration.

Best wishes and thanks again for your help so far

Jeff

Raul wrote:

On 12/22/2014 5:18 AM, Jeff Gallant wrote:
> when I try to run ExecSQL on one of the tables. It's just me, working on one PC within the one application.

You seem to have the table open in exclusive mode when running the query
- AFAIK exclusive does not mean "exclusive to session or app" it really
means nobody but this component (i.e. TEDBTable) can open it while i
have it open exclusively.

> tbl.exclusive:=true;
<snip>
> tbl.active:=true;

Assuming this succeeded then tbl now has an exclusive lock on the table.

> query.sql is set up as UPDATE USERS SET DATELU = DATE ‘2014-12-22, TIMELU = ’09:54:37’ … WHERE ID = ‘… <I don’t expect precise details are important>
> Query.ExecSQL;

Assuming tbl also refers to table USERS then query cannot get access
since your tbl has exclusive open already.


Raul
Jeff Gallant
Technical Manager
Computing for Labour
United Kingdom

On 12/22/2014 12:29 PM, Jeff Gallant wrote:
> Am I right in deducing, from what you've each said, that I need to close the tbl object for the Alter table sql to succeed rather than opening it exclusive?

Yes.

Alter table will fail if it cannot get exclusive access to table anyways
so you can just handle the error (same #300).

Other option is to leave your current logic in place but close table
right before you run alter query.

It does not give you anything over the alter error but might be useful
for other cases where you're running regular data manipulation sql.

tbl.exclusive := true;
tbl.active := true;
//if no exception here then you have it open exclusively
tbl.active := false;
query.ExecSQL;

The other option is to start a transaction (here you can include list of
tables instead of doing one-by-one or do entire database) and set a
short timeout so if it errors out you know you failed to obtain locks
right away.

> Alternatively, is there another way to give exclusive access to tables or the whole database so that only one instance of the application  can access it?

Transaction (for whole database) should do what you need for any of the
data manipulation aspects (insert/update/delete).

However i have not tried an alter table or other structure changes -
logically it should not work inside the transaction due to its nature
but I might be wrong here.

However you could still start a transaction and then if it succeeds
(i.e. you got the locks), commit/rollback right away and then run your
alter code.

In our case we're using the c/s so we just shut down the server(s)
during db upgrades. With c/s you can also check for any remote sessions
and drop if needed.

I believe some other people have rolled their own app specific solutions
- usually checking a table on app start and if there is a "maintenance"
flag then the app stops and does not attempt to open a table (you can
then poll the table and once flag clears proceed).

Raul

Jeff


When wanting to alter tables you have two problems:

1. Get everyone else off
2. Keep them off

Neither is easy. Raul's solution of just try and alter the table and if it works great, if not let the user know to try again later is pretty good.

Addressing the two problems individually:

1. Best approach is to run around like a headless chicken screaming "fire, run for your lives" and then after they've all left walk calmly around and log everyone out.

2. There are a few different ways. I elected to have a separate lock table based on UserAppLock (copy in the extensions) but you could also, whilst people are still logged on (I think) alter access permissions so that once logged out no one can log back in, you can also have a table that is used for no other purpose and open it exclusively after everyone else has logged out. If this table has to be opened as part of the login process then that will fail until you release the exclusivity.

If you can use the try and hope approach then that is probably the best one.

Roy Lambert

Raul wrote:

> In our case we're using the c/s so we just shut down the server(s)
> during db upgrades. With c/s you can also check for any remote
> sessions and drop if needed.

Strikes me that what I'd want in my "ideal database server" is the
ability to give it "startup scripts" to run, with a "once only" flag
too. So I could pass in the script to add columns or whatever, then
have the service restarted, and it would start up, run the script, and
then allow connections. Guaranteed safe time for changes.

--

Matthew Jones