Re: Pessimistic Record Locking

[SGreen]

Scott Klarenbach [EMAIL PROTECTED] wrote on 04/29/2005 05:39:36 
PM:

 Thanks Shawn, that helps a lot.  I like your general idea of handling
 it at application level.  I guess my main concern w/ web apps and
 locking the record (even at app level) is orphaned locks, resulting
 from browser closes or other events that don't go through the normal
 channels of updating or cancelling the update.
 
 So, if you set a lock to have a timeout of say 15 minutes, and the
 user WAS simply taking longer than that 15 minutes, how do you handle
 that when they try and save it?
 
 I was thinking, just go ahead with the commit if the record hasn't
 been locked in the meantime by another user, otherwise, prompt them
 saying it's now been locked by another user and your changes won't be
 saved.  But this is the same user inconvenience caused by optomistic
 locking.

That's basically what I do. Here's my general logic. It may or may not fit 
your situation.

If userA has a record locked and that lock has that timed out but userA 
still had the page open for editing, userB could follow along and change 
the same record (it appears to be unlocked because userA took too long to 
save their changes). If userA finally decides to save their changes, the 
application code detects that they no longer have a lock on the record 
(either because userB has the record locked or because the lock was 
cleared when userB released it) and offers userA two choices: restart the 
edit from the current state or abandon their changes. Either way, you 
avoid changing userB's update unintentionally.

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine


 
 Thanks,
 Scott.
 
 On 4/29/05, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote:
  
  
  Scott Klarenbach [EMAIL PROTECTED] wrote on 04/29/2005 
02:28:25
  PM:
  
  
   Hello,
   
   I'm using MySQL 5.0.3 Beta, and I'm hoping for some general
   tips/experience you guys may have had regarding optomistic vs
   pessimistic locking in a web app (PHP 5.0.3)
   
   I'm more of a windows programmer, and I've always implemented
   pessimistic over optomistic as it's much more professional and
   attractive to the end user.
   
   The problem as you know, is that web development makes pessimistic
   locking much more difficult, because of the user closing the 
browser,
   and a bunch of other factors I can't control.
   
   Question:  which type of locking do you usually implement in your 
web
   apps, and do you do it at a DB level or in your application layer?
   
   Any thoughts on a custom locking scheme (ie, a lock table that is
   written to with a user id and record id and timestamp)?
   
   Other solutions/suggestions are greatly appreciated.
   
   Thanks in advance.
   
   Scott.
   
  
  I don't use record locking until it's time to actually do something to 
the
  data. Even then it depends on what I am doing. As Mathias already 
suggested,
  InnoDB works better for this because it can lock individual rows and 
has
  full transactional support. 
  
  In my webapp the users do mostly reads (which do not require locks) 
and few
  edits. In order to prevent another user from editing the same record 
that
  someone else is already editing, I have added a field to those table 
that
  require concurrency checking and fill in that field with the
  application-login of the user requesting to edit the record. It looks
  something like this: 
  
  UPDATE datatable 
  SET mtxEditor = 'user_id' 
  WHERE pkid =  
  AND mtxEditor is null; 
  
  SELECT mtxEditor 
  FROM datatable 
  WHERE pkid = ; 
  
  If I get a match, then I allow the user to navigate to the edit web 
page,
  otherwise they get the view web page and a popup saying that 
therecord is
  already being edited by insert name here. That way I don't have 2 
users
  trying to make concurrent changes and the second or later users are 
told who
  has that record open so they can check with that person to see if they 
are
  done or if they just forgot to save their changes. 
  
  Now, if the user exits the page manually (the page gets the onunload 
event)
  or decides to cancel their edit, I request a page that cleares 
themtxEditor
  field. That works something like this: 
  
  UPDATE datatable 
  SET mtxEditor = null 
  WHERE pkid =  
  AND mtxEditor ='userid'; 
  
  When it comes time to apply the effects of the edit, I check the 
mtxEditor
  field one more time to make sure that nobody has hijacked the page or 
that
  the user didn't navigate away (causing their edit lock to go away) and 
come
  back (a user can't update the record unless they are the one editing 
it).
  It's not perfect but it works remarkably well for the 
application-level
  locking I need to provide. 
  
  One enhancement to this would be to provide a sunset timer. Whena 
user is
  assigned as the editor set a datetimefield to 10 or 20 minutes from 
NOW().
  If the user hasn't submitted their updates by then, they have to 
