Instructs SQL commands to be generated with the FOR UPDATE OF option.

Syntax

SET FOR UPDATE ON|OFF

Comments

When connected to any SQL servers, the SQL syntax generated to perform operations on the remote server can be controlled to add the FOR UPDATE OF option on the primary key fields to SELECT commands.

The option ON turns this generation on and remains in effect until another command, this time with the option OFF, is issued. Therefore, all SELECT statements generated by Zim after the switch is turned on will have the FOR UPDATE OF clause until the switch is turned off.

This is useful to guarantee that records will be locked for update within the next SQL commands.

This option setting only applies to SELECT statements generated by Zim in order to perform actions on connected SQL servers.

Example 1

SET FOR UPDATE ON
SET SQLTRACE ON
FIND MyEntA
SELECT FieldA FROM MyEntA FOR UPDATE OF FieldA
FIND MyEntB
SELECT FieldB FROM MyEntB FOR UPDATE OF FieldB
SET FOR UPDATE OFF
FIND MyEntA
SELECT FieldA FROM MyEntA

The FIND statements between the ON and OFF settings will be sent to the connected SQL server with the FOR UPDATE OF clauses.

Example 2

The following Zim program shows a small procedure that takes a control number and then adds a record using this control number as a primary key. The numbers at left are for subsequent reference only.

1  Procedure Prog2() Local (vl_number, vl_1)
2  on deadlock
3          if $intransaction=$true
4                      out $concat(“record blocked – “, $trim(vl_1))
5                      halt
6                      goto trans1
7          else
8                      goto previous
9          endif
10 endon
11
12 trans1:
13 transaction
14 let vl_1=”Compute tblControl”
15 find tblControl -> s1
16 let vl_number=(LastNumber+1)
17 out vl_1
18 out vl_number
19 halt
20 let vl_1=”Add tblCust”
21 add tblcust let custcode = vl_number  
22                     custname = vl_number
23 out vl_1
24 halt
25 let vl_1=”Change tblControl”
26 ch tblControl let LastNumber = vl_number
27 out vl_1
28 halt
29 endtransaction
30
31 EndProcedure

Scenario 1

By default, SET FOR UPDATE is OFF. This is the behaviour found in previous versions of Zim.

If two users are running the same program, then:

. User 1 starts Prog2 and stops at line 19: Vl_number is 1 and no locks were applied by Oracle;

. User 2 starts Prog2 and stops at line 19: Vl_number is 1 and no locks were applied by Oracle;

. User 1 continues execution: a record will be added in TblCust and execution stops at line 24;

. User 2 continues execution: it waits because Oracle serializes the ADD statement in TblCust;

. User 1 continues execution: TblControl is updated, Oracle locks TblControl and execution is halted at line 28;

. User 2 is still waiting…

. User 1 continues execution: Oracle releases the lock in TblControl, the transaction is committed and the program ends;

. User 2 now proceeds: Oracle will try to add a record but a duplicated record error is raised.

Scenario 2

SET FOR UPDATE is set to ON either before calling Prog2 or in line 11 of Prog2:

. User 1 starts Prog2 and stops at line 19: Vl_number is 2 (assuming that a record was added in Scenario 1) and Oracle locks TblControl;

. User 2 starts Prog2, but enters in a waiting state at line 15, since Oracle serializes the SELECT statement (the record is locked);

. User 1 continues execution: the record will be added in TblCust, Oracle locks TblCust and the execution stops at line 24;

. User 2 still waiting…

. User 1 continues execution: TblControl is updated, Oracle locks TblControl and execution stops at line 28;

. User 2 still waiting…

. User 1 continues execution: Oracle releases the lock in TblControl, the transaction is committed and the program ends;

. User 2 resumes execution: Oracle retrieves a TblControl record, Vl_number becomes 3 and the transaction continues normally;