LockPessimistic not working 2

[QatQat]

Hi there,

MySQL 4.0.12, ASP front-end, this is the setup.
All tables are InnoDB


If I use LockOptimistic in ASP it correctly locks the row and not the table however, due to a rather lenghty form to update records, it happens that two users try edit the same row.

If I use LockPessimistic it should prevent users from accessing the row once is called for edit from another user.

It works with MSSQL server. Why doesn't it work with MySQL?

According to InnoDB documentation the InnoDB engine supports row-level locking and all its features.

Any idea?

Thank you.


Qatqat

Life is what happens when you are making other plans.

 

[rzs0502]

If you perform a SELECT without FOR UPDATE or LOCK
IN SHARE MODE, the the SELECT is a consistent read and will not see the modifications made by
uncommitted or later transactions.

If you do not want a reading SELECT to proceed before your other transaction has done its update,
you have to use LOCK IN SHARE MODE or FOR UPDATE also in the reading select.

 

[QatQat]

I kow about that but according to the InnoDB reference, if you performe a UPDATE you don't need to use it. AS a matter of fact LockType Optimistic seems to work normally without the need to specify FOR UPDATE but it is not strict enough in my case.

From the InnoDB manual
.....
UPDATE ... SET ... WHERE ... : sets an exclusive lock on every record the search encounters
.....

Anyway, thanks for now, I will try to add it and check it out.

Bye

Qatqat

Life is what happens when you are making other plans.

 

[rzs0502]

I read an article once that stated that Pessimistic Locking was unavailable if your Cursor Location is on the Client...

Let me know your findings...

Thanks

 

[QatQat]

HI,

Unfortunately I am still not able to lock the row after the first user opens it for update.

I am using server-side cursor so there should not be a problem about using Pessimistic.

I am starting to think that it is not supported.

If you find any good reading about please post it. I will do the same.

Bye

Qatqat

Life is what happens when you are making other plans.

 

[rzs0502]

Extract from

http://www.vbmysql.com/articles/cursorsandlocks.html

adLockPessimistic
In a situation of high concurrency, with multiple users modifying the same data, you may need a pessimistic lock type. With asLockPessimistic, the underlying rows (or table) will be locked as soon as you begin making changes to the current record, and will not be unlocked until the Update method is called.

While this will ensure that you do not have overlapping changed with other users, it could cause performance issues, especially with a MyISAM table, with features table-level locking only. Make sure that the changes are immediately followed by the recordset's update method, and that there is no break for user input between a change and the update in order to ensure no long breaks (and potentially canceled locks by the database) in the operation of the database.

While adLockPessimistic has it's place, the same advice I gave regarding dynamic cursors applies: avoid it when possible, as it is very resource intensive and involves a lot more work both on the client and server side.

 

[QatQat]

Hi rzs0502,

it only works with dynamic cursor and not keyset.

That was my problem, I try to avoid dynamic cursor as much as possible but this time it seems like it is the only way.

Thanks for your time and cooperation.


Bye

Qatqat

Life is what happens when you are making other plans.

 

[rzs0502]

Thx QatQat... Very useful...