SQL table lock, exclusive mode

[Mr. K.V.B.L.]

So late last week it occurred to me that I have a concurrency issue
with a table. I wonder what the best method is to fix it.

The basic flow of the program(s):

SELECT a value from the table
UPDATE that value with value + 1

so I feel that these two steps should be locked and both completed
before the next program can access the table.
So you can use this...
LOCK TABLE <table> IN EXCLUSIVE MODE

What I don't understand is that there is no unlock mechanism, except
that some 'unit of work' is completed.

Presently these programs don't use any commitment control, everything
is auto-commit.

Looking at
http://publib.boulder.ibm.com/infocenter/iseries/v6r1m0/index.jsp?topic=/sqlp/rbafycncr.htm

there is some mention of COMMIT(*RR). I can't find this option with
CHGPF or OVRDBF.

[CRPence]

On 06-Mar-2012 12:51 , Mr. K.V.B.L. wrote:
> So late last week it occurred to me that I have a concurrency issue
> with a table. I wonder what the best method is to fix it.
>
> The basic flow of the program(s):
>
> SELECT a value from the table
> UPDATE that value with value + 1
>
> so I feel that these two steps should be locked and both completed
> before the next program can access the table. So you can use this...
> LOCK TABLE<table> IN EXCLUSIVE MODE
>
> What I don't understand is that there is no unlock mechanism, except
> that some 'unit of work' is completed.
>
> Presently these programs don't use any commitment control,
> everything is auto-commit.
>
> Looking at

> http://publib.boulder.ibm.com/infocenter/iseries/v6r1m0/topic/sqlp/rbafycncr.htm

> there is some mention of COMMIT(*RR). I can't find this option with
> CHGPF or OVRDBF.

Commitment control, the COMMIT level, is scoped to the job or
activation group; i.e. neither part of a database file [i.e. no relation
to CHGPF] nor overrides [i.e. no relation to OVRDBF].

Since the LOCK is dropped when the transaction is rolled back
[ROLLBACK] or committed [COMMIT], the LOCK TABLE does not fit well
within the autocommit concept. FWiW the CL command DLCOBJ can effect
the "UNLOCK"; e.g.: dlcobj ((the_table *file *excl))

Why not just use the FOR UPDATE clause on the SELECT and WHERE
CURRENT OF clause on the UPDATE after a FETCH of the selected row?

Depending upon the requirements and what is actually being effected
by the noted update, the "flow" might better be achieved by an
auto-increment [aka IDENTITY] column or more likely a Sequence object.
/db2/rbafzcsequence.htm
_Sequences_
http://publib.boulder.ibm.com/infocenter/iseries/v6r1m0/topic/db2/rbafzsequences.htm
"
A sequence is a stored object that simply generates a sequence of
numbers in a monotonically ascending (or descending) order. Sequences
provide a way to have the database manager automatically generate unique
integer and decimal primary keys, and to coordinate keys across multiple
rows and tables.

A sequence can be used to exploit parallelization, instead of
programmatically generating unique numbers by locking the most recently
used value and then incrementing it.
...
"
_i DB2 for i5/OS SQL: Creating and using sequences i_
http://publib.boulder.ibm.com/infocenter/iseries/v6r1m0/topic/sqlp/rbafysequence.htm
_Sequence reference_
http://publib.boulder.ibm.com/infocenter/iseries/v6r1m0/topic/db2/rbafzseqref.htm
_i CREATE SEQUENCE i_
http://publib.boulder.ibm.com/infocenter/iseries/v6r1m0/topic

Regards, Chuck

[Mr. K.V.B.L.]

On Mar 6, 3:42 pm, CRPence <CRPe...@vnet.ibm.com> wrote:
> On 06-Mar-2012 12:51 , Mr. K.V.B.L. wrote:
>
>
>
>
>
>
>
>
>
> > So late last week it occurred to me that I have a concurrency issue
> > with a table. I wonder what the best method is to fix it.
>
> > The basic flow of the program(s):
>
> >   SELECT a value from the table
> >   UPDATE that value with value + 1
>
> > so I feel that these two steps should be locked and both completed
> > before the next program can access the table. So you can use this...
> > LOCK TABLE<table>  IN EXCLUSIVE MODE
>
> > What I don't understand is that there is no unlock mechanism, except
> > that some 'unit of work' is completed.
>
> > Presently these programs don't use any commitment control,
> > everything is auto-commit.
>
> > Looking at

> >http://publib.boulder.ibm.com/infocenter/iseries/v6r1m0/topic/sqlp/rb...

