VB6/ADO/ClientSDK2.4 Record locking issue

-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 Hello all, I have developed an application for a client which seems to have one remaining issue: record locking in the one place I do an update. I'm trying to increment a value in a column on one row in a table that looks after 'the last used key value'. ie. a way to ensure a unique number is assigned to each newly added row in another table. My VB code has evolved over that past few weeks to the point where: 1. start a transaction 2. open a static recordset with server-side cursor and pessimistic record locking, (tried setting repeatable read somewhere too) 3. retrieve column value 4. increment it 5. modify the value 6. save the change 7. commit transaction 8. close recordset I'd have thought this would force other users to wait for the above steps to complete before they could do their own 8 steps - however it appears as though the database server is allowing other processes to retrieve the value anyway. We've installed the latest Client SDK (2.4) and verified that the server meets the minimum version required to support the SDK. Do you have any idea what I'm doing wrong ? - -- Steven Shearer Key-Access Technologies Inc. -----BEGIN PGP SIGNATURE----- Version: PGPfreeware 6.5.1 Int. for non-commercial use <http://www.pgpinternational.com>; iQA/AwUBOIQmGZIw7LbQ8LJgEQKn/QCdEJ8csfPqejMTv6Vnb9g7mipJvpsAoKer YBnNq7xucNCSYsnq7GJl1ech =Ht0j -----END PGP SIGNATURE-----

Step 2 should be a cursor FOR UPDATE. If that's not possible, you could try modifying your code as follows 1. start a transaction 2. Update column value increasing by 1. 3. retrieve column value 4. Use column value (Insert data row, etc). 5. commit transaction Rudy Steven Shearer wrote: > My VB code has evolved over that past few weeks to the point where: > 1. start a transaction > 2. open a static recordset with server-side cursor and pessimistic > record locking, (tried setting repeatable read somewhere too) > 3. retrieve column value > 4. increment it > 5. modify the value > 6. save the change > 7. commit transaction > 8. close recordset > > I'd have thought this would force other users to wait for the above > steps to complete before they could do their own 8 steps - however > it appears as though the database server is allowing other processes > to retrieve the value anyway. > ...

The isolation level you set in your client only affects your client. If another client is set to 'dirty read', you cannot stop it from reading your non-commited values. -- Bashar Chalabi CTL, London Steven Shearer <sshearer@metaman.com> wrote in message news:JtVg4.13843$Dv1.371191@news2.rdc1.on.home.com... > -----BEGIN PGP SIGNED MESSAGE----- > Hash: SHA1 > > Hello all, I have developed an application for a client which seems > to have one remaining issue: record locking in the one place I do > an update. > > I'm trying to increment a value in a column on one row in a table > that > looks after 'the last used key value'. ie. a way to ensure a unique > number is assigned to each newly added row in another table. > > My VB code has evolved over that past few weeks to the point where: > 1. start a transaction > 2. open a static recordset with server-side cursor and pessimistic > record locking, (tried setting repeatable read somewhere too) > 3. retrieve column value > 4. increment it > 5. modify the value > 6. save the change > 7. commit transaction > 8. close recordset > > I'd have thought this would force other users to wait for the above > steps to complete before they could do their own 8 steps - however > it appears as though the database server is allowing other processes > to retrieve the value anyway. > > We've installed the latest Client SDK (2.4) and verified that the > server > meets the minimum version required to support the SDK. > > Do you have any idea what I'm doing wrong ? > > - -- > Steven Shearer > Key-Access Technologies Inc. > > -----BEGIN PGP SIGNATURE----- > Version: PGPfreeware 6.5.1 Int. for non-commercial use > <http://www.pgpinternational.com>; > > iQA/AwUBOIQmGZIw7LbQ8LJgEQKn/QCdEJ8csfPqejMTv6Vnb9g7mipJvpsAoKer > YBnNq7xucNCSYsnq7GJl1ech > =Ht0j > -----END PGP SIGNATURE----- > > >