Baanboard.com

Go Back   Baanboard.com > Forum > Baan Quick Support: Functional & Technical > Tools Development

User login

Frontpage Sponsor

Main

Poll
For ERP LN feature pack upgrade, what method of install are you using?
Installation Wizard into existing VRC
37%
Installation Wizard into new VRC
39%
Manual into existing VRC
3%
Manual into new VRC
21%
Total votes: 38

Baanboard at LinkedIn


Reference Content

Reply
 
Thread Tools Display Modes
  #1  
Old 4th June 2014, 11:32
andy2609 andy2609 is offline
Member
 
Join Date: Feb 2011
Posts: 37
andy2609 is on a distinguished road
Baan: IVc4 - DB: Informix - OS: HP-UX
Cool Ascertain if a table record is locked when read
Baan: iBaanERP 5.0b
C/S: None/Unknown

When reading a table record that has been created by another process, but that has perhaps not yet been committed by that process, I want to be able to determine whether the record is available for update / deletion at the point of reading it. Does anyone know a method of doing this?

Scenario:
- Process A creates a record in a Baan table, then moves on to create other records, but has not yet committed the entries.
- Process B is a continuous process that is running concurrently and reads the record that has been created by process A (but not yet committed). Currently, process B creates an output file from the record it has read, then attempts to delete the record from the table (generating a retry and, ultimately, a fatal error).
I want to be able to identify, at the point of reading the record in process B, that it is not yet available to delete and skip it until the next cycle.

Any thoughts, anyone?
Reply With Quote
  #2  
Old 4th June 2014, 12:58
andy2609 andy2609 is offline
Member
 
Join Date: Feb 2011
Posts: 37
andy2609 is on a distinguished road
Baan: IVc4 - DB: Informix - OS: HP-UX
OK, I have worked around this by making the following change to process B:
After reading the record without update,
1) Set a start time
2) Set a retry point
3) Set a finish time
4) Compare start and finish time and if > 2 seconds have elapsed, abort the transaction and skip the read of this record (indicates a retry)
5) Read the record for update
6) Back up the contents of a field value
7) Change the field value
8) Commit the change (if this fails, it will return to 2, above)
9) If the commit was successful, restore the field value from backup and proceed to process the record

This works, but if there is a better method that anyone can think of, then I would be interested to hear from you!

Andy
Reply With Quote
  #3  
Old 4th June 2014, 14:42
bhushanchanda's Avatar
bhushanchanda bhushanchanda is offline
Guru
 
Join Date: Sep 2012
Location: India
Posts: 2,320
bhushanchanda has a spectacular aura aboutbhushanchanda has a spectacular aura aboutbhushanchanda has a spectacular aura about
Baan: LN FP 1-9, 10.4, a little bit of Baan IV - DB: SQL Server 2008, Oracle - OS: Windows Server 2008 R2, Unix
Hi,

I guess setting the time will not help and could cause issues. DB operations are rather fast and you dont have a time stamp for separating the interval.

I will go with a flag setting way. You can hold old values in temp variables after commit you can check if the commit is done or not.

Btw, I am still not sure about what you are looking for. If you can post your code snippet, that will help.
__________________
Regards,

Bhushan

Unless you try to do something beyond what you have already mastered, you will never grow!
Reply With Quote
Sponsored Links
  #4  
Old 4th June 2014, 20:15
BaanInOhio BaanInOhio is offline
Senior Member
 
Join Date: Oct 2005
Location: Northeast Ohio
Posts: 180
BaanInOhio is on a distinguished road
Baan: Baan 4c4, 5C, LN - DB: Informix, Oracle, SQL - OS: HP UX, Win2K
Thumbs up

There a couple ways you can handle this. The easiest would be to modify your db.update (if using 'db' instead of DAL) to trap errors instead of going to the retry point. This way, you can gracefully handle the ELOCKED (record locked) or EFLOCKED (file locked) situations by disregarding the line until the next run that it becomes available.

Code:
reterr = db.update(ttableid, db.retry, db.return.error)
if (reterr = ELOCKED or reterr = EFLOCKED) then
    | do nothing, wait until next update
else
    | handle other type of error yourself
endif


You might be able to check for the lock ahead of time using db.check.row.dlocked:

Code:
      | tableid is a table with two fields in the primary index.
      | fill all index fields
