Page 1 of 1

MySQL and row locking

Posted: Wed Jul 28, 2004 3:49 pm
by visionmaster
Hello,

I have a table where data can be changed by a call center agent as well as from a user who my want to change his/her data.

To avoid any collisions, the table row being changed or updated must be locked and my only be change by the "owner"? How do I realize this?

A extra field in my table row which marks a flag. So when a cca logs in and changes something I write 1 into the field. If a user logs in and wants to change something I write a 2 into the field. A 0 means this row is free and has no owner.

Or is there a mysql method I could use?

Thanks!

Posted: Wed Jul 28, 2004 3:58 pm
by lostboy

Posted: Wed Jul 28, 2004 4:35 pm
by visionmaster
lostboy wrote:use the lock table sql

http://dev.mysql.com/doc/mysql/en/LOCK_TABLES.html
Can't I lock a specific row. I can't for sure lock the whole table, that is of course impossible!

Another solution, no row locking possible?

Regards

Posted: Wed Jul 28, 2004 6:11 pm
by feyd
I think what you proposed should work fine as long as the updates/inserts and things are not delayed..

Posted: Wed Jul 28, 2004 7:03 pm
by lostboy
rowlocking only works on innodb table types

Posted: Thu Jul 29, 2004 3:11 am
by visionmaster
Hi,
feyd wrote:I think what you proposed should work fine as long as the updates/inserts and things are not delayed..
What exactly do you mean with "not delayed"? Could you explain that?
Thanks.

Posted: Thu Jul 29, 2004 3:16 am
by feyd

Posted: Thu Jul 29, 2004 6:40 am
by Weirdan
well, there's SQL function GET_LOCK

Re: MySQL and row locking

Posted: Thu Jul 29, 2004 11:00 am
by ultraslacker
visionmaster wrote:A extra field in my table row which marks a flag. So when a cca logs in and changes something I write 1 into the field. If a user logs in and wants to change something I write a 2 into the field. A 0 means this row is free and has no owner.

Or is there a mysql method I could use?
You cannot determine the order of table writes when there is a degree of concurrency, so you are better off using transactions than a column hack. Under mysql, this means the INNODB type mentioned above.