re-request
  to edit the page

Re: Pessimistic Record Locking

[Scott Klarenbach]

Thanks for your help, that's going to work great!
sk

On 5/2/05, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote:
 
 
 Scott Klarenbach [EMAIL PROTECTED] wrote on 04/29/2005 05:39:36
 PM:
 
  Thanks Shawn, that helps a lot.  I like your general idea of handling
  it at application level.  I guess my main concern w/ web apps and
  locking the record (even at app level) is orphaned locks, resulting
  from browser closes or other events that don't go through the normal
  channels of updating or cancelling the update.
  
  So, if you set a lock to have a timeout of say 15 minutes, and the
  user WAS simply taking longer than that 15 minutes, how do you handle
  that when they try and save it?
  
  I was thinking, just go ahead with the commit if the record hasn't
  been locked in the meantime by another user, otherwise, prompt them
  saying it's now been locked by another user and your changes won't be
  saved.  But this is the same user inconvenience caused by optomistic
  locking.
 
 That's basically what I do. Here's my general logic. It may or may not fit
 your situation. 
 
 If userA has a record locked and that lock has that timed out but userA
 still had the page open for editing, userB could follow along and change the
 same record (it appears to be unlocked because userA took too long to save
 their changes). If userA finally decides to save their changes, the
 application code detects that they no longer have a lock on the record
 (either because userB has the record locked or because the lock was cleared
 when userB released it) and offers userA two choices: restart the edit from
 the current state or abandon their changes. Either way, you avoid changing
 userB's update unintentionally. 
 
 Shawn Green
 Database Administrator
 Unimin Corporation - Spruce Pine 
 
 
  
 
  Thanks,
  Scott.
  
  On 4/29/05, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote:
   
   
   Scott Klarenbach [EMAIL PROTECTED] wrote on 04/29/2005
 02:28:25
   PM:
   
   
Hello,

I'm using MySQL 5.0.3 Beta, and I'm hoping for some general
tips/experience you guys may have had regarding optomistic vs
pessimistic locking in a web app (PHP 5.0.3)

I'm more of a windows programmer, and I've always implemented
pessimistic over optomistic as it's much more professional and
attractive to the end user.

The problem as you know, is that web development makes pessimistic
locking much more difficult, because of the user closing the browser,
and a bunch of other factors I can't control.

Question:  which type of locking do you usually implement in your web
apps, and do you do it at a DB level or in your application layer?

Any thoughts on a custom locking scheme (ie, a lock table that is
written to with a user id and record id and timestamp)?

Other solutions/suggestions are greatly appreciated.

Thanks in advance.

Scott.

   
   I don't use record locking until it's time to actually do something to
 the
   data. Even then it depends on what I am doing. As Mathias already
 suggested,
   InnoDB works better for this because it can lock individual rows and has
   full transactional support. 
   
   In my webapp the users do mostly reads (which do not require locks) and
 few
   edits. In order to prevent another user from editing the same record
 that
   someone else is already editing, I have added a field to those table
 that
   require concurrency checking and fill in that field with the
   application-login of the user requesting to edit the record. It looks
   something like this: 
   
   UPDATE datatable 
   SET mtxEditor = 'user_id' 
   WHERE pkid =  
   AND mtxEditor is null; 
   
   SELECT mtxEditor 
   FROM datatable 
   WHERE pkid = ; 
   
   If I get a match, then I allow the user to navigate to the edit web
 page,
   otherwise they get the view web page and a popup saying that therecord
 is
   already being edited by insert name here. That way I don't have 2
 users
   trying to make concurrent changes and the second or later users are told
 who
   has that record open so they can check with that person to see if they
 are
   done or if they just forgot to save their changes. 
   
   Now, if the user exits the page manually (the page gets the onunload
 event)
   or decides to cancel their edit, I request a page that cleares
 themtxEditor
   field. That works something like this: 
   
   UPDATE datatable 
   SET mtxEditor = null 
   WHERE pkid =  
   AND mtxEditor ='userid'; 
   
   When it comes time to apply the effects of the edit, I check the
 mtxEditor
   field one more time to make sure that nobody has hijacked the page or
 that
   the user didn't navigate away (causing their edit lock to go away) and
 come
   back (a user can't update the record unless they are the one editing
 it).
   It's not perfect but it works remarkably well for the application-level
   locking I need to provide. 
   
   One enhancement to this would

