Page 1 of 1

how do i lock a certain row in database?

Posted: Sun Jan 08, 2006 9:35 pm
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.

Posted: Sun Jan 08, 2006 10:15 pm
by feyd
that would depend on if your version of MySQL supports it, or the table type supports it. More details would be helpful.

Posted: Sun Jan 08, 2006 10:34 pm
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

Posted: Sun Jan 08, 2006 10:55 pm
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

Posted: Mon Jan 09, 2006 11:33 pm
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,

Posted: Mon Jan 09, 2006 11:50 pm
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.

Posted: Tue Jan 10, 2006 12:39 am
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!

Posted: Tue Jan 10, 2006 12:53 am
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

Posted: Tue Jan 10, 2006 2:46 am
by khaki_monster
yeah, tanx. now im trying to figuring out some solutions. :)

Posted: Tue Jan 10, 2006 7:51 am
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.... :?