tableid.field1 = ...
tableid.field2 = ...
if (db.check.row.dlocked(ttableid) = 0) then
     | record is not locked, fill fields and update
else
     | record is locked, wait until next run - dlock > 0 is locked, < 0  is error.
endif
Reply With Quote
  #5  
Old 4th June 2014, 22:00
mark_h's Avatar
mark_h mark_h is offline
Guru
 
Join Date: Sep 2001
Location: Kentucky, USA
Posts: 6,930
mark_h will become famous soon enough
Baan: Baan 4C4 A&D1 - DB: Oracle - OS: Sun Solaris
Okay - did I miss something or am I not understanding something. I see this statement - "When reading a table record that has been created by another process, but that has perhaps not yet been committed by that process" - if the record has not been committed then another process would not be able to read it. So in your exampe - until process A commits the record process B would not be able to read it. As far as I know that is how Oracle works - is informix different?

PS - I use application locks for records on tables that I do not want to be messed with. Those are easy to set and release.
__________________
Mark

GO Cards!
My latest mantra - make sure you have latest stpapi patches and the latest session object. If on LN then please explore the option of using DAL2 functionality.

Shared Solutions for Baan systems provided free by Baan Board.
Play the Google game and help Baanboard get better rankings. Do your part. Click here to find how.
Reply With Quote
  #6  
Old 5th June 2014, 11:04
andy2609 andy2609 is offline
Member
 
Join Date: Feb 2011
Posts: 37
andy2609 is on a distinguished road
Baan: IVc4 - DB: Informix - OS: HP-UX
Thanks all - some response

Re: Setting a flag at db.delete / db.update:
Thanks bhushanchanda & BaanInOhio, but by the time the program reaches this point, it has already created O/S file entries and I want to prevent it from doing so in the first place, if it is not available for update.

Re: db.check.row.dlocked:
Thanks BaanInOhio - I wasn't aware of this function and it's not in the manuals I have seen. It seems like the perfect solution, but doesn't seem to work for me. I'm handling a bespoke table tried this:
ecedi904.pmno = i.pmno (Assigns single-field primary key)
tableid_ecedi904 = db.bind("tecedi904") (Creates pointer successfully)
if (db.check.row.dlocked(tableid_ecedi904) = 0) then (Zero is returned here, even though this record is locked by the other process)
Any ideas? (In this scenario, the Baan version is Vb - do you know whether this function is compatible?)

Mark_h:
Thanks for your thoughts. Process A in my scenario is creating multiple records in a bespoke table and commits at the end. As it is processing and before the commit point, the records are available to read - in fact these can be viewed via ttaad4500 before commit. (If the process were to be deliberately terminated without committing, for example, then the records are not written and are no longer visible - this is the same for all Baan tables during update operations on AS400 / DB2.)
Reply With Quote
  #7  
Old 5th June 2014, 11:05
andy2609 andy2609 is offline
Member
 
Join Date: Feb 2011
Posts: 37
andy2609 is on a distinguished road
Baan: IVc4 - DB: Informix - OS: HP-UX
Thanks for your thoughts

Process A in my scenario is creating multiple records in a bespoke table and commits at the end. As it is processing and before the commit point, the records are available to read - in fact these can be viewed via ttaad4500 before commit. (If the process were to be deliberately terminated without committing, for example, then the records are not written and are no longer visible - this is the same for all Baan tables during update operations on AS400 / DB2.)
Reply With Quote
  #8  
Old 5th June 2014, 15:48
mark_h's Avatar
mark_h mark_h is offline
Guru
 
Join Date: Sep 2001
Location: Kentucky, USA
Posts: 6,930
mark_h will become famous soon enough
Baan: Baan 4C4 A&D1 - DB: Oracle - OS: Sun Solaris
I never knew that about AS400/DB2.

How many records are being written before being committed? If it is not a lot of records then process A could set an application lock when it writes the record. Then it could remove the application when it does the commit. Process b could read the record, check for application lock, if it exists skip the record.

Now if it is writing 1000's of records this would probably not be a good solution. And there is probably something better. I know what bhushan an baaninohio recommended should work. I only used something like it once, but I think I just skipped any error - the next run would pick up the record. Can't seem to find that code.

