Locking only in trans mode, thanks to IT & Bastiaan, Identifying Integers
Doug Easterbrook
doug at artsman.com
Mon Jan 22 16:43:47 EST 2018
hi Das.
you have to be careful, and I’m surprised nobody has mentioned it on the list .. but i would NEVER put a sql transaction around an enter data.
its exactly like the omnis version — it locks the record and forced people to wait. IN a single user system, doesn’t matter. In a multi user system, it is kiss of death.
far better approach (pseudo code only) is to do optimistic locking
read record
calc tempvar as saved editnumber or timestamp
enter data
if user says to save
re-read record and lock
if tempvar <> editnumber
message: somebody beat you to the punch
rollback transaction and redraw screen with new data
else
update record.
end if
end if
the reason, just like with the omnis database using DML is twofold
1) you want a very short lock on the data . as short as possible to make sure you don’t block other users
2) optimistic updates makes for a better user experience.
3) nobody can shut you out of the data record by walking away from the computer leaving the data locked.
>> Do $cinst.$sessionobject.$transactionmode.$assign(kSessionTranManual)
>> Do $cinst.$sessionobject.$begin() Returns #F
>> select … for update
>> enter data
>> if flag true
>> update ...
>> Do $cinst.$sessionobject.$commit
>> Do $cinst.$sessionobject.$transactionmode.$assign(kSessionTranAutomatic)
>> else
>> Do $cinst.$sessionobject.$rollback
>> Do $cinst.$sessionobject.$transactionmode.$assign(kSessionTranAutomatic)
>> end if
Doug Easterbrook
Arts Management Systems Ltd.
mailto:doug at artsman.com
http://www.artsman.com
Phone (403) 650-1978
see you at the third annual users conference
https://tickets.proctors.org/TheatreManager/95/online?performance=29086 <https://tickets.proctors.org/TheatreManager/95/online?performance=29086>
> On Jan 22, 2018, at 1:45 PM, Das Goravani <das at goravani.com> wrote:
>
>
> Well this is a hoop to jump through but if that’s the way it is then that’s what I’ll do. I do understand what youre saying and doing here, this way, matches what I was thinking in terms of when to issue commands, the Select..For Update Before you enter data to lock the record
>
> So when there’s a CANCEL to the enter data…nothing has been sent to the server, so there’s nothing to rollback, correct ? And how do the locks get cancelled on a Cancel to enter data.. by undoing the Transaction mode command?
>
> Thank you IT for your very helpful answer. Thanks to Bastiaan for his as well.
>
> Bastiaan, you mentioned something regarding Table Classes. I was thinking up til now, and still, that I can avoid using them… the manuals say you really only need to use them to override the standard methods they have inside them, which I dont need to do at this point, that I’m aware of, or do you just need to use for various reasons when you’re serious about all this stuff? I want to get off on the correct footing.. I thought I could skip table classes but that sometimes I would need query classes when doing two tables at once into a list…
>
> There’s another thing.. in order to lock 2 records say, customer and invoice, that come from two different tables, I’m thinking I have to select each with a Prepare for Update (Select..For Update) commands… i hope it doesn’t tell me I cannot issue a second one.. I dont know how to select two records from two tables with one select command… i have unique fields with a simple integar in them for identity… my way is to have a table that is only for "Giving out numbers", it keeps track on the number, increments itself when one is taken out, and saves, just a very small definition, just 3 fields, mainly 2, the third is name of table it serves, 4 records for 4 types of number needs, customers, invoices, products, and representatives, so far… in future more parent type files will require making a record for number check out for them… these number check out records are the most accessed records in the database… a very quick lock and number grab during saving of a main record. I dont know of any other way to do it without risking duplicates, and being impervious to export import… mine is impervious… just check in startup construct for the records to be holding one digit higher than highest record holds.. easy.. to ensure correct dispensation.
>
> I think it’s great that there is this email list, I think that the knowers who answer are very magnanimous, kind. I hope to be an answer giver myself someday. I’m magnanimous too, by nature.
>
>
>
>> On Jan 22, 2018, at 10:20 AM, IT <it at plastipol.com> wrote:
>>
>> Hi Das
>>
>> SELECT … FOR UPDATE locks records in transaction mode, otherwise only selects the record
>> So you need to do something like this:
>>
>>
>> Do $cinst.$sessionobject.$transactionmode.$assign(kSessionTranManual)
>> Do $cinst.$sessionobject.$begin() Returns #F
>> select … for update
>> enter data
>> if flag true
>> update ...
>> Do $cinst.$sessionobject.$commit
>> Do $cinst.$sessionobject.$transactionmode.$assign(kSessionTranAutomatic)
>> else
>> Do $cinst.$sessionobject.$rollback
>> Do $cinst.$sessionobject.$transactionmode.$assign(kSessionTranAutomatic)
>> end if
>>
>>
>>
>>> El 22/1/2018, a las 4:30, Das Goravani <das at goravani.com> escribió:
>>>
>>> I would like to read the use of that command, $definefromSQLclass… if you can post it for me, just one line of code, i need to see what precedes it, how it’s used, syntax, example basically. Similarly for the one called $prepareforupdate as i assume that locks records since it issues a SELECT .. FOR UPDATE command which I was told locks records
>>
>> _____________________________________________________________
>> Manage your list subscriptions at http://lists.omnis-dev.com
>
> Das Goravani
> das at goravani.com
> http://www.Goravani.com <http://www.youtube.com/profile?user=RichardEdwardWurst>
> <http://www.youtube.com/RichardEdwardWurst>http://www.YouTube.com <http://www.youtube.com/>/RichardEdwardWurst
> http://www.YouTube.com/DasGoravani <http://www.youtube.com/DasGoravani>
> http://YouTube.com/JyotishStudio <http://youtube.com/JyotishStudio>
>
>
>
>
>
>
>
> _____________________________________________________________
> Manage your list subscriptions at http://lists.omnis-dev.com
More information about the omnisdev-en
mailing list