Re: Locking certain rows in a transaction
Perrin Harkins wrote: Assuming you're using InnoDB tables, SELECT...FOR UPDATE will lock the rows as you describe. It can prevent other inserts and updates to neighboring rows as well, depending on what isolation level you're running (default is REPEATABLE READ). Thanks, in fact it even does more than I expected. When another client tries to read a row previously selected by another client FOR UPDATE, he will read just fine. If he tries to select it with 'FOR UPDATE', he will have to wait. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Locking certain rows in a transaction
Hi, i'm currently experimenting with nested sets. To insert a new node,, I need 1 SELECT, 2 UPDATE and 1 INSERT statement. Of course all of this wii be packed into a transaction, because the table could get corrupted if not all of the mentioned queries are executed. Now here's the question: I need to lock certain rows, so that no other client can read or write that rows (I want those clients to wait until the transaction is commited or rolled back). I don't want to lock the entire table, since that would block to many clients, which never intended to read the critical rows. Please don't simply post a link to the documentation. I tried to read it, but I didn't find the solution. Example would be nice. Greets, peter -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Locking certain rows in a transaction
On Sat, Aug 9, 2008 at 8:10 AM, John Smith [EMAIL PROTECTED] wrote: Now here's the question: I need to lock certain rows, so that no other client can read or write that rows (I want those clients to wait until the transaction is commited or rolled back). I don't want to lock the entire table, since that would block to many clients, which never intended to read the critical rows. Assuming you're using InnoDB tables, SELECT...FOR UPDATE will lock the rows as you describe. It can prevent other inserts and updates to neighboring rows as well, depending on what isolation level you're running (default is REPEATABLE READ). - Perrin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