I found some old code a contractor wrote on our system - not sure if it helps or would even work in your case. Basically it looks like he tried to lock the record - if it was already locked it could not be updated.
Code:
	select	tdpur041.orno, tdpur041.pono
	from	tdpur041
	where	tdpur041.orno = :tdpur040.orno
	selectdo
		save.error.bypass = error.bypass
		error.bypass = 2
		retry.count  = 0  
		db.eq(ttdpur041,db.delayed.lock)	
		while db.error(ttdpur041) = ELOCKED  
			if retry.count < 10 then 
				mess("tibom11106",0, tdpur041.orno, 
				      tdpur041.pono, retry.count)
				| Record of positon %d/%d is locked; Retried
				| for %d  sec. 
				retry.count = retry.count + 1
				suspend (1000) | 1 seconds
				db.eq(ttdpur041,db.delayed.lock) 
			else 
				clean.mess() 
				lines.locked = true
				return 
			endif 
		endwhile 
		error.bypass = save.error.bypass
		if retry.count <> 0 then
			clean.mess()
		endif
		lines.locked = false 
	endselect
__________________
Mark

GO Cards!
My latest mantra - make sure you have latest stpapi patches and the latest session object. If on LN then please explore the option of using DAL2 functionality.

Shared Solutions for Baan systems provided free by Baan Board.
Play the Google game and help Baanboard get better rankings. Do your part. Click here to find how.
Reply With Quote
  #9  
Old 6th June 2014, 10:13
andy2609 andy2609 is offline
Member
 
Join Date: Feb 2011
Posts: 37
andy2609 is on a distinguished road
Baan: IVc4 - DB: Informix - OS: HP-UX
Thanks for the snippet, Mark - looks useful!
Reply With Quote
  #10  
Old 10th September 2014, 14:59
ashu2814's Avatar
ashu2814 ashu2814 is offline
Member
 
Join Date: Feb 2008
Posts: 69
ashu2814 is on a distinguished road
Baan: Baan 6.1 - DB: sql - OS: win2003
Hi Mark,

The code you have posted is not working in BaaN V.
Code:
g.tcibd100.id = db.bind("ttcibd100","",011)
	select  tcibd100.*
	from    tcibd100  
	where   tcibd100._index1 = {:1}
	order by tcibd100._index1
	as set with 1 rows
	wherebind(1, item.f)
	selectdo
		save.error.bypass = error.bypass
		error.bypass = 2
		retry.count  = 0  
		db.eq(g.tcibd100.id,db.delayed.lock)	
		g.lc.c =  db.error(g.tcibd100.id) 
		while db.error(g.tcibd100.id) = ELOCKED  
			if retry.count < 10 then 
				
				retry.count = retry.count + 1
				suspend (1000) | 1 seconds
				db.eq(ttcibd100,db.delayed.lock) 
			else 
				clean.mess() 
				lines.locked = true
				return 
			endif 
		endwhile 
		error.bypass = save.error.bypass
		if retry.count <> 0 then
			clean.mess()
		endif
		lines.locked = false 
	endselect

	if ( lines.locked = false ) then
		|no lockin g	
		select  tcibd100.*
		from    tcibd100 for update
		where   tcibd100._index1 = {:1}
		order by tcibd100._index1
		as set with 1 rows
		wherebind(1, item.f)
		selectdo
			tcibd100.ncst = tcncst.no
			tcibd100.ncdt = 0
			 db.update( ttcibd100, db.retry ) 
		endselect
	else
		g.fp.c = seq.open("\\kuuerp03\userdata\asha\011_Monitor.txt","at+")
		seq.puts(trim$(item.f) & " ; " & str$(g.lc.c),g.fp.c)
		seq.close(g.fp.c)
	endif	
	 commit.transaction()
__________________
Regards,
ashu2814

Last edited by bhushanchanda : 10th September 2014 at 15:02. Reason: Added code tags!
Reply With Quote
Reply


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Copy a record from one table to another mig28mx Tools Development 5 4th March 2014 00:08
Inserting an Inverse Record from the same table using DAL eric.dizon Tools Development 1 23rd January 2014 04:32
Copy a record from a table into the same table jcook331 Tools Development 3 30th April 2008 00:02
Insert Record to a Table Dynamically Hiba_t Tools Development 6 8th April 2008 10:30
CODE: Table Loader ~Vamsi Code & Utilities 3 26th February 2003 14:05


All times are GMT +2. The time now is 20:12.


©2001-2017 - Baanboard.com - Baanforums.com