Re: Transactions and locking
Sequences, if I got that right, need the new value to be stored immediately, i.e. outside of an active transaction. This requires a second connection to the database which probably causes more implementation work for my web application. You mean real sequences? As with Oracle? Then no, you can use the values to whatever purpose you like. An Oracle/InterBase/Firebird/DB2/any decent DBMS Sequence is a separate metadata object with the only purpose to generate sequential numbers. Martijn Tonies Database Workbench - tool for InterBase, Firebird, MySQL, NexusDB, Oracle MS SQL Server Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com I don't need LOCK TABLES anymore now. And at last, I can say that this is indeed not a simple topic as I've thought and maybe I've read most of the related documentation now anyway... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Transactions and locking
Yves,
Did you read this reply I send earlier? I think it does what you
want without needing to lock anything, thus making it portable.
Damn, I found out that I need table locking *and* transactions.
What makes you say that?
BEGIN TRANSACTION
SELECT MAX(id) FROM table
INSERT INTO table (id) VALUES (?)
INSERT INTO othertable (id) VALUES (?)
COMMIT
First I find a new id value, then I do several INSERTs that need to be
atomic, and especially roll back completely if a later one fails.
That Perl module uses the exact technique I described to you with
updates and LAST_INSERT_ID().
AUTO_INCREMENT isn't portable. Now I only support MySQL and SQLite.
But I also did PostgreSQL (until it failed one of the more complex
queries, maybe it comes back one day) and maybe Oracle or whatever will
be compatible, too, so that I then stand there with my AUTO_INCREMENT
and can't use it.
I would suggest the following --
create a table called SEQUENCES:
create table SEQUENCES
( table_name varchar(128/maxlength of tablename) not null primary key,
sequence_value largeint not null) ;
Create a row for each table, eg:
insert into sequences values('CUSTOMERS', 0);
Next, whenever you want to get a new value, do:
select sequence_value as current_value
from sequences
where table_name = 'CUSTOMERS';
Next, do this:
update sequences
set sequence_value = sequence_value + 1
where sequence_value = your current value you just got
and table_name = 'CUSTOMERS'
Now, repeate the above sequence until the UPDATE statement
above says that it's updated 1 row. If it updated 0 rows, it means
someone else did it just before you.
Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, NexusDB, Oracle
MS SQL Server
Upscene Productions
http://www.upscene.com
My thoughts:
http://blog.upscene.com/martijn/
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com
--
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: Transactions and locking
On 14.11.2007 12:50 CE(S)T, Martijn Tonies wrote: Yves, Did you read this reply I send earlier? I think it does what you want without needing to lock anything, thus making it portable. I would suggest the following -- create a table called SEQUENCES: Yes, I've read it and actually put a flag on that message, but then I decided to go for the other flagged message that explained SELECT ... FOR UPDATE. I did some tests with multiple client windows and found that the locking is good enough. I use it for finding new ID values and for telling whether a new value is unique where UNIQUE constraints won't help me (because I want the values to be caseless unique e.g.). SELECT ... FOR UPDATE works fine when I always use the same function to access that table. It is supported by MySQL, PostgreSQL and Oracle. I only need a small workaround for SQLite (which gets the FOR UPDATE stripped off and instead requires the programmer to have started an EXCLUSIVE transaction before; else - Exception). Sequences, if I got that right, need the new value to be stored immediately, i.e. outside of an active transaction. This requires a second connection to the database which probably causes more implementation work for my web application. I don't need LOCK TABLES anymore now. And at last, I can say that this is indeed not a simple topic as I've thought and maybe I've read most of the related documentation now anyway... -- Yves Goergen LonelyPixel [EMAIL PROTECTED] Visit my web laboratory at http://beta.unclassified.de -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Transactions and locking
On 13.11.2007 01:04 CE(S)T, Perrin Harkins wrote: On Nov 12, 2007 6:47 PM, Yves Goergen [EMAIL PROTECTED] wrote: From what I've read about MySQL's table locks and InnoDB, you cannot use LOCK TABLES with transactions. Either of them deactivates the other one. Beginning a transaction unlockes all tables, locking tables ends a transaction. I don't think that's correct. At least that's not how I read this: http://dev.mysql.com/doc/refman/5.0/en/innodb-restrictions.html It sounds like you issue a LOCK TABLES at the beginning of your transaction, and doing a COMMIT unlocks the tables at the end. From that page: Sometimes it would be useful to lock further tables in the course of a transaction. Unfortunately, LOCK TABLES in MySQL performs an implicit COMMIT and UNLOCK TABLES. An InnoDB variant of LOCK TABLES has been planned that can be executed in the middle of a transaction. In any case, you only need to do a table lock long enough to insert a row into your first table. After that, you can release the lock. And when I insert the row in the first table but cannot do so in the second because of some invalid data, I need to also remove the first row again because it doesn't make sense alone. This is what transactions are for. I think I'll go for transactions and check the error code in most cases. Only where a custom check is needed, I'll lock the tables without using a transaction. I'll see how far I get with it. Oh, I see from that page above: All InnoDB locks held by a transaction are released when the transaction is committed or aborted. Thus, it does not make much sense to invoke LOCK TABLES on InnoDB tables in AUTOCOMMIT=1 mode, because the acquired InnoDB table locks would be released immediately. So, it seems that locking tables is *impossible* with InnoDB. Bad. The only thing I can do then is write the data and afterwards count if there are two of them. But this still isn't safe, in concurrency means. Any solution? May be a bug report? -- Yves Goergen LonelyPixel [EMAIL PROTECTED] Visit my web laboratory at http://beta.unclassified.de -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Transactions and locking
(For the record... I missed the mailing list recipient - again!!) On 13.11.2007 00:30 CE(S)T, Perrin Harkins wrote: On Nov 12, 2007 5:58 PM, Yves Goergen [EMAIL PROTECTED] wrote: First I find a new id value, then I do several INSERTs that need to be atomic, and especially roll back completely if a later one fails. If you use a table lock on the first table where you get the ID, you know that ID is safe to use. Using a table lock when you get the ID and then trusting transactions to roll back all the inserts in the event of a later failure should work fine. From what I've read about MySQL's table locks and InnoDB, you cannot use LOCK TABLES with transactions. Either of them deactivates the other one. Beginning a transaction unlockes all tables, locking tables ends a transaction. That Perl module uses the exact technique I described to you with updates and LAST_INSERT_ID(). AUTO_INCREMENT isn't portable. You're misunderstanding. The LAST_INSERT_ID() function doesn't use AUTO_INCREMENT. That's why the perl module uses it. It just copies the value you pass to it and makes that available without another select. I don't understand what you mean. It's not portable to SQLite, but you can use a sequence there instead. To my knowledge, SQLite doesn't support sequences either, only auto_increment. I've began to convert my code to evaluate error codes now, but I see the next problem already: At one place, I insert a row where two columns could potentially violate a uniqueness constraint. With just reading the error code, I can't figure out which of them caused the problem. The error message I can present to the user will be somewhat generic then. (Either this or that of your input already exists. Find out which one. Haha!) Maybe I'll use error codes or table locks depending on the situation. It's all a big hack, but so is databases (and portability) it seems. I'm not sure yet. It's late. -- Yves Goergen LonelyPixel [EMAIL PROTECTED] Visit my web laboratory at http://beta.unclassified.de -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Transactions and locking
Yves Goergen wrote: (For the record... I missed the mailing list recipient - again!!) On 13.11.2007 00:30 CE(S)T, Perrin Harkins wrote: On Nov 12, 2007 5:58 PM, Yves Goergen [EMAIL PROTECTED] wrote: First I find a new id value, then I do several INSERTs that need to be atomic, and especially roll back completely if a later one fails. If you use a table lock on the first table where you get the ID, you know that ID is safe to use. Using a table lock when you get the ID and then trusting transactions to roll back all the inserts in the event of a later failure should work fine. From what I've read about MySQL's table locks and InnoDB, you cannot use LOCK TABLES with transactions. Either of them deactivates the other one. Beginning a transaction unlockes all tables, locking tables ends a transaction. It's more complicated than that. You can use them together, you just have to do it like this: set autocommit = 0; begin; lock tables; -- you are now in a transaction automatically begun by LOCK TABLES . commit; -- your tables are now unlocked. In fact, you *must* use a transaction for LOCK TABLES to be safe, at least in MySQL 5. Even if you're using non-transactional tables. Otherwise, you can get nasty behavior. See http://bugs.mysql.com/bug.php?id=31479 The manual isn't very clear on the interaction between LOCK TABLES and transactions, it's true. But this is what I've found. Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Transactions and locking
On Nov 13, 2007 4:53 AM, Yves Goergen [EMAIL PROTECTED] wrote: From that page: Sometimes it would be useful to lock further tables in the course of a transaction. Unfortunately, LOCK TABLES in MySQL performs an implicit COMMIT and UNLOCK TABLES. An InnoDB variant of LOCK TABLES has been planned that can be executed in the middle of a transaction. I read that as saying that you can't issue a LOCK TABLES and then another LOCK TABLES in the same transaction, because it causes a COMMIT before locking the tables. You can use one LOCK TABLES at the beginning of your transaction with no problems. In any case, you only need to do a table lock long enough to insert a row into your first table. After that, you can release the lock. And when I insert the row in the first table but cannot do so in the second because of some invalid data, I need to also remove the first row again because it doesn't make sense alone. This is what transactions are for. Yes, and you will be in a transaction, and the insert will be rolled back. But maybe UNLOCK TABLES would commit your transaction, in which case, you do need to keep the lock until the transaction is over. Oh, I see from that page above: All InnoDB locks held by a transaction are released when the transaction is committed or aborted. Thus, it does not make much sense to invoke LOCK TABLES on InnoDB tables in AUTOCOMMIT=1 mode, because the acquired InnoDB table locks would be released immediately. So, it seems that locking tables is *impossible* with InnoDB. No, the text you're quoting there says that LOCK TABLES is impossible without a transaction in InnoDB. You plan to use a transaction. - Perrin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Transactions and locking
Yves,
Damn, I found out that I need table locking *and* transactions.
What makes you say that?
BEGIN TRANSACTION
SELECT MAX(id) FROM table
INSERT INTO table (id) VALUES (?)
INSERT INTO othertable (id) VALUES (?)
COMMIT
First I find a new id value, then I do several INSERTs that need to be
atomic, and especially roll back completely if a later one fails.
That Perl module uses the exact technique I described to you with
updates and LAST_INSERT_ID().
AUTO_INCREMENT isn't portable. Now I only support MySQL and SQLite.
But I also did PostgreSQL (until it failed one of the more complex
queries, maybe it comes back one day) and maybe Oracle or whatever will
be compatible, too, so that I then stand there with my AUTO_INCREMENT
and can't use it.
I would suggest the following --
create a table called SEQUENCES:
create table SEQUENCES
( table_name varchar(128/maxlength of tablename) not null primary key,
sequence_value largeint not null) ;
Create a row for each table, eg:
insert into sequences values('CUSTOMERS', 0);
Next, whenever you want to get a new value, do:
select sequence_value as current_value
from sequences
where table_name = 'CUSTOMERS';
Next, do this:
update sequences
set sequence_value = sequence_value + 1
where sequence_value = your current value you just got
and table_name = 'CUSTOMERS'
Now, repeate the above sequence until the UPDATE statement
above says that it's updated 1 row. If it updated 0 rows, it means
someone else did it just before you.
Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, NexusDB, Oracle
MS SQL Server
Upscene Productions
http://www.upscene.com
My thoughts:
http://blog.upscene.com/martijn/
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Transactions and locking
Baron Schwartz wrote: Yves Goergen wrote: (For the record... I missed the mailing list recipient - again!!) On 13.11.2007 00:30 CE(S)T, Perrin Harkins wrote: On Nov 12, 2007 5:58 PM, Yves Goergen [EMAIL PROTECTED] wrote: First I find a new id value, then I do several INSERTs that need to be atomic, and especially roll back completely if a later one fails. If you use a table lock on the first table where you get the ID, you know that ID is safe to use. Using a table lock when you get the ID and then trusting transactions to roll back all the inserts in the event of a later failure should work fine. From what I've read about MySQL's table locks and InnoDB, you cannot use LOCK TABLES with transactions. Either of them deactivates the other one. Beginning a transaction unlockes all tables, locking tables ends a transaction. It's more complicated than that. You can use them together, you just have to do it like this: set autocommit = 0; begin; lock tables; -- you are now in a transaction automatically begun by LOCK TABLES . commit; -- your tables are now unlocked. In fact, you *must* use a transaction for LOCK TABLES to be safe, at least in MySQL 5. Even if you're using non-transactional tables. Otherwise, you can get nasty behavior. See http://bugs.mysql.com/bug.php?id=31479 The manual isn't very clear on the interaction between LOCK TABLES and transactions, it's true. But this is what I've found. As your using InnoDB, which has row level locking a SELECT ... FOR UPDATE should work. http://dev.mysql.com/doc/refman/4.1/en/innodb-locking-reads.html e.g. BEGIN TRANSACTION new_id := (SELECT MAX(id) FROM table FOR UPDATE) + 1 -- some more work here INSERT INTO table (id, ...) VALUES (new_id, ...) COMMIT mark -- MARK ADDISON WEB DEVELOPER 200 GRAY'S INN ROAD LONDON WC1X 8XZ UNITED KINGDOM T +44 (0)20 7430 4678 F E [EMAIL PROTECTED] WWW.ITN.CO.UK P Please consider the environment. Do you really need to print this email? Please Note: Any views or opinions are solely those of the author and do not necessarily represent those of Independent Television News Limited unless specifically stated. This email and any files attached are confidential and intended solely for the use of the individual or entity to which they are addressed. If you have received this email in error, please notify [EMAIL PROTECTED] Please note that to ensure regulatory compliance and for the protection of our clients and business, we may monitor and read messages sent to and from our systems. Thank You.
Re: Transactions and locking
On 13.11.2007 16:37 CE(S)T, mark addison wrote: As your using InnoDB, which has row level locking a SELECT ... FOR UPDATE should work. http://dev.mysql.com/doc/refman/4.1/en/innodb-locking-reads.html e.g. BEGIN TRANSACTION new_id := (SELECT MAX(id) FROM table FOR UPDATE) + 1 -- some more work here INSERT INTO table (id, ...) VALUES (new_id, ...) COMMIT Row level locking can only lock rows that exist. Creating new rows (that would have an influence on my MAX value) are still possible and thus row level locking is not what I need. I really need locking an entire table for every other read or write access. -- Yves Goergen LonelyPixel [EMAIL PROTECTED] Visit my web laboratory at http://beta.unclassified.de -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Transactions and locking
Yves Goergen wrote: On 13.11.2007 16:37 CE(S)T, mark addison wrote: As your using InnoDB, which has row level locking a SELECT ... FOR UPDATE should work. http://dev.mysql.com/doc/refman/4.1/en/innodb-locking-reads.html e.g. BEGIN TRANSACTION new_id := (SELECT MAX(id) FROM table FOR UPDATE) + 1 -- some more work here INSERT INTO table (id, ...) VALUES (new_id, ...) COMMIT Row level locking can only lock rows that exist. Creating new rows (that would have an influence on my MAX value) are still possible and thus row level locking is not what I need. I really need locking an entire table for every other read or write access. InnoDB can also lock the gap, which will prevent new rows that would have been returned by the SELECT. The manual has more info on this in the section on consistent reads in InnoDB. FOR UPDATE will do what you need. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Transactions and locking
(Damn I hate those lists that don't come with a Reply-To to the list! Resending...) On 13.11.2007 17:39 CE(S)T, Baron Schwartz wrote: Yves Goergen wrote: Row level locking can only lock rows that exist. Creating new rows (that would have an influence on my MAX value) are still possible and thus row level locking is not what I need. I really need locking an entire table for every other read or write access. InnoDB can also lock the gap, which will prevent new rows that would have been returned by the SELECT. The manual has more info on this in the section on consistent reads in InnoDB. FOR UPDATE will do what you need. I've read about that gap but it sounded like the place [somewhere] before a record where one could insert a new record into. Not sure what that should be. I'm not aware of the InnoDB internals. I know that usually (?) when a new record is stored, it is written to where is enough space for it, linked from a free pointer index. If one is locked, another one might be used. Order doesn't matter in relational databases. -- Yves Goergen LonelyPixel [EMAIL PROTECTED] Visit my web laboratory at http://beta.unclassified.de -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Transactions and locking
Yves Goergen wrote: (Damn I hate those lists that don't come with a Reply-To to the list! Resending...) On 13.11.2007 17:39 CE(S)T, Baron Schwartz wrote: Yves Goergen wrote: Row level locking can only lock rows that exist. Creating new rows (that would have an influence on my MAX value) are still possible and thus row level locking is not what I need. I really need locking an entire table for every other read or write access. InnoDB can also lock the gap, which will prevent new rows that would have been returned by the SELECT. The manual has more info on this in the section on consistent reads in InnoDB. FOR UPDATE will do what you need. I've read about that gap but it sounded like the place [somewhere] before a record where one could insert a new record into. Not sure what that should be. I'm not aware of the InnoDB internals. I know that usually (?) when a new record is stored, it is written to where is enough space for it, linked from a free pointer index. If one is locked, another one might be used. Order doesn't matter in relational databases. Are you thinking that your theoretical knowledge of relational databases must hold the answer to your questions about MySQL? :-) I suggest you read the entire manual section on InnoDB and experiment. Set aside a day for it; there's a lot to learn there. Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Transactions and locking
On Nov 13, 2007 11:39 AM, Baron Schwartz [EMAIL PROTECTED] wrote: InnoDB can also lock the gap, which will prevent new rows that would have been returned by the SELECT. The manual has more info on this in the section on consistent reads in InnoDB. FOR UPDATE will do what you need. Interesting, I didn't think that would work, but the manual does say it will: You can use next-key locking to implement a uniqueness check in your application: If you read your data in share mode and do not see a duplicate for a row you are going to insert, then you can safely insert your row and know that the next-key lock set on the successor of your row during the read prevents anyone meanwhile inserting a duplicate for your row. Thus, the next-key locking allows you to lock the non-existence of something in your table. http://dev.mysql.com/doc/refman/5.0/en/innodb-next-key-locking.html There's another suggestion in the comments on that page: INSERT IGNORE and then check the number of rows affected. But, not portable to SQLite. - Perrin P.S. I enjoy your blog, Baron. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Transactions and locking
On 13.11.2007 14:01 CE(S)T, Baron Schwartz wrote: It's more complicated than that. You can use them together, you just have to do it like this: set autocommit = 0; begin; lock tables; -- you are now in a transaction automatically begun by LOCK TABLES . I assume that at this point, any SELECT on the table I have locked should block. But guess what, it doesn't. So it doesn't really lock. commit; -- your tables are now unlocked. In fact, you *must* use a transaction for LOCK TABLES to be safe, at least in MySQL 5. Even if you're using non-transactional tables. Otherwise, you can get nasty behavior. See http://bugs.mysql.com/bug.php?id=31479 -- Yves Goergen LonelyPixel [EMAIL PROTECTED] Visit my web laboratory at http://beta.unclassified.de -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Transactions and locking
Yves Goergen wrote: On 13.11.2007 14:01 CE(S)T, Baron Schwartz wrote: It's more complicated than that. You can use them together, you just have to do it like this: set autocommit = 0; begin; lock tables; -- you are now in a transaction automatically begun by LOCK TABLES . I assume that at this point, any SELECT on the table I have locked should block. But guess what, it doesn't. So it doesn't really lock. What kind of lock are you using? -- cxn 1 set autocommit=0; begin; lock tables t1 write; Query OK, 0 rows affected (6.29 sec) -- cxn 2 set autocommit=0; begin; select * from t1; -- hangs -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Transactions and locking
On 13.11.2007 19:19 CE(S)T, Perrin Harkins wrote: You can use next-key locking to implement a uniqueness check in your application: (...) http://dev.mysql.com/doc/refman/5.0/en/innodb-next-key-locking.html This doesn't help my problem either. It may lock new INSERTs to the table, but it won't lock SELECTs so any other concurrent user can still find its own (same) MAX(id) value and then do an insert. Or any other process can still check for uniqueness and then fail with its insert. The insert of the first process may succeed guaranteed, but the second will fail at a point where it should not. (Actually, it should never fail when I found a new id value / found that my new value is unique.) I have tested the SELECT ... FOR UPDATE and the LOCK TABLES with autocommit = 0 thing. Both don't lock anything (at least not for reading by others which is what I need). May I now conclude that exclusive full table locking is not possible with InnoDB? Or is there another way that I don't know yet? -- Yves Goergen LonelyPixel [EMAIL PROTECTED] Visit my web laboratory at http://beta.unclassified.de -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Transactions and locking
Yves Goergen wrote: On 13.11.2007 19:19 CE(S)T, Perrin Harkins wrote: You can use next-key locking to implement a uniqueness check in your application: (...) http://dev.mysql.com/doc/refman/5.0/en/innodb-next-key-locking.html This doesn't help my problem either. It may lock new INSERTs to the table, but it won't lock SELECTs so any other concurrent user can still It will absolutely lock SELECTs. Are you sure autocommit is set to 0 and you have an open transaction? Are you sure your table is InnoDB? I'm doing this right now: -- cxn 1 mysql set autocommit=0; mysql begin; mysql select * from t1 for update; +--+ | a| +--+ |1 | +--+ 1 row in set (0.00 sec) -- cxn 2 mysql set autocommit=0; mysql begin; mysql select * from t1 for update; ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Transactions and locking
On 13.11.2007 20:43 CE(S)T, Baron Schwartz wrote: Yves Goergen wrote: I assume that at this point, any SELECT on the table I have locked should block. But guess what, it doesn't. So it doesn't really lock. What kind of lock are you using? -- cxn 1 set autocommit=0; begin; lock tables t1 write; Query OK, 0 rows affected (6.29 sec) -- cxn 2 set autocommit=0; begin; select * from t1; -- hangs Not for me. This is what I was doing here. (FYI: MySQL 5.0.45-community-nt, Windows XP, mysql command line client, InnoDB tables) -- Yves Goergen LonelyPixel [EMAIL PROTECTED] Visit my web laboratory at http://beta.unclassified.de -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Transactions and locking
On 13.11.2007 20:57 CE(S)T, Baron Schwartz wrote: It will absolutely lock SELECTs. Are you sure autocommit is set to 0 and you have an open transaction? Are you sure your table is InnoDB? I'm doing this right now: -- cxn 1 mysql set autocommit=0; mysql begin; mysql select * from t1 for update; +--+ | a| +--+ |1 | +--+ 1 row in set (0.00 sec) -- cxn 2 mysql set autocommit=0; mysql begin; mysql select * from t1 for update; Okay, my fault, I didn't use the FOR UPDATE in the second connection. If I do (which is likely to be the case in an application because there, the same code is run concurrently), the second SELECT locks. (The same is true when I select MAX(id) instead of *.) If I don't, it still works. Okay, so we have some table locking, tested, working. Very nice. Thank you for this one. :) ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction -- Yves Goergen LonelyPixel [EMAIL PROTECTED] Visit my web laboratory at http://beta.unclassified.de -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Transactions and locking
On 13.11.2007 20:43 CE(S)T, Baron Schwartz wrote: -- cxn 2 set autocommit=0; begin; select * from t1; -- hangs Delete my last message. I just did it again and now it works, too. I have no idea what I did a couple of minutes ago, but it must have been wrong. Okay. Works, too. I was doubting that it was possible at all. Meanwhile, I found the Oracle reference and it says that locks can never lock queries, so reading a table is possible in any case. Thank you for all your patience you had with me. I think my problems are now solved... I'll see it when I test my application the next time. ;) -- Yves Goergen LonelyPixel [EMAIL PROTECTED] Visit my web laboratory at http://beta.unclassified.de -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Transactions and locking
On Nov 13, 2007 3:32 PM, Yves Goergen [EMAIL PROTECTED] wrote: I found the Oracle reference and it says that locks can never lock queries, so reading a table is possible in any case. No, you just have to use FOR UPDATE and it will block. - Perrin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Transactions and locking
Hi, there's very much information about how transactions and locking works in InnoDB, but maybe there's also a simple and understandable answer to my simple question: When I start a transaction, then find the maximum value of a column and use that + 1 to write a new row into the table, how do transactions protect me from somebody else doing the same thing so that we'd both end up writing a new row with the same value? Here's a description: BEGIN TRANSACTION new_id := (SELECT MAX(id) FROM table) + 1 -- some more work here INSERT INTO table (id, ...) VALUES (new_id, ...) COMMIT What happens if another user does the same in that more work region? (Of course, this example is pseudocode, I really have a PHP application that does this.) -- Yves Goergen LonelyPixel [EMAIL PROTECTED] Visit my web laboratory at http://beta.unclassified.de -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Transactions and locking
Hello Yves, there's very much information about how transactions and locking works in InnoDB, but maybe there's also a simple and understandable answer to my simple question: When I start a transaction, then find the maximum value of a column and use that + 1 to write a new row into the table, how do transactions protect me from somebody else doing the same thing so that we'd both end up writing a new row with the same value? They won't, a constraint protects you from inserting a new row with the same value. Here's a description: BEGIN TRANSACTION new_id := (SELECT MAX(id) FROM table) + 1 -- some more work here INSERT INTO table (id, ...) VALUES (new_id, ...) COMMIT What happens if another user does the same in that more work region? You will end up with the same new_id value, but the primary key constraint - if you have one - will reject the insert. Transactions come in multiple flavors, have a look at the different isolation levels: http://dev.mysql.com/doc/refman/5.0/en/commit.html http://dev.mysql.com/doc/refman/5.0/en/set-transaction.html Depending on your isolation level, for example, you will see new rows in the table between your begin transaction and select max... or between two select max statements. Other isolation levels will give you the same max value when reading the value twice, even though someone else inserted a new row. Martijn Tonies Database Workbench - development tool for MySQL, and more! Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Transactions and locking
On Nov 12, 2007 1:25 PM, Yves Goergen [EMAIL PROTECTED] wrote: When I start a transaction, then find the maximum value of a column and use that + 1 to write a new row into the table, how do transactions protect me from somebody else doing the same thing so that we'd both end up writing a new row with the same value? Usually you would use an auto_increment column for this. If you want to do it manually, you either need to lock the whole table (to prevent rows from being added) or do the work in one statement (untested): INSERT INTO table (id) values (SELECT MAX(id) + 1 FROM table); You could also keep a separate table that just holds the current ID in a single row and use an update to get it (also untested): UPDATE counter SET id = LAST_INSERT_ID(id + 1); Putting the LAST_INSERT_ID in there lets you grab the id afterward in the same way you get it from an auto_increment, without doing another select. - Perrin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Transactions and locking
Okay, I feel like I need to clarify some things. I do have a UNIQUE INDEX constraint on those columns, so the other user won't actually write the same value another time, but it will fail at a level which it should not. I don't want to use AUTO_INCREMENT because it's not portable. My application should work on MySQL and SQLite (and maybe someday it will also run on many other systems - today, incompatibilities are just too big). Here's another example: SELECT COUNT(*) FROM table WHERE name = ? -- a short delay which is long enough for a concurrent request :( UPDATE table SET name = ? WHERE id = ? I do the first query to find out whether my new name is already assigned. Each name can only appear one time. If I just try and update the row, the query will fail, but I don't know why. All I could do is try and parse the error message, but this will by DBMS-dependent. I'd like to do it in a way so that I can tell the user whether the name was not unique or there was another error. But this case should be detected separately. I'll have a look at those isolation levels though. Maybe it's what I'm looking for. -- Yves Goergen LonelyPixel [EMAIL PROTECTED] Visit my web laboratory at http://beta.unclassified.de -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Transactions and locking
On Nov 12, 2007 2:43 PM, Yves Goergen [EMAIL PROTECTED] wrote: SELECT COUNT(*) FROM table WHERE name = ? -- a short delay which is long enough for a concurrent request :( UPDATE table SET name = ? WHERE id = ? I think that even with SERIALIZABLE isolation level, this won't lock anything if it doesn't match any rows, so someone could do an insert between those statements. I could be wrong about that. The alternative is to lock the table. I'm not sure how that would be done in SQLite, although SQLite works by taking an exclusive write lock on the entire database so it may not be an issue. - Perrin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Transactions and locking
On 12.11.2007 20:43 CE(S)T, Yves Goergen wrote: I'll have a look at those isolation levels though. Maybe it's what I'm looking for. Not quite. But I'm going the LOCK TABLES way now. Locking a single table exclusively for those rare moments seems to be the best solution. I could also implement an abstraction for that, because other DBMS have different syntax to do the same thing. Since I only need these locks for a very short time and a single table with no transaction support, this works fine for me. -- Yves Goergen LonelyPixel [EMAIL PROTECTED] Visit my web laboratory at http://beta.unclassified.de -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Transactions and locking
On 12.11.2007 22:16 CE(S)T, Yves Goergen wrote: Since I only need these locks for a very short time and a single table with no transaction support, this works fine for me. Damn, I found out that I need table locking *and* transactions. I'm lost... Maybe I'm really better off using a sequence (like the one PostgreSQL offers and like it is available as an add-on for Perl [1]). But then again, I need queries outside of a transaction so that the sequence's next number is immediately commited and visible to other users. I have the impression that it all doesn't work. [1] http://search.cpan.org/~adamk/DBIx-MySQLSequence-1.00/lib/DBIx/MySQLSequence.pm -- Yves Goergen LonelyPixel [EMAIL PROTECTED] Visit my web laboratory at http://beta.unclassified.de -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Transactions and locking
On Nov 12, 2007 5:24 PM, Yves Goergen [EMAIL PROTECTED] wrote: Damn, I found out that I need table locking *and* transactions. What makes you say that? Maybe I'm really better off using a sequence (like the one PostgreSQL offers and like it is available as an add-on for Perl [1]). That Perl module uses the exact technique I described to you with updates and LAST_INSERT_ID(). Frankly, doing the insert and checking for an error seems like a pretty reasonable solution to me, since you only have two databases to care about at this point. - Perrin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Transactions and locking
On 12.11.2007 23:31 CE(S)T, Perrin Harkins wrote: On Nov 12, 2007 5:24 PM, Yves Goergen [EMAIL PROTECTED] wrote: Damn, I found out that I need table locking *and* transactions. What makes you say that? BEGIN TRANSACTION SELECT MAX(id) FROM table INSERT INTO table (id) VALUES (?) INSERT INTO othertable (id) VALUES (?) COMMIT First I find a new id value, then I do several INSERTs that need to be atomic, and especially roll back completely if a later one fails. That Perl module uses the exact technique I described to you with updates and LAST_INSERT_ID(). AUTO_INCREMENT isn't portable. Now I only support MySQL and SQLite. But I also did PostgreSQL (until it failed one of the more complex queries, maybe it comes back one day) and maybe Oracle or whatever will be compatible, too, so that I then stand there with my AUTO_INCREMENT and can't use it. Frankly, doing the insert and checking for an error seems like a pretty reasonable solution to me, since you only have two databases to care about at this point. I wonder if I can safely use an error code to determine this error condition and then just retry. Here's an interesting page: http://dev.mysql.com/doc/refman/5.0/en/error-messages-server.html Error: 1022 SQLSTATE: 23000 (ER_DUP_KEY) Message: Can't write; duplicate key in table '%s' No documentation for SQLite. PostgreSQL uses several SQLSTATE codes for this situation. (http://www.postgresql.org/docs/8.2/interactive/errcodes-appendix.html) Something must be wrong with SQL-92 because the two reference tables have no common SQLSTATE values for related error conditions. But generally I think that an SQLSTATE beginning with 23 is close enough for a match. -- Yves Goergen LonelyPixel [EMAIL PROTECTED] Visit my web laboratory at http://beta.unclassified.de -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Transactions and locking
On Nov 12, 2007 5:58 PM, Yves Goergen [EMAIL PROTECTED] wrote: BEGIN TRANSACTION SELECT MAX(id) FROM table INSERT INTO table (id) VALUES (?) INSERT INTO othertable (id) VALUES (?) COMMIT First I find a new id value, then I do several INSERTs that need to be atomic, and especially roll back completely if a later one fails. If you use a table lock on the first table where you get the ID, you know that ID is safe to use. Using a table lock when you get the ID and then trusting transactions to roll back all the inserts in the event of a later failure should work fine. That Perl module uses the exact technique I described to you with updates and LAST_INSERT_ID(). AUTO_INCREMENT isn't portable. You're misunderstanding. The LAST_INSERT_ID() function doesn't use AUTO_INCREMENT. That's why the perl module uses it. It just copies the value you pass to it and makes that available without another select. It's not portable to SQLite, but you can use a sequence there instead. - Perrin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Dump question: transactions vs. locking
Csongor,
in InnoDB, it is better to use
SELECT ... FOR UPDATE
to lock the result set of a SELECT.
A plain SELECT in InnoDB is a consistent, non-locking read that reads a
snapshot of the database at an earlier time. It does not lock anything.
Best regards,
Heikki Tuuri
Innobase Oy
Foreign keys, transactions, and row level locking for MySQL
InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM
tables
http://www.innodb.com/order.php
Order MySQL technical support from https://order.mysql.com/
- Original Message -
From: Fagyal Csongor [EMAIL PROTECTED]
Newsgroups: mailing.database.myodbc
Sent: Friday, September 10, 2004 1:55 PM
Subject: Dump question: transactions vs. locking
Hi,
I think I have some problems understanding how transactions work, and
how they relate to locking. Can someone please explain?
The question is this:
I have a table where I have a column into which I insert numbers in
sequencial order - it is not set to auto_increment, though, because the
field is only unique with two other columns. The fields are:
mainid,subid and userid (in the table invoices).
Before I changed to InnoDB, using MyISAM I used to lock the table, get
MAX(subid), do an insert, then unlock. It was something like this (this
is Perl):
# lock the table
$dbh-do('LOCK TABLES invoices WRITE');
# next subid is MAX(subid)
my ($nextsubid) = $dbh-selectrow_array('SELECT MAX(subid) FROM invoices
WHERE userid=? AND mainid=?', undef, $userid, $mainid);
# increment by 1
$nextsubid++;
# insert all stuff with new $subid
$dbh-do('INSERT INTO invoices '); # set subid here
# unlock
$dbh-do('UNLOCK TABLES');
So what if I change to transactions? Should I simply just substitute
LOCK/UNLOCK by BEGIN/COMMIT, or should I still use LOCK/UNLOCK? If the
later, what is the correct order: BEGIN and then LOCK, or LOCK and then
BEGIN? Also, am I right that as in transactional mode I always need a
commit, I MUST use BEGIN/COMMIT, and cannot keep the original simple
LOCK/UNLOCK?
Thank you,
- Csongor
--
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: Dump question: transactions vs. locking
Hi Heikki, Csongor, in InnoDB, it is better to use SELECT ... FOR UPDATE to lock the result set of a SELECT. Thank you, I think I will go with this one. A plain SELECT in InnoDB is a consistent, non-locking read that reads a snapshot of the database at an earlier time. It does not lock anything. But it does allow a LOCK during a transaction, doesn't it? Or is this practice not recommended? Regards, - Csongor -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Dump question: transactions vs. locking
Hi Ed,
Maybe MyISAM is still a better choice for this use...?
For MyISAM and BDB tables you can specify AUTO_INCREMENT on a secondary
column (or three columns in your case) in a multiple-column index. In
this case, the generated value for the AUTO_INCREMENT column is
calculated as MAX(auto_increment_column)+1 WHERE prefix=given-prefix.
This is useful when you want to put data into ordered groups.
See the link:
http://dev.mysql.com/doc/mysql/en/example-AUTO_INCREMENT.html
Wow, thank you! MySQL keeps surprising me :-) INSERT ... ON DUPLICATE
KEY UPDATE ... was the first in the list ;-)
However, I still need InnoDB, though, as I am doing deletes on multiple
tables, of which I am scared :-)
Reagards,
- Csongor
ps: Obviously this was a dumb question, not a dump one :-)
Ed
-Original Message-
From: Fagyal Csongor [mailto:[EMAIL PROTECTED]
Sent: Friday, September 10, 2004 4:53 AM
To: [EMAIL PROTECTED]
Subject: Dump question: transactions vs. locking
Hi,
I think I have some problems understanding how transactions work, and
how they relate to locking. Can someone please explain?
The question is this:
I have a table where I have a column into which I insert numbers in
sequencial order - it is not set to auto_increment, though, because the
field is only unique with two other columns. The fields are:
mainid,subid and userid (in the table invoices).
Before I changed to InnoDB, using MyISAM I used to lock the table, get
MAX(subid), do an insert, then unlock. It was something like this (this
is Perl):
# lock the table
$dbh-do('LOCK TABLES invoices WRITE');
# next subid is MAX(subid)
my ($nextsubid) = $dbh-selectrow_array('SELECT MAX(subid) FROM invoices
WHERE userid=? AND mainid=?', undef, $userid, $mainid);
# increment by 1
$nextsubid++;
# insert all stuff with new $subid
$dbh-do('INSERT INTO invoices '); # set subid here
# unlock
$dbh-do('UNLOCK TABLES');
So what if I change to transactions? Should I simply just substitute
LOCK/UNLOCK by BEGIN/COMMIT, or should I still use LOCK/UNLOCK? If the
later, what is the correct order: BEGIN and then LOCK, or LOCK and then
BEGIN? Also, am I right that as in transactional mode I always need a
commit, I MUST use BEGIN/COMMIT, and cannot keep the original simple
LOCK/UNLOCK?
Thank you,
- Csongor
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Dump question: transactions vs. locking
Csongor, - Alkuperäinen viesti - Lähettäjä: Fagyal Csongor [EMAIL PROTECTED] Vastaanottaja: Heikki Tuuri [EMAIL PROTECTED] Kopio: [EMAIL PROTECTED] Lähetetty: Monday, September 13, 2004 3:36 PM Aihe: Re: Dump question: transactions vs. locking Hi Heikki, Csongor, in InnoDB, it is better to use SELECT ... FOR UPDATE to lock the result set of a SELECT. Thank you, I think I will go with this one. A plain SELECT in InnoDB is a consistent, non-locking read that reads a snapshot of the database at an earlier time. It does not lock anything. But it does allow a LOCK during a transaction, doesn't it? Or is this practice not recommended? the behavior in = 4.0.20 and = 4.1.3 is that when you call LOCK TABLES ... it sets a table lock both in MySQL and also inside InnoDB. Note that LOCK TABLES also implicitly commits the current transaction. When you call COMMIT that releases the InnoDB table and row locks, but does NOT release the MySQL table locks. To release the MySQL table locks, you have to call UNLOCK TABLES. Actually, also UNLOCK TABLES implicitly commits the current transaction. Since the rules for LOCK TABLES are as complex as this, it is usually better to avoid that command with transactions. A sensible use of LOCK TABLES might look like this: SET AUTOCOMMIT=0; #Let us process a transaction LOCK TABLES t WRITE, s READ; #Lock all tables we are going to use; note that this command may also fail SELECT * FROM s LOCK IN SHARE MODE; #We must use a 'locking read' to see the latest data calculate a new row for t based on what we read from s INSERT INTO t VALUES (...); COMMIT; UNLOCK TABLES; Above we have enclosed a transaction inside LOCK TABLES ... UNLOCK TABLES. Be prepared for lock wait timeout errors and deadlocks when issuing LOCK TABLES. It is NOT guaranteed to succeed every time. Regards, - Csongor Best regards, Heikki Innobase Oy InnoDB - transactions, row level locking, and foreign keys for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables http://www.innodb.com/order.php Order MySQL support from http://www.mysql.com/support/index.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Dump question: transactions vs. locking
Hi,
I think I have some problems understanding how transactions work, and
how they relate to locking. Can someone please explain?
The question is this:
I have a table where I have a column into which I insert numbers in
sequencial order - it is not set to auto_increment, though, because the
field is only unique with two other columns. The fields are:
mainid,subid and userid (in the table invoices).
Before I changed to InnoDB, using MyISAM I used to lock the table, get
MAX(subid), do an insert, then unlock. It was something like this (this
is Perl):
# lock the table
$dbh-do('LOCK TABLES invoices WRITE');
# next subid is MAX(subid)
my ($nextsubid) = $dbh-selectrow_array('SELECT MAX(subid) FROM invoices
WHERE userid=? AND mainid=?', undef, $userid, $mainid);
# increment by 1
$nextsubid++;
# insert all stuff with new $subid
$dbh-do('INSERT INTO invoices '); # set subid here
# unlock
$dbh-do('UNLOCK TABLES');
So what if I change to transactions? Should I simply just substitute
LOCK/UNLOCK by BEGIN/COMMIT, or should I still use LOCK/UNLOCK? If the
later, what is the correct order: BEGIN and then LOCK, or LOCK and then
BEGIN? Also, am I right that as in transactional mode I always need a
commit, I MUST use BEGIN/COMMIT, and cannot keep the original simple
LOCK/UNLOCK?
Thank you,
- Csongor
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Dump question: transactions vs. locking
Maybe MyISAM is still a better choice for this use...?
For MyISAM and BDB tables you can specify AUTO_INCREMENT on a secondary
column (or three columns in your case) in a multiple-column index. In
this case, the generated value for the AUTO_INCREMENT column is
calculated as MAX(auto_increment_column)+1 WHERE prefix=given-prefix.
This is useful when you want to put data into ordered groups.
See the link:
http://dev.mysql.com/doc/mysql/en/example-AUTO_INCREMENT.html
Ed
-Original Message-
From: Fagyal Csongor [mailto:[EMAIL PROTECTED]
Sent: Friday, September 10, 2004 4:53 AM
To: [EMAIL PROTECTED]
Subject: Dump question: transactions vs. locking
Hi,
I think I have some problems understanding how transactions work, and
how they relate to locking. Can someone please explain?
The question is this:
I have a table where I have a column into which I insert numbers in
sequencial order - it is not set to auto_increment, though, because the
field is only unique with two other columns. The fields are:
mainid,subid and userid (in the table invoices).
Before I changed to InnoDB, using MyISAM I used to lock the table, get
MAX(subid), do an insert, then unlock. It was something like this (this
is Perl):
# lock the table
$dbh-do('LOCK TABLES invoices WRITE');
# next subid is MAX(subid)
my ($nextsubid) = $dbh-selectrow_array('SELECT MAX(subid) FROM invoices
WHERE userid=? AND mainid=?', undef, $userid, $mainid);
# increment by 1
$nextsubid++;
# insert all stuff with new $subid
$dbh-do('INSERT INTO invoices '); # set subid here
# unlock
$dbh-do('UNLOCK TABLES');
So what if I change to transactions? Should I simply just substitute
LOCK/UNLOCK by BEGIN/COMMIT, or should I still use LOCK/UNLOCK? If the
later, what is the correct order: BEGIN and then LOCK, or LOCK and then
BEGIN? Also, am I right that as in transactional mode I always need a
commit, I MUST use BEGIN/COMMIT, and cannot keep the original simple
LOCK/UNLOCK?
Thank you,
- Csongor
--
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]
