delete records in

For discussion related to Ffenics 2.0.

Moderators: Phil Winkler, Graham Smith, Pete Tabord

delete records in

Postby Pete Tabord » Tue Nov 15, 2016 11:41 am

I expect all long-term users know about using this to get a quick delete of all records. However, I've just discovered a downside - it tries for a database lock to allow it to work at the file level, otherwise it will switch into record by record mode.

If it gets the database lock, because it was run by the first user in, it won't be released until that query ends, and so no-one else will be able to log in. So if working multi-user I'd avoid it unless it is right at the end of a process.

I had created a query which did some clean-up before going off to do a lot of processing, and no-one else could log in while it was running.

It probably was always like this - I certainly haven't changed it - but I was working in 2.0 so here is where I've put the info.
Peter J. Tabord
Head of Development
Database Software Ltd.
ptabord@ffenics.com
Pete Tabord
 
Posts: 1882
Joined: Fri Sep 07, 2007 12:48 pm
Location: Caernarfon, Gwynedd, UK
Has thanked: 0 time
Been thanked: 3 times
 

Re: delete records in

Postby Graham Smith » Tue Nov 15, 2016 2:51 pm

I've been using this because it was much faster (it truncates the table rather than doing a record by record delete) but had run into a problem in DFW with one database. If I recall, I removed the command from the procedure and put it into a separate procedure by itself and the issue went away.

I wouldn't have thought it needed a database lock, but thinking about it, that makes sense. FWIW, I can't think of a case where I would need to use this for anything other than a working/holder table. So I'm not sure how much difference that would make.

Are you saying that it would behave different if the db was locked vs unlocked but no one using the table?

Would adding specific locking commands to the procedure matter?
Graham Smith
DataSmith, Delaware
"For every expert there is an equal and opposite expert.", Arthur C. Clarke (1917 - 2008)
"X-Clacks-Overhead: GNU Terry Pratchett"
User avatar
Graham Smith
 
Posts: 2501
Joined: Fri Sep 07, 2007 11:31 am
Location: Delaware, USA
Has thanked: 0 time
Been thanked: 1 time
 

Re: delete records in

Postby Pete Tabord » Fri Nov 25, 2016 4:47 pm

I didn't actually think of that - like you, I was only using it for a scratch table that will only ever have a few records in it, so switching to the other syntax was no biggie . An unlock db command might do the trick.
Peter J. Tabord
Head of Development
Database Software Ltd.
ptabord@ffenics.com
Pete Tabord
 
Posts: 1882
Joined: Fri Sep 07, 2007 12:48 pm
Location: Caernarfon, Gwynedd, UK
Has thanked: 0 time
Been thanked: 3 times
 
 

Return to Ffenics 2.0

Who is online

Users browsing this forum: No registered users and 1 guest