Dear All,

I am getting an error while using db.lock.table. I want to lock the table so that nobody will be table to perform any update or delete on the table till my program is being executed.

Error 203: Action not allowed within transaction

The code for the same is as follows:

while db.lock.table(ttisfa111) <> 0
suspend(1000)
i = i + 1
if i > 20 then
message("Cannot lock table tigbb761, please try after some time.")
choice.again()
endif
endwhile
tisfa111.plan = "1212"
db.retry.point()
tisfa111.plan = 0
tisfa111.item = "TEST12"
tisfa111.quan = 12
db.insert(ttisfa111, db.retry, db.skip.dupl)
commit.transaction()

Can anybody tell me why the error is occuring. ??
Where should db.lock.table be excatly used in the program

Dear All,

I am getting an error while using db.lock.table. I want to lock the table so that nobody will be table to perform any update or delete on the table till my program is being executed.

Error 203: Action not allowed within transaction

The code for the same is as follows:

while db.lock.table(ttisfa111) <> 0
suspend(1000)
i = i + 1
if i > 20 then
message("Cannot lock table tigbb761, please try after some time.")
choice.again()
endif
endwhile
tisfa111.plan = "1212"
db.retry.point()
tisfa111.plan = 0
tisfa111.item = "TEST12"
tisfa111.quan = 12
db.insert(ttisfa111, db.retry, db.skip.dupl)
commit.transaction()

Can anybody tell me why the error is occuring. ??
Where should db.lock.table be excatly used in the program

Hello,

BaanERP Programmers Guide


Locking

--------------------------------------------------------------------------------

Database inconsistencies can arise when two or more processes attempt to update or delete the same record or table. Read inconsistencies can arise when changes made during a transaction are visible to other processes before the transaction has been completed – for example, the transaction might subsequently be abandoned. To avoid such inconsistencies, BaanERP supports the following locking mechanisms:

Record locking
Table locking
Application locking
Record locking
To ensure that only one process at a time can modify a record, the database driver locks the record when the first process attempts to modify it. Other processes cannot then update or delete the record until the lock has been released. However, they can still read the record.

While one process is updating a table, it is important that other processes retain read consistency on the table. Read consistency means that a process does not see uncommitted changes. Updates become visible to other processes only when the transaction has been commited. Some database systems do not support read consistency, and so a dirty read is possible. A dirty read occurs when one process updates a record and another process views the record before the modifications have been committed. If the modifications are rolled back, the information read by the second process becomes invalid.

Supported features
INFORMIX
ORACLE
DB2
SQL Server

locking
row
row
row
row

dirty read
yes
no

consistent read
no
yes

transactions
yes
yes
yes
yes


Delayed locks
Locking a record for longer than required can result in unnecessarily long waiting times. The use of delayed locks solves this problem to a great extent.

A delayed lock is applied to a record immediately before changes are committed to the database and not earlier. When the record is initially read, it is temporarily stored. Immediately before updating the database, the system reads the value of the record again, this time placing a lock on it. If the record is already locked, the system goes back to the retry point and retries the transaction. If the record is not locked, the system compares the content of the record from the first read with the content from the second read. If changes have been made to the record by another process since the first read, the error EROWCHANGED is returned and the transaction is undone. If no changes have occurred, the update is committed to the database.

You place a delayed lock by adding the keyword FOR UPDATE to the SELECT statement (see BAAN SQL). For example:

table tpctst999
db.retry.point()
SELECT pctst999.*
FROM pctst999 FOR UPDATE
SELECTDO
pctst999.dsca = "...."
....
db.update(tpctst999, DB.RETRY)
ENDSELECT

Table locks
BaanERP provides a table locking mechanism, which enables you to lock all the records in a specified table. A table lock prevents other processes from modifying or locking records in the table but not from reading them. This is useful when a particular transaction would otherwise require a large number of record locks. You use the db.lock.table() function to apply a table lock.

Application locks
An application lock prevents other applications and users from reading and/or modifying an application's data during critical operations. It is not part of a transaction and so is not automatically removed when a transaction is committed. Instead, an application lock is removed when the application ends or when appl.detete() is called.



Regards

If your session has uncommitted changes, then db.lock.table() (http://www.baanboard.com/programmers_manual_baanerp_help_functions_db_operations_db_lock_table) will fail. (The same is true for db.create.table() (http://www.baanboard.com/programmers_manual_baanerp_help_functions_db_operations_db_create_table) and some other functions. See also the Transaction Handling (http://www.baanboard.com/programmers_manual_baanerp_help_functions_database_handling_transaction_handling) page.) In other words, if your session/script

first updates a record, in any table;
then calls db.lock.table(), for the same or any other table,

then you will get the error you report.

This fact was previously not documented in the Programmer's Guide. The most recent version available from Infor has been improved on this point.

I have not used db.lock yet because there was never a need to.

But the following might work:


commit.transaction()
if db.lock.table(ttisfa111) <> 0
then
message("Cannot lock table ttisfa111, please try after some time.")
choice.again()
endif

db.retry.point()
tisfa111.plan = 0
tisfa111.item = "TEST12"
tisfa111.quan = 12
db.insert(ttisfa111, db.retry, db.skip.dupl)
commit.transaction()


Should not be a need to use a loop.

Again, if this is the only record you are inserting, there should not even be the need to lock the table.

What database you use? Also why do you want to lock the whole table? Just curious..

We have informix as the our database
I want to lock the whole table as i dont want anyone to write or update any record while i am uploading data in the table

I think I see what they are trying to. Like this - and yes it might be necessary to put it in a loop.


| Try 20 times to lock the table before returning the error.
i=0
While db.lock.table(sometable) <> 0 do
i = i +1
suspend(3000)
if i > 20 then
message("can't lock table try again."
choice.again()
endif
endwhile

The question is where did you put the code - post the whole script. Is a transaction already opened? To me you do not want this type process in a maintain type session - let standard baan do locking. This would be in like a processing session in maybe choice.cont.process. I would try that first. I have just never done this so I would just make sure this code is in a section that does not involve any table transactions(like insert, update, delete) of a maintain session. If in a maintain session I would probably add a button to activate the code to test. Just my humble opinion.

You can also try doing a commit.transaction() right before the table lock. I don't like this because I do not know the rest of you code and what you might be committing or really doing.