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
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
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 = <>
> 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 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]
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 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: > 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
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 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 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 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
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
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
(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: 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
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; Is this the key that I have missed? I thought that setting autocommit = 0 is pointless when I issue statements within a transaction only, anyway. So this variable still has some influence even inside a transaction? That would indeed be very unclear from the documentation. > begin; > lock tables; > -- you are now in a transaction automatically begun by LOCK TABLES > . > commit; > -- your tables are now unlocked. > The manual isn't very clear on the interaction between LOCK TABLES and > transactions, it's true. But this is what I've found. -- 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 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
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
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 = <>
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
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 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
(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
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
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: 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: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 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 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 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
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 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
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]
