Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 1 to 10 of 15 total |
Exclusive lock problem |
Mon, Dec 22 2014 5:18 AM | Permanent Link |
Jeff Gallant | 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 dont expect precise details are important> Query.ExecSQL; Jeff Gallant Technical Manager Computing for Labour United Kingdom |
Mon, Dec 22 2014 9:57 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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 |
Mon, Dec 22 2014 10:29 AM | Permanent Link |
Jeff Gallant | 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 |
Mon, Dec 22 2014 10:31 AM | Permanent Link |
Jeff Gallant | 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 |
Mon, Dec 22 2014 11:11 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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 |
Mon, Dec 22 2014 11:14 AM | Permanent Link |
Raul Team Elevate | 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 dont 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 |
Mon, Dec 22 2014 12:29 PM | Permanent Link |
Jeff Gallant | 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 dont 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 |
Mon, Dec 22 2014 1:03 PM | Permanent Link |
Raul Team Elevate | 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 |
Tue, Dec 23 2014 2:56 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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 |
Tue, Dec 23 2014 9:14 AM | Permanent Link |
Matthew Jones | 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 |
Page 1 of 2 | Next Page » | |
Jump to Page: 1 2 |
This web page was last updated on Tuesday, May 14, 2024 at 07:14 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |