How to force a lock on a table

Discussion forum about all things older ANSI Team Developer versions.
narkosis
Malaysia
Posts: 2
Joined: 22 Nov 2017, 06:33
Location: Ipoh, Malaysia

How to force a lock on a table

Post by narkosis » 06 Jun 2018, 02:41

Development : TD 4.2 + SQLBase 9.01 on Win 10 64-bit
Deployment : Windows 7 Pro 64-bit on TCP/IP

I have this situation where users on 2 separate PCs save a transaction at the same time, and they are reading out the same primary key sequence number from a parameter table. Isolation level used is 'RL'. The basic logic of the sequence number generation is :

Call SqlPrepareAndExecute( hSqlTemp, 'select prefix, next_no, tktno_len from params where trx_type = :strTrxType into :strPrefix, :nNextNum, :nNumLen' )
If SqlFetchNext( hSqlTemp, nReturn )
If strTrxType = 'SQ'
Else If strTrxType = 'PQ'
Else If strTrxType = 'SY'
Else If strTrxType = 'PY'
Else If strTrxType = 'SD'
Else If strTrxType = 'PD'
Else
! for regular P, S and R
Call SqlPrepareAndExecute( hSqlTemp, 'update params set next_no = next_no + 1 where trx_type = :strTrxType' )

In this situation, both users are using strTrxType = 'P' so they're both hitting the last Else block. In my experience, the time between the Select statement above and the Update statement at the bottom is only a fraction of a split-second, even with that block of conditions which they'll both bypass. But there's no lockout happening and both users read out the same value from the table.

Is there a way to force a lock on a table at the point of a Select statement?

Thanks

Roger

FRBhote
India
Posts: 1233
Joined: 09 Mar 2017, 05:32
Location: Hyderabad, India

How to force a lock on a table

Post by FRBhote » 06 Jun 2018, 05:30

You should always do the update first so that you get a lock, and then the select. So I think it should be:

Call SqlPrepareAndExecute( hSqlTemp, 'select prefix, tktno_len from params where trx_type = :strTrxType into :strPrefix, :nNumLen' )
If SqlFetchNext( hSqlTemp, nReturn )
If strTrxType = 'SQ'
Else If strTrxType = 'PQ'
Else If strTrxType = 'SY'
Else If strTrxType = 'PY'
Else If strTrxType = 'SD'
Else If strTrxType = 'PD'
Else
! for regular P, S and R
Call SqlPrepareAndExecute( hSqlTemp, 'update params set next_no = next_no + 1 where trx_type = :strTrxType' )
Call SqlPrepareAndExecute( hSqlTemp, 'select next_no from params where trx_type = :strTrxType into :nNextNum')

Return to “General Discussion”

Who is online

Users browsing this forum: [Ccbot] and 0 guests