how do i lock a certain row in database?

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
User avatar
khaki_monster
Forum Commoner
Posts: 73
Joined: Tue Oct 11, 2005 12:36 am
Location: Philippines
Contact:

how do i lock a certain row in database?

Post by khaki_monster »

hi guyz!

how do i lock a certain row in a MySql database so that no one can access that particular row specially if someone is already having control on the said row(s)?

cheerz

khaki.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

that would depend on if your version of MySQL supports it, or the table type supports it. More details would be helpful.
User avatar
khaki_monster
Forum Commoner
Posts: 73
Joined: Tue Oct 11, 2005 12:36 am
Location: Philippines
Contact:

Post by khaki_monster »

im using latest version of MySQL. i cant give much more details with regards of mysql since im in a bit of confusion and as a starter with
the use of PHP/ MySQL. :)

it would be nice of you if you could give me some article or examples regarding locking rows or tables.

anyways, thanx for the reponse...

cheerz,

khaki
josh
DevNet Master
Posts: 4872
Joined: Wed Feb 11, 2004 3:23 pm
Location: Palm beach, Florida

Post by josh »

Do you have phpmyadmin?

When I list all my tables it's shows me:

Code: Select all

Table   	  Action   	  RecordsMay be approximate. See FAQ 3.11   	  [b]Type[/b]   	  Collation   	  Size   	  Overhead
"type" will show you what the table type is.



What kind of locking needs to be done, is it something that can be done internally within your application with a "lock" field (ie, lock the whole table, check that the "lock" field is not set to 1, if all is good set it 1 and release the table-wide lock)

---
oh yeah, here - http://dev.mysql.com/doc/refman/5.0/en/lock-tables.html
User avatar
khaki_monster
Forum Commoner
Posts: 73
Joined: Tue Oct 11, 2005 12:36 am
Location: Philippines
Contact:

Post by khaki_monster »

by what your saying locking table - do you literaly mean LOCKING the whole table? i only need to lock a certained row.
my case is, im doing a CMS for my project. problem is what if someone is accessing row 3 while im doing for row 8 for editing.

would it be a conflict of control? since by locking table will keep some other user away? :)

hhmm... im confuse 8O

pls... more explaination here.

cheerz,

khaki,
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

Since such a system will likely be distributed over many systems, using a database specific control such as locking of a row isn't likely the most optimum direction. Instead, using your own locking mechanism that's portable across all installations may be better. Something as simple as having a field in the record that signifies a lock is on the record (probably best to note who locked it).. I would simply make the field a reference to the userid that's locking it. If the field is null/empty, there's no lock. Simple enough to build and should be database independant, as the controls are in your code's hands now.
User avatar
khaki_monster
Forum Commoner
Posts: 73
Joined: Tue Oct 11, 2005 12:36 am
Location: Philippines
Contact:

Post by khaki_monster »

so, i should make my own script to control the locking method? weh!. I guess time for me to do some scracth huh?
haha... oh' will :)

tanx again pepz!

cheerz!
josh
DevNet Master
Posts: 4872
Joined: Wed Feb 11, 2004 3:23 pm
Location: Palm beach, Florida

Post by josh »

That's exactly what I suggested, except you need to lock the whole table with write access before you check for the lock, to prevent two users from trying to lock the same row at the same exact instant, the entire table would only be locked long enough to set a field that says that row is "locked", you lock the whole table with write access, THEN (since now only the script with the lock can modify) you make sure no one else has the row "locked", if not you set your appropriate values and then release the table wide lock...

I hope you get what I'm saying
User avatar
khaki_monster
Forum Commoner
Posts: 73
Joined: Tue Oct 11, 2005 12:36 am
Location: Philippines
Contact:

Post by khaki_monster »

yeah, tanx. now im trying to figuring out some solutions. :)
User avatar
raghavan20
DevNet Resident
Posts: 1451
Joined: Sat Jun 11, 2005 6:57 am
Location: London, UK
Contact:

Post by raghavan20 »

i am wondering normally every database has its own mechanising of locking coarse objects to granular objects....database, tables, fields, records...to a set of records ....all these are done by the database itself. I do not understand why one would want to lock something like a record.... :?
Post Reply