hello
somebody faced such design?
delete from table
enddelete
commit.transaction ()

gives out an error of Error 107 (Record is locked)
though works at night

Not sure I understand. What do you mean it works at night? Are you locking the records before the delete? I have usually seen something like this when the commit.transaction was not doe at the right spot and the transaction was not ended successfully.

Hello,
why not ?

db.clear.table()
Syntax:

function long db.clear.table (long table_id [, long flag] [, long comp_nr])

Description


This deletes all data from a specified table. Reference counters are automatically updated.

By default, all deleted records are saved in rollback segments. Consequently, using this function to clear a large table can result in reduced performance and, in some cases, can cause overflow of the internal rollback segments.

Note that this function cannot be used within a transaction if the option NO.ROLLBACK is given; see Transaction handling.


Arguments

long table_id The table ID, as returned by db.bind().

[long flag ] Use this optional argument to indicate whether the delete operation must be performed as a single transaction or as multiple transactions. The possible values are:

0 WITH.ROLLBACK The table is cleared in a single transaction. All records are saved in rollback segments. This is the default option.
1 NO.ROLLBACK The table is cleared in one or more transactions, depending on the number of records in the table. Each transaction is automatically committed after 20 records have been deleted. The number of records deleted in the final transaction can be 20 or less; this depends on how many records remain to be deleted.

When you use this option, transactions are small and records are not saved in rollback segments.

When you use this option, you must place db.clear.table() at the start of the transaction.

The table is cleared in a single transaction. All records are saved in rollback segments. This is the default option.

The table is cleared in one or more transactions, depending on the number of records in the table. Each transaction is automatically committed after 20 records have been deleted. The number of records deleted in the final transaction can be 20 or less; this depends on how many records remain to be deleted.

When you use this option, transactions are small and records are not saved in rollback segments.

When you use this option, you must place db.clear.table() at the start of the transaction.

[long comp_nr ] This optional argument specifies a company number for the table. The default company is the company of the user.

Return values

0 success

<>0 error

Context

This function can be used in all script types.

about this db.clear.table I know. but it is necessary to remove records on a condition:
delete from tisfc001
where tisfc001.osta=1
enddelete
commit.transaction ()

My code would look like

db.retry.point()
select tisfc001.*
from tisfc001 for update
where tisfc001.osta=1
db.delete(ttisfc001, db.retry)
commit.transaction()
endselect

Now we can debate where to put the commit, but inside the select it will commit each record. This way if something errors out you can re-run it and not have to re-delete a bunch of records. Now depending on what I am doing I might commit every 50 or 100 records. But if I am delete 1000's of records I typically never commit after all the records.

Now we can debate where to put the commit, but inside the select it will commit each record. This way if something errors out you can re-run it and not have to re-delete a bunch of records. Now depending on what I am doing I might commit every 50 or 100 records. But if I am delete 1000's of records I typically never commit after all the records.
From the Design Principles Guide -

Size of database transactions
Problem
For optimal performance, database transactions must not be too small and not too large. However they must always be logical transactions.

Large transactions will also have relatively long locking times. Depending on the frequency of updates done by other users, this can cause locking problems.

Other problems can also occur, for example, run-time tools (bshell, driver, and so on) consume more memory and CPU, more rollback segments are needed in the RDBMS.

Solution
If possible, do not implement the commit.transaction() for each single update. On the other hand, if possible, do not implement the commit.transaction() for too many updates.

It is difficult to give hard numbers for this. The optimal transaction size depends on many factors.

Currently a general rule can be about 100 to 250 updates per commit. (The commit rate). Apply these numbers only if the chances are small that other users update the same records.

Example
Bad situation: one commit per updatedb.retry.point()

select table.*
from table for update
order by table._index1 with retry
selectdo
...
db.update(table, db.retry)
commit.transaction()
endselect
Bad situation: commit for (more than) 1000 updates

db.retry.point()
select table.*
from table for update
selectdo |* table can have more than
... |* 1000 rows !
db.update(table, db.retry)
endselect
commit.transaction()
Improved: commit per 100 updates

long number.of.updates

db.retry.point()
number.of.updates = 0
select table.*
from table for update
order by table._index1 with retry
selectdo
...
db.update(table, db.retry)
number.of.updates = number.of.updates + 1
if number.of.updates = 100 then
commit.transaction()
number.of.updates = 0
endif
selecteos
commit.transaction()
endselect
The selecteos section with the commit.transaction() is needed. It will be explained in help page 'implementation of selecteos'.

delete from table
enddelete
commit.transaction ()

gives out an error of Error 107 (Record is locked)
Rinkashiki,

To delete a record, you need exclusive access i.e., no other transaction has a lock on the same record.

It appears like you are trying to delete via MS-SQL than Baan/LN-SQL code-
To control the transaction size you can specify the number of rows or conditions etc. Refer to - SQL Server DELETE
(http://www.sqlservertutorial.net/sql-server-basics/sql-server-delete/)

thanks to all for answers. the matter is that year the program worked without problems. and now started giving out a mistake. I will test still