> > there is some mention of COMMIT(*RR). I can't find this option with
> > CHGPF or OVRDBF.
>
>    Commitment control, the COMMIT level, is scoped to the job or
> activation group; i.e. neither part of a database file [i.e. no relation
> to CHGPF] nor overrides [i.e. no relation to OVRDBF].
>
>    Since the LOCK is dropped when the transaction is rolled back
> [ROLLBACK] or committed [COMMIT], the LOCK TABLE does not fit well
> within the autocommit concept.  FWiW the CL command DLCOBJ can effect
> the "UNLOCK"; e.g.: dlcobj ((the_table *file *excl))
>
>    Why not just use the FOR UPDATE clause on the SELECT and WHERE
> CURRENT OF clause on the UPDATE after a FETCH of the selected row?
>
>    Depending upon the requirements and what is actually being effected
> by the noted update, the "flow" might better be achieved by an
> auto-increment [aka IDENTITY] column or more likely a Sequence object.
> /db2/rbafzcsequence.htm

> _Sequences_http://publib.boulder.ibm.com/infocenter/iseries/v6r1m0/topic/db2/rba...

> "
> A sequence is a stored object that simply generates a sequence of
> numbers in a monotonically ascending (or descending) order. Sequences
> provide a way to have the database manager automatically generate unique
> integer and decimal primary keys, and to coordinate keys across multiple
> rows and tables.
>
> A sequence can be used to exploit parallelization, instead of
> programmatically generating unique numbers by locking the most recently
> used value and then incrementing it.
> ...
> "

> _i DB2 for i5/OS SQL: Creating and using sequences i_http://publib.boulder.ibm.com/infocenter/iseries/v6r1m0/topic/sqlp/rb...
> _Sequence reference_http://publib.boulder.ibm.com/infocenter/iseries/v6r1m0/topic/db2/rba...
> _i CREATE SEQUENCE i_http://publib.boulder.ibm.com/infocenter/iseries/v6r1m0/topic
>
> Regards, Chuck

I don't know about the sequence thing, but I'll check it out. The
database table is already defined and in production. There are two
fields for each row in this table: an account number and transaction
number. The account number is key, so a record lock would work here
instead of a whole table lock. I'll check the SELECT FOR UPDATE
thing. I've not used that before. The the record, I'm using SQL CLI
here underneath a C++ wrapper I wrote myself.

[Mr. K.V.B.L.]

On Mar 6, 4:42 pm, CRPence <CRPe...@vnet.ibm.com> wrote:
> On 06-Mar-2012 12:51 , Mr. K.V.B.L. wrote:
>
>    Commitment control, the COMMIT level, is scoped to the job or
> activation group; i.e. neither part of a database file [i.e. no relation
> to CHGPF] nor overrides [i.e. no relation to OVRDBF].

But if a lock is placed on a file or record, aren't other jobs
prevented from using that file/record in spite of their own commitment
level? If I do a LOCK TABLE IN EXCLUSIVE MODE, other jobs are
prevented from using that table, no matter their commitment control
level.

>    Since the LOCK is dropped when the transaction is rolled back
> [ROLLBACK] or committed [COMMIT], the LOCK TABLE does not fit well
> within the autocommit concept.  FWiW the CL command DLCOBJ can effect
> the "UNLOCK"; e.g.: dlcobj ((the_table *file *excl))
>
>    Why not just use the FOR UPDATE clause on the SELECT and WHERE
> CURRENT OF clause on the UPDATE after a FETCH of the selected row?

With these statements, you need to be using cursors, correct? I'm
using SQLCLI, FWIW.

Thanks!

[Franco Lombardo]

As Chuck said, the best solution would use a IDENTITY column or a sequence
object, but, if you can't and you have an UNIQUE constraint on your field,
why don't you consider a different approach:

SELECT a value from the table

loop:

UPDATE that value with value + 1

IF duplicate record error goto LOOP

Bye

Franco


--
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
http://www.francolombardo.net
Scala, Java, As400.....
http://twitter.com/f_lombardo
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

[CRPence]

On 13-Mar-2012 14:25 , Mr. K.V.B.L. wrote:
> On Mar 6, 4:42 pm, CRPence<CRPe...@vnet.ibm.com> wrote:
>> On 06-Mar-2012 12:51 , Mr. K.V.B.L. wrote:
>>
>> Commitment control, the COMMIT level, is scoped to the job or
>> activation group; i.e. neither part of a database file [i.e. no
>> relation to CHGPF] nor overrides [i.e. no relation to OVRDBF].
>
> But if a lock is placed on a file or record, aren't other jobs
> prevented from using that file/record in spite of their own
> commitment level?