Pessimistic Record Locking

[Scott Klarenbach]

Hello,

I'm using MySQL 5.0.3 Beta, and I'm hoping for some general
tips/experience you guys may have had regarding optomistic vs
pessimistic locking in a web app (PHP 5.0.3)

I'm more of a windows programmer, and I've always implemented
pessimistic over optomistic as it's much more professional and
attractive to the end user.

The problem as you know, is that web development makes pessimistic
locking much more difficult, because of the user closing the browser,
and a bunch of other factors I can't control.

Question:  which type of locking do you usually implement in your web
apps, and do you do it at a DB level or in your application layer?

Any thoughts on a custom locking scheme (ie, a lock table that is
written to with a user id and record id and timestamp)?

Other solutions/suggestions are greatly appreciated.

Thanks in advance.

Scott.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Pessimistic Record Locking

[mathias fatene]

Hi,
Try to have optimistic locking in the database server (row level locking
a.k.a. innodb storage), et let your transactions managed by the server.
Any line of code like lock table will generate a very bad web
application performances.

You can add connection pooling if you want to manage total number of
users.

Best Regards

Mathias FATENE
 
Hope that helps
*This not an official mysql support answer
 


-Original Message-
From: Scott Klarenbach [mailto:[EMAIL PROTECTED] 
Sent: vendredi 29 avril 2005 20:28
To: My SQL
Subject: Pessimistic Record Locking


Hello,

I'm using MySQL 5.0.3 Beta, and I'm hoping for some general
tips/experience you guys may have had regarding optomistic vs
pessimistic locking in a web app (PHP 5.0.3)

I'm more of a windows programmer, and I've always implemented
pessimistic over optomistic as it's much more professional and
attractive to the end user.

The problem as you know, is that web development makes pessimistic
locking much more difficult, because of the user closing the browser,
and a bunch of other factors I can't control.

Question:  which type of locking do you usually implement in your web
apps, and do you do it at a DB level or in your application layer?

Any thoughts on a custom locking scheme (ie, a lock table that is
written to with a user id and record id and timestamp)?

Other solutions/suggestions are greatly appreciated.

Thanks in advance.

Scott.

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Pessimistic Record Locking

[SGreen]

Scott Klarenbach [EMAIL PROTECTED] wrote on 04/29/2005 02:28:25 
PM:

 Hello,
 
 I'm using MySQL 5.0.3 Beta, and I'm hoping for some general
 tips/experience you guys may have had regarding optomistic vs
 pessimistic locking in a web app (PHP 5.0.3)
 
 I'm more of a windows programmer, and I've always implemented
 pessimistic over optomistic as it's much more professional and
 attractive to the end user.
 
 The problem as you know, is that web development makes pessimistic
 locking much more difficult, because of the user closing the browser,
 and a bunch of other factors I can't control.
 
 Question:  which type of locking do you usually implement in your web
 apps, and do you do it at a DB level or in your application layer?
 
 Any thoughts on a custom locking scheme (ie, a lock table that is
 written to with a user id and record id and timestamp)?
 
 Other solutions/suggestions are greatly appreciated.
 
 Thanks in advance.
 
 Scott.
 

I don't use record locking until it's time to actually do something to the 
data. Even then it depends on what I am doing. As Mathias already 
suggested, InnoDB works better for this because it can lock individual 
rows and has full transactional support.

In my webapp the users do mostly reads (which do not require locks) and 
few edits. In order to prevent another user from editing the same record 
that someone else is already editing, I have added a field to those table 
that require concurrency checking and fill in that field with the 
application-login of the user requesting to edit the record. It looks 
something like this:

UPDATE datatable
SET mtxEditor = 'user_id'
WHERE pkid = 
AND mtxEditor is null;

SELECT mtxEditor
FROM datatable
WHERE pkid = ;

If I get a match, then I allow the user to navigate to the edit web 
page, otherwise they get the view web page and a popup saying that the 
record is already being edited by insert name here. That way I don't 
have 2 users trying to make concurrent changes and the second or later 
users are told who has that record open so they can check with that person 
to see if they are done or if they just forgot to save their changes.

Now, if the user exits the page manually (the page gets the onunload 
event) or decides to cancel their edit, I request a page that cleares the 
mtxEditor field. That works something like this:

UPDATE datatable
SET mtxEditor = null
WHERE pkid = 
AND mtxEditor ='userid';

When it comes time to apply the effects of the edit, I check the mtxEditor 
field one more time to make sure that nobody has hijacked the page or that 
the user didn't navigate away (causing their edit lock to go away) and 
come back (a user can't update the record unless they are the one editing 
it). It's not perfect but it works remarkably well for the 
application-level locking I need to provide. 

One enhancement to this would be to provide a sunset timer. When a user 
is assigned as the editor set a datetimefield to 10 or 20 minutes from 
NOW(). If the user hasn't submitted their updates by then, they have to 
re-request to edit the page. That way, in case someone manages to leave 
the page without tripping the onunload event (which would trigger the 
reset of the mtxEditor field) you still have a way of unlocking the 
record for the next user.

BTW, I use Hungarian notation only if a field is used more like a variable 
than the other data-related fields. In this case the 'mtx' is my shorthand 
for 'mutex' because that field is acting as a mutual exclusion flag to be 
read by the application. The name of the application user editing a record 
adds no useful value to the rest of the data stored on the table (if I 
listed the properties of the object being stored in the table, the editing 
user isn't one of them. Am I making sense?).

It's generally a BAD idea to lock any rows for longer than what is 
absolutely necessary to complete the transaction. That means you should 
not lock a row (at the database level) and wait for a user to update it 
before you release the lock. To do that completely ruins your database 
concurrency. Move that kind of lock into the application layer and manage 
your user-vs-user concurrency from there. 

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

Re: Pessimistic Record Locking

[Scott Klarenbach]

Thanks Shawn, that helps a lot.  I like your general idea of handling
it at application level.  I guess my main concern w/ web apps and
locking the record (even at app level) is orphaned locks, resulting
from browser closes or other events that don't go through the normal
channels of updating or cancelling the update.

So, if you set a lock to have a timeout of say 15 minutes, and the
user WAS simply taking longer than that 15 minutes, how do you handle
that when they try and save it?

I was thinking, just go ahead with the commit if the record hasn't
been locked in the meantime by another user, otherwise, prompt them
saying it's now been locked by another user and your changes won't be
saved.  But this is the same user inconvenience caused by optomistic
locking.

Thanks,
Scott.

On 4/29/05, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote:
 
 
 Scott Klarenbach [EMAIL PROTECTED] wrote on 04/29/2005 02:28:25
 PM:
 
 
  Hello,
  
  I'm using MySQL 5.0.3 Beta, and I'm hoping for some general
  tips/experience you guys may have had regarding optomistic vs
  pessimistic locking in a web app (PHP 5.0.3)
  
  I'm more of a windows programmer, and I've always implemented
  pessimistic over optomistic as it's much more professional and
  attractive to the end user.
  
  The problem as you know, is that web development makes pessimistic
  locking much more difficult, because of the user closing the browser,
  and a bunch of other factors I can't control.
  
  Question:  which type of locking do you usually implement in your web
  apps, and do you do it at a DB level or in your application layer?
  
  Any thoughts on a custom locking scheme (ie, a lock table that is
  written to with a user id and record id and timestamp)?
  
  Other solutions/suggestions are greatly appreciated.
  
  Thanks in advance.
  
  Scott.
  
 
 I don't use record locking until it's time to actually do something to the
 data. Even then it depends on what I am doing. As Mathias already suggested,
 InnoDB works better for this because it can lock individual rows and has
 full transactional support. 
 
 In my webapp the users do mostly reads (which do not require locks) and few
 edits. In order to prevent another user from editing the same record that
 someone else is already editing, I have added a field to those table that
 require concurrency checking and fill in that field with the
 application-login of the user requesting to edit the record. It looks
 something like this: 
 
 UPDATE datatable 
 SET mtxEditor = 'user_id' 
 WHERE pkid =  
 AND mtxEditor is null; 
 
 SELECT mtxEditor 
 FROM datatable 
 WHERE pkid = ; 
 
 If I get a match, then I allow the user to navigate to the edit web page,
 otherwise they get the view web page and a popup saying that the record is
 already being edited by insert name here. That way I don't have 2 users
 trying to make concurrent changes and the second or later users are told who
 has that record open so they can check with that person to see if they are
 done or if they just forgot to save their changes. 
 
 Now, if the user exits the page manually (the page gets the onunload event)
 or decides to cancel their edit, I request a page that cleares the mtxEditor
 field. That works something like this: 
 
 UPDATE datatable 
 SET mtxEditor = null 
 WHERE pkid =  
 AND mtxEditor ='userid'; 
 
 When it comes time to apply the effects of the edit, I check the mtxEditor
 field one more time to make sure that nobody has hijacked the page or that
 the user didn't navigate away (causing their edit lock to go away) and come
 back (a user can't update the record unless they are the one editing it).
 It's not perfect but it works remarkably well for the application-level
 locking I need to provide. 
 
 One enhancement to this would be to provide a sunset timer. When a user is
 assigned as the editor set a datetimefield to 10 or 20 minutes from NOW().
 If the user hasn't submitted their updates by then, they have to re-request
 to edit the page. That way, in case someone manages to leave the page
 without tripping the onunload event (which would trigger the reset of the
 mtxEditor field) you still have a way of unlocking the record for the next
 user. 
 
 BTW, I use Hungarian notation only if a field is used more like a variable
 than the other data-related fields. In this case the 'mtx' is my shorthand
 for 'mutex' because that field is acting as a mutual exclusion flag to be
 read by the application. The name of the application user editing a record
 adds no useful value to the rest of the data stored on the table (if I
 listed the properties of the object being stored in the table, the editing
 user isn't one of them. Am I making sense?). 
 
 It's generally a BAD idea to lock any rows for longer than what is
 absolutely necessary to complete the transaction. That means you should not
 lock a row (at the database level) and wait for a user to update it before
 you release the lock. To do that completely ruins

Re: Pessimistic Record Locking

[Scott Klarenbach]

I could set the user's session timeout to be the same duration as the
record lock timeout...that way, in any event where the user's lock
would have expired, he would have to log back into the system
anyway...but this may be inconvenient as well, as I know a lot of
user's could be idle for some time, and would be annoyed if they had
to log back in every time...

On 4/29/05, Scott Klarenbach [EMAIL PROTECTED] wrote:
 Thanks Shawn, that helps a lot.  I like your general idea of handling
 it at application level.  I guess my main concern w/ web apps and
 locking the record (even at app level) is orphaned locks, resulting
 from browser closes or other events that don't go through the normal
 channels of updating or cancelling the update.
 
 So, if you set a lock to have a timeout of say 15 minutes, and the
 user WAS simply taking longer than that 15 minutes, how do you handle
 that when they try and save it?
 
 I was thinking, just go ahead with the commit if the record hasn't
 been locked in the meantime by another user, otherwise, prompt them
 saying it's now been locked by another user and your changes won't be
 saved.  But this is the same user inconvenience caused by optomistic
 locking.
 
 Thanks,
 Scott.
 
 On 4/29/05, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote:
 
 
  Scott Klarenbach [EMAIL PROTECTED] wrote on 04/29/2005 02:28:25
  PM:
 
 
   Hello,
  
   I'm using MySQL 5.0.3 Beta, and I'm hoping for some general
   tips/experience you guys may have had regarding optomistic vs
   pessimistic locking in a web app (PHP 5.0.3)
  
   I'm more of a windows programmer, and I've always implemented
   pessimistic over optomistic as it's much more professional and
   attractive to the end user.
  
   The problem as you know, is that web development makes pessimistic
   locking much more difficult, because of the user closing the browser,
   and a bunch of other factors I can't control.
  
   Question:  which type of locking do you usually implement in your web
   apps, and do you do it at a DB level or in your application layer?
  
   Any thoughts on a custom locking scheme (ie, a lock table that is
   written to with a user id and record id and timestamp)?
  
   Other solutions/suggestions are greatly appreciated.
  
   Thanks in advance.
  
   Scott.
  
 
  I don't use record locking until it's time to actually do something to the
  data. Even then it depends on what I am doing. As Mathias already suggested,
  InnoDB works better for this because it can lock individual rows and has
  full transactional support.
 
  In my webapp the users do mostly reads (which do not require locks) and few
  edits. In order to prevent another user from editing the same record that
  someone else is already editing, I have added a field to those table that
  require concurrency checking and fill in that field with the
  application-login of the user requesting to edit the record. It looks
  something like this:
 
  UPDATE datatable
  SET mtxEditor = 'user_id'
  WHERE pkid = 
  AND mtxEditor is null;
 
  SELECT mtxEditor
  FROM datatable
  WHERE pkid = ;
 
  If I get a match, then I allow the user to navigate to the edit web page,
  otherwise they get the view web page and a popup saying that the record is
  already being edited by insert name here. That way I don't have 2 users
  trying to make concurrent changes and the second or later users are told who
  has that record open so they can check with that person to see if they are
  done or if they just forgot to save their changes.
 
  Now, if the user exits the page manually (the page gets the onunload event)
  or decides to cancel their edit, I request a page that cleares the mtxEditor
  field. That works something like this:
 
  UPDATE datatable
  SET mtxEditor = null
  WHERE pkid = 
  AND mtxEditor ='userid';
 
  When it comes time to apply the effects of the edit, I check the mtxEditor
  field one more time to make sure that nobody has hijacked the page or that
  the user didn't navigate away (causing their edit lock to go away) and come
  back (a user can't update the record unless they are the one editing it).
  It's not perfect but it works remarkably well for the application-level
  locking I need to provide.
 
  One enhancement to this would be to provide a sunset timer. When a user is
  assigned as the editor set a datetimefield to 10 or 20 minutes from NOW().
  If the user hasn't submitted their updates by then, they have to re-request
  to edit the page. That way, in case someone manages to leave the page
  without tripping the onunload event (which would trigger the reset of the
  mtxEditor field) you still have a way of unlocking the record for the next
  user.
 
  BTW, I use Hungarian notation only if a field is used more like a variable
  than the other data-related fields. In this case the 'mtx' is my shorthand
  for 'mutex' because that field is acting as a mutual exclusion flag to be
  read by the application. The name of the application user

Re: InnoDB, record locking question

[Gleb Paharenko]

Hello.



Use:

 set autocommit=0;

or begin a transaction before executing 'select ... lock in share mode'.

Commit the tramsaction to release lock.





Mojtaba Faridzad [EMAIL PROTECTED] wrote:

 Hi,

 I am trying to learn more about InnoDB to convert MyISAM to InnoDB. 

 according to MySQL document, I can lock a record like this:

 

 SELECT * FROM mytable WHERE id = '100' LOCK IN SHARE MODE;

 

 I run this query and showed a message to stop the screen (waiting) and on 

 the other computer I run the same query or even I updated the record (which 

 is locked by the other computer), but I didn't get any error. How does this 

 work? Did I miss anything here?

 

 My other question: if I lock a record with that command, then how I can 

 release the lock? I could find any command to release the lock!

 

 thanks 

 

 



-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Gleb Paharenko
 / /|_/ / // /\ \/ /_/ / /__   [EMAIL PROTECTED]
/_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.NET
   ___/   www.mysql.com




-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

InnoDB, record locking question

[Mojtaba Faridzad]

Hi,
I am trying to learn more about InnoDB to convert MyISAM to InnoDB. 
according to MySQL document, I can lock a record like this:

SELECT * FROM mytable WHERE id = '100' LOCK IN SHARE MODE;
I run this query and showed a message to stop the screen (waiting) and on 
the other computer I run the same query or even I updated the record (which 
is locked by the other computer), but I didn't get any error. How does this 
work? Did I miss anything here?

My other question: if I lock a record with that command, then how I can 
release the lock? I could find any command to release the lock!

thanks 

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Record Locking

[Mike Doanh Tran]

hi all,

Can someone show me how to lock a record in mySQL?
As far as my understanding goes, only innoDB tables support record locking
level.  I am currently using mySQL version 3.23 and my tables are in myISAM.

Thanks in advance for any assistance.

Mike

---

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Record Locking

[Zenzo]

  I dont know if you can lock entries in a table but a
 table you can.
 More info:

http://www.mysql.com/documentation/mysql/bychapter/manual_MySQL_Optimisation.html#Locking_Issues
 
 --- Mike Doanh Tran [EMAIL PROTECTED]
 wrote:
  hi all,
  
  Can someone show me how to lock a record in mySQL?
  As far as my understanding goes, only innoDB
 tables
  support record locking
  level.  I am currently using mySQL version 3.23
 and
  my tables are in myISAM.
  
  Thanks in advance for any assistance.
  
  Mike
  
 

---
  
  -- 
  MySQL General Mailing List
  For list archives: http://lists.mysql.com/mysql
  To unsubscribe:   
 

http://lists.mysql.com/[EMAIL PROTECTED]
  
 
 
 __
 Do you Yahoo!?
 Free Pop-Up Blocker - Get it now
 http://companion.yahoo.com/
 


__
Do you Yahoo!?
Free Pop-Up Blocker - Get it now
http://companion.yahoo.com/

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Record Locking

[Zenzo]

  I dont know if you can lock entries in a table but a
 table you can.
 More info:

http://www.mysql.com/documentation/mysql/bychapter/manual_MySQL_Optimisation.html#Locking_Issues
--- Mike Doanh Tran [EMAIL PROTECTED] wrote:
 hi all,
 
 Can someone show me how to lock a record in mySQL?
 As far as my understanding goes, only innoDB tables
 support record locking
 level.  I am currently using mySQL version 3.23 and
 my tables are in myISAM.
 
 Thanks in advance for any assistance.
 
 Mike
 

---
 
 -- 
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:   

http://lists.mysql.com/[EMAIL PROTECTED]
 


__
Do you Yahoo!?
Free Pop-Up Blocker - Get it now
http://companion.yahoo.com/

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Single Record Locking - Permanent?

[Rudy Metzger]

I would add a flag to the record (e.g. 'user_lock'). You then have to
evaluate this flag in your application. Or you can wait until 5.0 for
triggers where you then can do it in the DB (or maybe not, dunno how far
triggers will go).

Maybe MERGE tables could help you, not sure about this though. Split the
table in 2 subtables, one part with the user updateable data, the other
one with the manager only data. Then grant rights specific to this
tables. I do not know however which rights are used: the one you granted
to the individual tables or the one you granted to the merge table. You
would have to check that out yourself.

Cheers
/rudy

-Original Message-
From: Dan Ullom [mailto:[EMAIL PROTECTED] 
Sent: dinsdag 15 juli 2003 19:04
To: [EMAIL PROTECTED]
Subject: Single Record Locking - Permanent?

Is it possible to lock single records for all but a certain set of
users,
permanently?
The intention is to make old items permanently unchangeable by anyone
but
managers.

Thanks,
Dan Ullom
TechCentric
314-991-2594



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Single Record Locking - Permanent?

[Dan Ullom]

Is it possible to lock single records for all but a certain set of users,
permanently?
The intention is to make old items permanently unchangeable by anyone but
managers.

Thanks,
Dan Ullom
TechCentric
314-991-2594



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Single Record Locking - Permanent?

[otherguy]

2 methods I can think of:

1) Move the records into a different table, and set permissions 
accordingly (allow updates on for managers on that table)

2) Control authentication and access in your program.

-Cameron Wilhelm

On Tuesday, July 15, 2003, at 11:03  AM, Dan Ullom wrote:

Is it possible to lock single records for all but a certain set of 
users,
permanently?
The intention is to make old items permanently unchangeable by anyone 
but
managers.

Thanks,
Dan Ullom
TechCentric
314-991-2594


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]




--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Record Locking?

[randy]

Hi..

I'm new to this list, and I am sort of new to MySQL and PHP. I was
wondering, if you create an html-form based interface to a database, lets
say its a database of customers, how can you lock a record if one person has
it open? Lets say PersonA opens up Joe Blow's record on the intranet, and
seconds later, so does PersonB on the other side of the office. Is there any
way to prevent PersonB from making changes, and saving them, overwriting
what PersonA just updated?

If there is an obvious solution that I am missing, I apologize..

Thanks,

--Randy


-
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

Record Locking in InnoDB

[Kishor K]

Hi,

I am using jsp-mysal combination in my application.
In my case i need the second user accessing the record to edit to get a
error message this record is being edited by another user. try later if
the record is being edited by first user.

In the page i have used
i have set autocommit=0
then selected the record for update using
select * from mytable where id=3 for update;

now how will be i catch this so that second user can be informed about the
lock.


thanx in advance
kishor




_
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com


-
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

Record locking problems

[Paul Will]

I am experiencing problems with record locking on database updates.

When I use an Access update query to modify a record subsequent updates
to the same records result an Access 97 message stating that the record
is in use by another user. This happens if I am the sole user of the
database. It would appear that the MySQL server is not releasing it's
table locks correctly after an Access update query.

Unfortunately I am unable to pin down which component is the source of
the problem; MyODBC, Access97 or  a MySQL servere misconfiguration.



Software: MySQL server v3.22.21  o/s: SGI IRIX v6.5.9

  MyODBC v2.50.36 MS Windows 95

  MS Access 97MS Windows 95

Grateful for any help you can offer.

Thanks.

--
Paul Will
Daewoo Worthing Technical Centre
Tel:01903 227132
Fax:01903 227212

-
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