> 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, yo
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 ac
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 ta
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 arc
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 do
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 *
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
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 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
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 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
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.
Inte
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 st
(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:
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) +
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 tra
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
> -- s
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
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 INS
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
> >
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
(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 compl
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 unl
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
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: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 P
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 sequenc
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 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
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 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
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
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 tab
Dear All,
I hope somebody can help me, I have installed MySQL on a Windows 2000 system
and it is working fine.
However I require Transactions, Record Locking and RollBack, so I have also
downloaded the Berkley DB v3.2.3, this is where my problems start. I am
using MS Visual C++ v6, when I try
34 matches
Mail list logo
