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
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
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
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
(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
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
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
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
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
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
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) +
(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
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
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.
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,
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
]
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
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
: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
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
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
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
38 matches
Mail list logo