If an update lock is placed on a row, then no other job can use that
row. If there is a read lock on the row, then the ability of another
job to see the row depends upon its locking requirements; i.e. its
isolation or commitment control level.

> If I do a LOCK TABLE IN EXCLUSIVE MODE, other jobs
> are prevented from using that table, no matter their commitment
> control level.

Effectively. Other jobs are prevented from using the data in that
TABLE; prevented from most if not all DML and DDL. Some OS
database-related and object-related actions will be allowed, and maybe
at least the SQL DESCRIBE TABLE, because the TABLE is accessible for
review but not the data [or the /member/ object in which the data resides].

My previous reply however, was not meant to imply anything about the
effect of those locks with regard to [or as noted above, irrespective
of] the commitment control. I was trying to explain that the CmtCtl was
not implemented via either of CHGPF or OVRDBF, that instead the
commitment control was defined as part of an activation group. So
trying to find more information about isolation by looking there [at
CHGPF or OVRDBF, as if isolation were an attribute of the database
*FILE] would be fruitless; the commitment control is started implicitly
by the ISOLATION LEVEL defined for the SQL OPTION [dynamically or part
of a compile], or via the STRCMTCTL CL command, either of which defines
the scope.

>> Since the LOCK is dropped when the transaction is rolled back
>> [ROLLBACK] or committed [COMMIT], the LOCK TABLE does not fit well
>> within the autocommit concept. FWiW the CL command DLCOBJ can
>> effect the "UNLOCK"; e.g.: dlcobj ((the_table *file *excl))
>>
>> Why not just use the FOR UPDATE clause on the SELECT and WHERE
>> CURRENT OF clause on the UPDATE after a FETCH of the selected row?
>
> With these statements, you need to be using cursors, correct? I'm
> using SQLCLI, FWIW.
>

Do the work in a stored procedure. The ODBC makes the CALL to the
stored procedure with the necessary input parameters and the output
parameter that defines the new value to be returned.

Regards, Chuck

[CRPence]

On 07-Mar-2012 13:47 , Mr. K.V.B.L. wrote:
>
> I don't know about the sequence thing, but I'll check it out. The
> database table is already defined and in production.

The sequence object is separate from the TABLE. An IDENTITY could be
defined in a separate table, but the scenario in the original message,
as further clarified and then included below, probably not a good fit.
I am not sure a sequence is either. Too little information to know for
sure.

> There are two fields for each row in this table: an account number
> and transaction number. The account number is key, so a record lock
> would work here instead of a whole table lock.

I will refer to the TABLE as TBL, the account number as AN, and the
transaction number as TN.

>>>> The basic flow of the program(s):
>>>>
>>>> SELECT a value from the table
>>>> UPDATE that value with value + 1
>>>>
>>>> so I feel that these two steps should be locked and both
>>>> completed before the next program can access the table. So you
>>>> can use this...
>>>>
>>>> LOCK TABLE <table> IN EXCLUSIVE MODE

I had originally alluded that LOCK TABLE was not a /good fit/ with
autocommit active, although the DLCOBJ CL command could remove the lock
if necessary; i.e. for lack of a COMMIT to do so [or if the LOCK were
dropped, depending on the implementation of autocommit; isolation *none
or otherwise].

I then alluded that a row lock by FOR UPDATE could be used instead
with FETCH and UPDATE WHERE CURRENT of; though unstated, like the
DLCOBJ, the work would would need to be done in a stored procedure.

The following is possible from the client... albeit like many things,
might best be done by a stored procedure on the server to limit the
activity betwixt:

pseudo-code:
attemp_count = 0
do while attempt_count < excessive_attempt_count
select TN from TBL where AN=:account
INTO :value
update TBL set TN=:value+1
where AN=:account
and TN=:value /* ensure same tuple obtained by select */
if row-not-found /* some other work already incremented! */
then increment attempt_count
else leave
end do
if attempt_count >= excessive_attempt_count
then issue error

> I'll check the SELECT FOR UPDATE thing. I've not used that before.
> The the record, I'm using SQL CLI here underneath a C++ wrapper I
> wrote myself.

With autocommit active, the work would have to be performed in just
one statement. As responded in another message just recently, and
alluded to again above, use the SQL CALL statement to invoke a stored
procedure which is defined to do the necessary work.

Regards, Chuck