MySQL and row locking

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
visionmaster
Forum Contributor
Posts: 139
Joined: Wed Jul 14, 2004 4:06 am

MySQL and row locking

Post 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!
lostboy
Forum Contributor
Posts: 329
Joined: Mon Dec 30, 2002 8:12 pm
Location: toronto,canada

Post by lostboy »

visionmaster
Forum Contributor
Posts: 139
Joined: Wed Jul 14, 2004 4:06 am

Post 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
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

I think what you proposed should work fine as long as the updates/inserts and things are not delayed..
lostboy
Forum Contributor
Posts: 329
Joined: Mon Dec 30, 2002 8:12 pm
Location: toronto,canada

Post by lostboy »

rowlocking only works on innodb table types
visionmaster
Forum Contributor
Posts: 139
Joined: Wed Jul 14, 2004 4:06 am

Post 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.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Post by Weirdan »

well, there's SQL function GET_LOCK
ultraslacker
Forum Newbie
Posts: 3
Joined: Tue Jul 20, 2004 2:11 pm

Re: MySQL and row locking

Post 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.
Post Reply