On Thursday 20 June 2002 17:50, Cal Evans wrote: What you want to do is wrap the update in a transaction with transaction isolation level set to SERALIZABLE. This should insure that whichever client is first to initiate the transaction will have effectively exclusive access to the record in question. Thus something like.
SET AUTOCOMMIT=0 SET TRANSACTION ISOLATION LEVEL SERIALIZABLE BEGIN SELECT .... UPDATE .... COMMIT If both clients do this, then I believe you should insure that whichever client gets to the database first will be able to read the record and update it before the other client even gets to see the data. Note however that effectively what is happening is that a lock is being set on ALL the rows in the SELECT clause, so every other access to the table will block until the transaction completes. It can, needless to say, have a negative effect on database performance... > Short answer: > You don't. That's not the way SQL databases work. When you hear of 'row > level locking' it means something different than you are used to if you are > coming from Access/Foxpro. > > If you are using InnoDB tables then you can: > BEGIN TRANS > statement > statement > statement > COMMIT or ROLLBACK > > > Long answer: > If you are coming from a FoxPro background (as I am) then you are used to > being able to issue a RLOCK() and the database will place a lock on that > record not allowing anyone else to touch it into you are done with it. (If > your background is not FoxPro then I'm sure the desktop DB you are using > has something similar). > > You don't do that in SQL. > > However, in times past, when I've felt the overwhelming urge to regress, I > have built systems that have a LOCK field. (i.e. userLock varChar(10)) > Then when someone wanted to 'lock' the record, I would write their userID > in the lock field with an update statement. > > Then before granting someone else a lock on the record, I would check to > make sure that someone else didn't already have it locked. The downside to > this methodology is that you are increasing your hits on the database > server. If your application is not busy then it's not a major deal. If, > however, you have a busy server, this will cause it to be busier. > > Once the user with the lock issues their UPDATE statement then I issue a > second UPDATE to clear the lock (or if you want to get fancy, clear it in > the first UPDATE with a userLock='') > > Anyhow, if you study how databases are built in SQL you won't find a lot of > this type of programming. I now rely much more on transactions to keep > things straight. Yes, this means that two users could issue updates almost > at the same time. Last one to the database wins. > > HTH, > =C= > > -----Original Message----- > From: Silmara Cristina Basso [mailto:[EMAIL PROTECTED]] > Sent: Wednesday, June 19, 2002 1:10 PM > To: [EMAIL PROTECTED] > Subject: locking on row level ... > > > I'm newbie MySQL and I'm using MySQL-max 4.0.1(Innodb), with one > application developed in Delphi and connect through MyODBC. The question is > ... How can i do to lock one row so that it is editing in the application? > > > --------------------------------------------------------------------- > Before posting, please check: > http://www.mysql.com/manual.php (the manual) > http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > > > > --------------------------------------------------------------------- > Before posting, please check: > http://www.mysql.com/manual.php (the manual) > http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail > <[EMAIL PROTECTED]> Trouble > unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
