Re: Transactions with ODBC
Thank you for your response. I am using InnoDB (picked that out of the docs).
Does that mean what I did should have worked? I should not have had 2 rows in
that table after running the commands?
Thanks again...
I would like to wrap my updates top MySQL in transactions.
Use InnoDB tables.
PB
[EMAIL PROTECTED] wrote:
I apologize if you saw this on the MySQL Forums but I have not gotten a
response... Thanks for your help...
I know this is probably a stupid question but I could use a nudge in the
right direction.
I would like to wrap my updates top MySQL in transactions. I am using ODBC as
my means of communication (Visual FoxPro is the front end) and passing SQL
statements off to the server one Execute at a time.
I understand how I could do this with a stored procedure, or possibly even
ADO, but I would prefer to send it one command at a time for various reasons.
When I send the following series of commands through, though, it doesn't work
although each statement tells me that it executed properly:
*-- Set the connection string
ConnectionString = DRIVER={MySQL ODBC 3.51 Driver};SERVER=localhost;
+ ; DATABASE=test; + ; USER=test;+; PASSWORD=test;+; OPTION=3;
lnHandle = SQLSTRINGCONNECT(ConnectionString) Get the connection ?
lnHandle Check the connection to make sure it worked.
lcSQL = START TRANSACTION
? SQLEXEC(lnHandle, lcSQL)
lcSQL = BEGIN
? SQLEXEC(lnHandle, lcSQL)
lcSQL = INSERT INTO n1 (n1) VALUES (1)
? SQLEXEC(lnHandle, lcSQL)
lcSQL = INSERT INTO n1 (n1) VALUES (2)
? SQLEXEC(lnHandle, lcSQL)
lcSQL = ROLLBACK
? SQLEXEC(lnHandle, lcSQL)
Yet, when I query the table after this runs, there are still two records
despite the fact that I asked it to rollback the transaction and that all
commands reported as executing OK.
I am sure I am missing something stupid but I cannot find a decent doc on
this (if you know of one, a reference would be appreciated).
Thanks in advance for your help.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Transactions with ODBC
Does that mean what I did should have worked?
I'd want to know (i) the result of executing those cmds in a mysql
client, and (ii) what sqlresult acountinfo contain after each cmd.
PB
[EMAIL PROTECTED] wrote:
Thank you for your response. I am using InnoDB (picked that out of the docs).
Does that mean what I did should have worked? I should not have had 2 rows in
that table after running the commands?
Thanks again...
I would like to wrap my updates top MySQL in transactions.
Use InnoDB tables.
PB
[EMAIL PROTECTED] wrote:
I apologize if you saw this on the MySQL Forums but I have not gotten a
response... Thanks for your help...
I know this is probably a stupid question but I could use a nudge in the right
direction.
I would like to wrap my updates top MySQL in transactions. I am using ODBC as
my means of communication (Visual FoxPro is the front end) and passing SQL
statements off to the server one Execute at a time.
I understand how I could do this with a stored procedure, or possibly even ADO,
but I would prefer to send it one command at a time for various reasons.
When I send the following series of commands through, though, it doesn't work
although each statement tells me that it executed properly:
*-- Set the connection string
ConnectionString = DRIVER={MySQL ODBC 3.51 Driver};SERVER=localhost;
+ ; DATABASE=test; + ; USER=test;+; PASSWORD=test;+; OPTION=3;
lnHandle = SQLSTRINGCONNECT(ConnectionString) Get the connection ?
lnHandle Check the connection to make sure it worked.
lcSQL = START TRANSACTION
? SQLEXEC(lnHandle, lcSQL)
lcSQL = BEGIN
? SQLEXEC(lnHandle, lcSQL)
lcSQL = INSERT INTO n1 (n1) VALUES (1)
? SQLEXEC(lnHandle, lcSQL)
lcSQL = INSERT INTO n1 (n1) VALUES (2)
? SQLEXEC(lnHandle, lcSQL)
lcSQL = ROLLBACK
? SQLEXEC(lnHandle, lcSQL)
Yet, when I query the table after this runs, there are still two records
despite the fact that I asked it to rollback the transaction and that all
commands reported as executing OK.
I am sure I am missing something stupid but I cannot find a decent doc on this
(if you know of one, a reference would be appreciated).
Thanks in advance for your help.
Re: Transactions with ODBC
I would like to wrap my updates top MySQL in transactions.
Use InnoDB tables.
PB
[EMAIL PROTECTED] wrote:
I apologize if you saw this on the MySQL Forums but I have not gotten a
response... Thanks for your help...
I know this is probably a stupid question but I could use a nudge in the right
direction.
I would like to wrap my updates top MySQL in transactions. I am using ODBC as
my means of communication (Visual FoxPro is the front end) and passing SQL
statements off to the server one Execute at a time.
I understand how I could do this with a stored procedure, or possibly even ADO,
but I would prefer to send it one command at a time for various reasons.
When I send the following series of commands through, though, it doesn't work
although each statement tells me that it executed properly:
*-- Set the connection string
ConnectionString = DRIVER={MySQL ODBC 3.51 Driver};SERVER=localhost; + ;
DATABASE=test; + ;
USER=test;+;
PASSWORD=test;+;
OPTION=3;
lnHandle = SQLSTRINGCONNECT(ConnectionString) Get the connection
? lnHandle Check the connection to make sure it worked.
lcSQL = START TRANSACTION
? SQLEXEC(lnHandle, lcSQL)
lcSQL = BEGIN
? SQLEXEC(lnHandle, lcSQL)
lcSQL = INSERT INTO n1 (n1) VALUES (1)
? SQLEXEC(lnHandle, lcSQL)
lcSQL = INSERT INTO n1 (n1) VALUES (2)
? SQLEXEC(lnHandle, lcSQL)
lcSQL = ROLLBACK
? SQLEXEC(lnHandle, lcSQL)
Yet, when I query the table after this runs, there are still two records
despite the fact that I asked it to rollback the transaction and that all
commands reported as executing OK.
I am sure I am missing something stupid but I cannot find a decent doc on this
(if you know of one, a reference would be appreciated).
Thanks in advance for your help.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
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]
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: Transactions in MySQL.
Hi, All locking in *MySQL* is deadlock-free. This is managed by always requesting all needed locks at once at the beginning of a query and always locking the tables in the same order. The --external-locking and --skip-external-locking options explicitly enable and disable external locking. The LOCK TABLES and UNLOCK TABLES statements use internal locking, so you can use them even if external locking is disabled. A call to LOCK TABLES tries to lock any tables you list so that the current thread can work with it without interference. A call to UNLOCK TABLES releases any locks that this thread holds.There are two main types of locks: read and write. When a lock is released, the lock is made available to the threads in the write lock queue, then to the threads in the read lock queue. External locking is a part of configuration and Internal locking is a part of query. Thanks ViSolve DB Team - Original Message - From: [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Monday, September 25, 2006 11:42 AM Subject: Transactions in MySQL. Hi All, How transactions and locking are handled in MYSQL? Is it a part of configuration? Or a query (lock tables, Unlock tables) for each set of queries? Regards, Ravi K The information contained in this electronic message and any attachments to this message are intended for the exclusive use of the addressee(s) and may contain proprietary, confidential or privileged information. If you are not the intended recipient, you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately and destroy all copies of this message and any attachments. WARNING: Computer viruses can be transmitted via email. The recipient should check this email and any attachments for the presence of viruses. The company accepts no liability for any damage caused by any virus transmitted by this email. www.wipro.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Transactions in MySQL.
It only works with engines that support transactions like innodb and solid, i strongly sugget to read these links from the manual. http://dev.mysql.com/doc/refman/5.0/en/ansi-diff-transactions.html http://dev.mysql.com/doc/refman/5.0/en/innodb-transaction-model.html Carlos On 9/25/06, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: Hi All, How transactions and locking are handled in MYSQL? Is it a part of configuration? Or a query (lock tables, Unlock tables) for each set of queries? Regards, Ravi K The information contained in this electronic message and any attachments to this message are intended for the exclusive use of the addressee(s) and may contain proprietary, confidential or privileged information. If you are not the intended recipient, you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately and destroy all copies of this message and any attachments. WARNING: Computer viruses can be transmitted via email. The recipient should check this email and any attachments for the presence of viruses. The company accepts no liability for any damage caused by any virus transmitted by this email. www.wipro.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Transactions and testing an Insert statement
Chris W. Parker wrote:
Hello,
Me again. Excuse for sending two questions so closely together.
I'm looking through the MySQL manual (as well as searching Google and
the PHP site's MySQL functions) trying to find out how to test an Insert
statement (or any other statement for that matter).
Although I haven't found a direct answer, my searching usually points me
to transactions in InnoDB. Is this what I will need to use to do what I
want?
I'm preparing to import a bunch of data that is coming from an Excel
file from one the vendors we deal with and I want to find out what
manual data preparation I need to do. I'm using PHP's
mysql_real_escape_string as well as some other custom functions but I
need to find out if this is enough.
As I imagine it in my head:
TEST INSERT INTO `table` VALUES ('value', 'value');
You can't test an insert like this but you could do:
begin;
insert into table values(value1, value2);
rollback;
which does mean you need innodb tables. That will rollback any changes
that the insert does, however I'm not sure what happens to an
auto_increment field in that situation (ie does the value get rolled
back or is it left incremented).
The mysql_query function in php returns a resource or failure, so you
could do:
$result = mysql_query($my_query);
if (!$result) {
echo Query ${my_query} failed: . mysql_error() . br/;
} else {
echo Query ${my_query} worked!br/;
}
see php.net/mysql_query for more info.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Transactions (not rolling back on error)
I think it might have something to do with the fact that I'm running the NDB engine. I'm not sure... It doesn't seem to have the same problem on Inno (another transaction-safe engine) I think I need to send MySQL some info so they can try to duplicate it. I'm wondering if anyone else running cluster has had any issues with transactions??? Anyone? I'd _really_ like to get this working since my work-around is in my web application until I can get it working... Good to see you on this list too James! :) Cory. James Harvard wrote: Hi Cory - nice to see a fellow Lasso user here! I've not use transactions myself but I think you might be having a problem with autocommit. http://dev.mysql.com/doc/refman/5.0/en/commit.html HTH, James Harvard At 12:44 am -0700 15/12/05, Cory @ SkyVantage wrote: I have a transaction that is very simple, I need to create records in multiple tables that are related. If any one insert statement fails or throws an error I want to rollback the ENTIRE transaction. I thought that this was the default functionality, but apparently that's not the case here. I'm running MySQL-Cluster 5.0.something. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Transactions (not rolling back on error)
Hi Cory - nice to see a fellow Lasso user here! I've not use transactions myself but I think you might be having a problem with autocommit. http://dev.mysql.com/doc/refman/5.0/en/commit.html HTH, James Harvard At 12:44 am -0700 15/12/05, Cory @ SkyVantage wrote: I have a transaction that is very simple, I need to create records in multiple tables that are related. If any one insert statement fails or throws an error I want to rollback the ENTIRE transaction. I thought that this was the default functionality, but apparently that's not the case here. I'm running MySQL-Cluster 5.0.something. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Transactions (not rolling back on error)
This should be working. You can respond directly to me with schema and SQL that you are using and I can try to reproduce in-house. Thanks, Jonathan Miller Austin, Texas USA Senior Quality Assurance Developer MySQL AB www.mysql.com __ ___ ___ __ / |/ /_ __/ __/ __ \/ / / /|_/ / // /\ \/ /_/ / /__ /_/ /_/\_, /___/\___\_\___/ ___/ www.mysql.com Jumpstart your cluster! http://www.mysql.com/consulting/packaged/cluster.html Get training on clusters http://www.mysql.com/training/courses/mysql_cluster.html All-in-one Enterprise-grade Database, Support and Services http://www.mysql.com/network/ - -Original Message- - From: Cory @ SkyVantage [mailto:[EMAIL PROTECTED] - Sent: Thursday, December 15, 2005 1:45 AM - To: [EMAIL PROTECTED]; mysql@lists.mysql.com - Subject: Transactions (not rolling back on error) - - I have a transaction that is very simple, I need to create records in - multiple tables that are related. If any one insert statement fails or - throws an error I want to rollback the ENTIRE transaction. - - I thought that this was the default functionality, but apparently that's - not the case here. - - I'm running MySQL-Cluster 5.0.something. - - Here's a simplified example.. - - START TRANSACTION; - INSERT INTO; - INSERT INTO; - INSERT INTO; - COMMIT; - - I'm getting data inserted into some tables when others (or one) throw an - error.What am I doing wrong? Do I need to put in a conditional - that checks for an error between each statement? If so, what might that - look like? - - I guess the reason this is so important to me is that Cluster doesn't - enforce Foriegn Key Rules, so it's important that my relationships are - maintained by the transaction properly or else I'll have orphaned - records all over the place... - - Thanks in advance! - - Cory. - - -- - MySQL Cluster Mailing List - For list archives: http://lists.mysql.com/cluster - 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 are not enable
Hello. See: http://dev.mysql.com/doc/mysql/en/transaction.html liofr [EMAIL PROTECTED] wrote: Hi i use a sofware to connect to mysql and it can connect with succes but wanted to share acces to mysql to many poeple and it popup [MUSQL][ODBC 3.51 Driver] Transaction are not enable Is ot a mysql server message or not ( my application ) thank's -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Transactions in Java - JDBC
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 C.F. Scheidecker Antunes wrote: Hello, Can anyone tell me what to do in order to use transactions on a java application? Is there any howto regarding this issu? Thanks, C.F. C.F. First, make sure you're using the InnoDB storage engine (which supports transactions): http://dev.mysql.com/doc/mysql/en/using-innodb-tables.html Then use Connection.setAutoCommit(false) before starting your transaction, and Connection.commit()/Connection.rollback() to commit or rollback transactions: http://java.sun.com/docs/books/tutorial/jdbc/basics/transactions.html -Mark - -- Mark Matthews MySQL AB, Software Development Manager - Connectivity www.mysql.com -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.1 (GNU/Linux) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFC+OdZtvXNTca6JD8RApjfAJ4q5K0N/Tnn5hpQYzJapO8AoDZEFQCfXsE7 laCWxC37BdRNqC3E6qenBzw= =Rab0 -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Transactions dilemma
Stuart Felenstein [EMAIL PROTECTED] wrote: I have a slight dilemma. I am using transactions to insert data into multiple tables. All but one table is Innodb. That one is Myisam and it's left as such because its one text column, so I want the benefits of full text search. Still I need this transaction to somehow include this entry. Two thoughts : 1- I created a temp innodb table and then after transaction move the data over to the myisam. 2-Figure out what the text search options are in innodb and maybe if there is a way to improve on them. Any suggestions ? Consider LOCK TABLES: http://dev.mysql.com/doc/mysql/en/LOCK_TABLES.html -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Transactions dilemma
Egor, Thank you , I wasn't sure anyone would ever respond to this post :) What I wound up doing is , from the application level, running an if / else. The if checks to see if each $query has succeeded. If any of them failed, I do a rollback. If they all have succeeded, I then do a committ. Now I'm not totally sure how a rollback would effect the one Myisam query. I mean it wouldn't , since rollback is alien to myisam, so I probably need to put a line in there to (whatever a rollback is in myisam language) as well. Stuart --- Egor Egorov [EMAIL PROTECTED] wrote: Stuart Felenstein [EMAIL PROTECTED] wrote: I have a slight dilemma. I am using transactions to insert data into multiple tables. All but one table is Innodb. That one is Myisam and it's left as such because its one text column, so I want the benefits of full text search. Still I need this transaction to somehow include this entry. Two thoughts : 1- I created a temp innodb table and then after transaction move the data over to the myisam. 2-Figure out what the text search options are in innodb and maybe if there is a way to improve on them. Any suggestions ? Consider LOCK TABLES: http://dev.mysql.com/doc/mysql/en/LOCK_TABLES.html -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.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 - working but unsure about steps
You have a problem, what if the first insert give a error, then you
don't know. You have to check each statement for error and if no error
if found in all the statements then issue a commit if not a rollback
Osvaldo Sommer
-Original Message-
From: Stuart Felenstein [mailto:[EMAIL PROTECTED]
Sent: Saturday, October 16, 2004 7:19 AM
To: [EMAIL PROTECTED]
Subject: Transactions - working but unsure about steps
My statements are all working but I'm not sure if
things are set up correctly. I say this because at
one point the first $query failed, yet the rest of
inserts wre committed. Now I believe I need to set
autocommit to 0 , yet the query failed due to a syntax
error. Hence the rule about 0 records effected
wouldn't be the case here.
Here is what I have:
//Create these functions -
function begin()
{
mysql_query(BEGIN);
}
function commit()
{
mysql_query(COMMIT);
}
function rollback()
{
mysql_query(ROLLBACK);
}
connection statement with error checking...
begin(); // transaction begins
$query = INSERT INTO firsttable.//first query
$result = mysql_query($query); // process first query
$query = INSERT INTO secondtable...//second query
$result = mysql_query($query); // process second query
$query = INSERT INTO thirdtable//third query
$result = mysql_query($query); // process third query
then:
if(!$result)
{
echo mysql_errno($link) . : . mysql_error($link).
\n;
rollback(); // transaction rolls back
exit;
}
else
{
commit(); // transaction is committed
echo your insertion was successful;
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
---
Incoming mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.778 / Virus Database: 525 - Release Date: 10/15/2004
---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.778 / Virus Database: 525 - Release Date: 10/15/2004
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Transactions - working but unsure about steps
Thank you Osvaldo, That is what I was thinking.
Sometimes when you try to learn from example or manual
it's a bit unclear because they generally show a
simple transaction (1 insert into 1 table)
Stuart
--- Osvaldo Sommer [EMAIL PROTECTED] wrote:
You have a problem, what if the first insert give a
error, then you
don't know. You have to check each statement for
error and if no error
if found in all the statements then issue a commit
if not a rollback
Osvaldo Sommer
-Original Message-
From: Stuart Felenstein [mailto:[EMAIL PROTECTED]
Sent: Saturday, October 16, 2004 7:19 AM
To: [EMAIL PROTECTED]
Subject: Transactions - working but unsure about
steps
My statements are all working but I'm not sure if
things are set up correctly. I say this because at
one point the first $query failed, yet the rest of
inserts wre committed. Now I believe I need to set
autocommit to 0 , yet the query failed due to a
syntax
error. Hence the rule about 0 records effected
wouldn't be the case here.
Here is what I have:
//Create these functions -
function begin()
{
mysql_query(BEGIN);
}
function commit()
{
mysql_query(COMMIT);
}
function rollback()
{
mysql_query(ROLLBACK);
}
connection statement with error checking...
begin(); // transaction begins
$query = INSERT INTO firsttable.//first query
$result = mysql_query($query); // process first
query
$query = INSERT INTO secondtable...//second query
$result = mysql_query($query); // process second
query
$query = INSERT INTO thirdtable//third query
$result = mysql_query($query); // process third
query
then:
if(!$result)
{
echo mysql_errno($link) . : . mysql_error($link).
\n;
rollback(); // transaction rolls back
exit;
}
else
{
commit(); // transaction is committed
echo your insertion was successful;
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
---
Incoming mail is certified Virus Free.
Checked by AVG anti-virus system
(http://www.grisoft.com).
Version: 6.0.778 / Virus Database: 525 - Release
Date: 10/15/2004
---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system
(http://www.grisoft.com).
Version: 6.0.778 / Virus Database: 525 - Release
Date: 10/15/2004
--
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 question
Sorry I found out about Last Insert_ID right after writing this. I guess the correct sequence is check manual then post to list ? Stuart --- Stuart Felenstein [EMAIL PROTECTED] wrote: I'm in the midst of writing out some code that will take data from a huge form and write it to the database. All the tables for insertions are innodb so i'm going (or trying too) with the one stop shop approach - transactions. Question: The first table I write too has as it's first column an auto-inc int. This recordID will serve as the recordID for all the other tables that will need insertions. Meaning the same RecordID must go into each other table. I need to get that recordID first before continuing along with the transaction. But, in my understanding (and I could be wrong) nothing gets written until the very end ? So how do I get the recordID into the other tables. Right now - i do the insert into the first table and that is fine. I imagine , haven't worked it out yet, but following that insertion I need to do a select statement and in the application layer assign that number to a variable. Anyway once again Im running my mouth , so can someone tell me if I'm right or wrong ? and if I'm neither how I can go about keeping the transaction as one step. Thank you, Stuart -- 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 mysql insert it
MySQL doesn't guarantee that there will be no gaps in sequence values. Assigment of the id is always atomic because innodb uses an AUTO_INC lock that lasts for the time of the insert, not the life of the transaction. lets say your highest order number is 10 transaction begins for client 1 insert into orders (...) values (...) mysql_insert_id = 11 ... user adds stuff to order ... aborts order, transaction rolls back at the same time transaction begins for client 2 insert into orders (...) values (...) mysql_insert_id = 12 ... adds some stuff ... commits order there will be a row with an id of 10 and a row with an id of 12 in your database.. id 11 was rolled back and is gone this is all documented here: http://dev.mysql.com/doc/mysql/en/InnoDB_auto-increment_column.html --- Scott Haneda [EMAIL PROTECTED] wrote: I have been pulling my hair out trying to get a solution to something, assuming idiotically that in a transaction scenario I would not be able to get the insert it back out. It seems to work, I am wondering how and if it is reliable. Give the scenario where I have 2 inserts I want to make, since I can not seem to figure out how to make 2 inserts in one statement, I will make 2, with the condition that the second one needs to know the insert id. I just don't see how mysql can know the insert id in a transaction situation, I am assumeing that mysql does not actually insert anything at all untill it sees the COMMIT, so how does it know the insert id at all, the records have not been inserted yet? Is this reliable? Here is some pseudo code that shows what I am doing and commetns where I am confused: mysqlQuery(tConn, START TRANSACTION); repeat 1000 times mysqlQuery(tConn, INSERT into zaddress SET user_id = '123', address = '[EMAIL PROTECTED]'); // How can mysql know about this tID = mysqlInsertID(tConn); mysqlQuery(tConn, INSERT INTO zblacklist SET user_id = '123', id = tID , address = tID); end repeat; mysqlQuery(tConn, COMMIT); -- - Scott HanedaTel: 415.898.2602 http://www.newgeo.com Fax: 313.557.5052 [EMAIL PROTECTED]Novato, CA U.S.A. -- 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 mysql insert it
on 7/22/04 3:54 PM, Justin Swanhart at [EMAIL PROTECTED] wrote: MySQL doesn't guarantee that there will be no gaps in sequence values. Assigment of the id is always atomic because innodb uses an AUTO_INC lock that lasts for the time of the insert, not the life of the transaction. lets say your highest order number is 10 transaction begins for client 1 insert into orders (...) values (...) mysql_insert_id = 11 ... user adds stuff to order ... aborts order, transaction rolls back at the same time transaction begins for client 2 insert into orders (...) values (...) mysql_insert_id = 12 ... adds some stuff ... commits order there will be a row with an id of 10 and a row with an id of 12 in your database.. id 11 was rolled back and is gone Ok, so there can be gaps, but there would never be collisions if id's? -- - Scott HanedaTel: 415.898.2602 http://www.newgeo.com Fax: 313.557.5052 [EMAIL PROTECTED]Novato, CA U.S.A. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: transactions
hi there, i'm using two different database objects, for each connection. i still haven't tried with save points, but i will, to see what append. regards, andré Citando Heikki Tuuri [EMAIL PROTECTED]: Andre, - Original Message - From: [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Sunday, March 07, 2004 3:28 PM Subject: transactions Hello there. i'm working with transactions, and i have one situtation where i start a transaction and execute the insert/update statement, then i execute another start transaction for another db and execute the insert/update, and if this return a error i do the rollback, and then i do the rollback for the first transaction. example: start transaction insert/update start transaction (for another database) insert/update ... rollback or commit rollback or commit (this rollback isn't working) my problem is that the rollback for the first transaction isn't working, and= i don't understand why! can you help me? maybe you should use savepoints. Though, then if you rollback the outer transaction, it will also rollback the inner 'transaction'. If that is not ok, then you must use 2 different connections to MySQL. http://www.innodb.com/ibman.php#Savepoints thanks, Andr=E9 Br=E1s 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 Register now for the 2004 MySQL Users Conference! http://www.mysql.com/events/uc2004/index.html -- 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
Andre, - Original Message - From: [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Sunday, March 07, 2004 3:28 PM Subject: transactions Hello there. i'm working with transactions, and i have one situtation where i start a transaction and execute the insert/update statement, then i execute another start transaction for another db and execute the insert/update, and if this return a error i do the rollback, and then i do the rollback for the first transaction. example: start transaction insert/update start transaction (for another database) insert/update ... rollback or commit rollback or commit (this rollback isn't working) my problem is that the rollback for the first transaction isn't working, and= i don't understand why! can you help me? maybe you should use savepoints. Though, then if you rollback the outer transaction, it will also rollback the inner 'transaction'. If that is not ok, then you must use 2 different connections to MySQL. http://www.innodb.com/ibman.php#Savepoints thanks, Andr=E9 Br=E1s 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 Register now for the 2004 MySQL Users Conference! http://www.mysql.com/events/uc2004/index.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: transactions and create table (was Questions about MySQL implementation)
Bill, http://www.innodb.com/ibman.php#Implicit_commit: 8.7 When does MySQL implicitly commit or rollback a transaction? MySQL has the autocommit mode switched on in a session if you do not do SET AUTOCOMMIT=0. In the autocommit mode MySQL does a commit after each SQL statement, if that statement did not return an error. If an error is returned by an SQL statement, then the commit/rollback behavior depends on the error. See section 13 for details. The following SQL statements cause an implicit commit of the current transaction in MySQL: CREATE TABLE (commits only if version MySQL-4.0.13 and MySQL binlogging is used), ALTER TABLE, BEGIN, START TRANSACTION, CREATE INDEX, DROP DATABASE, DROP TABLE, RENAME TABLE, TRUNCATE, LOCK TABLES, UNLOCK TABLES, SET AUTOCOMMIT=1. The CREATE TABLE statement in InnoDB is processed as a single transaction. It means that a ROLLBACK from the user does not undo CREATE TABLE statements the user made during his transaction. If you have the autocommit mode off and end a connection without calling an explicit COMMIT of your transaction, then MySQL will roll back your transaction. Best regards, Heikki Innobase Oy http://www.innodb.com 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 Order MySQL support from http://www.mysql.com/support/index.html - Alkuperäinen viesti - Lähettäjä: Bill Easton [EMAIL PROTECTED] Vastaanottaja: [EMAIL PROTECTED] Kopio: [EMAIL PROTECTED] Lähetetty: Thursday, December 18, 2003 5:02 PM Aihe: transactions and create table (was Questions about MySQL implementation) What is the official word on doing a CREATE TABLE inside a transaction? Can I do one without causing the transaction to commit? By experiment, it appears that 4.0.14 allows this, although, even if the CREATE TABLE is for an InnoDB table, a ROLLBACK doesn't remove the created table. It appears that CREATE TABLE caused a commit of the current transaction in 3.23.49. I'd like to create a temporary table during a transaction--mostly to be able to emulate things like subqueries and views that will come in some future production version of MySQL. I don't have a problem with the table creation not being rolled back. I tried to RTFM. I did find a note on how transactions are treated for CREATE TABLE SELECT..., but I wasn't able to find a clear statement that CREATE TABLE will no longer force a commit. From: Heikki Tuuri [EMAIL PROTECTED] To: [EMAIL PROTECTED] Subject: Re: Questions about MySQL implementation Date: Thu, 18 Dec 2003 01:18:39 +0200 Chris, - Original Message - From: Chris Nolan [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Saturday, December 13, 2003 7:24 AM Subject: Questions about MySQL implementation [snip] 2. I've been told on good authority (by persons on this fine list) that Sybase and PostgreSQL (and, from personal experience, SQLBase) support ROLLBACK of DDL statements such as DROP TABLE, ALTER TABLE, RENAME TABLE etc. From what I can gather, neither BDB nor InnoDB do this. Does anyone know what sort of technical challenges making the above statements undoable involve over and above INSERT, DELETE and UPDATE statements? Would this functionality be something that MySQL AB / Innobase Oy would be interested in developing should it be sponsored? Not very difficult: we could keep the 'old' table until the transaction commit. In a rollback we would fall back to the old table. But the demand for such a feature is so low that most databases do not have a rollback of DDL statements. [snip] Chris Best regards, Heikki Tuuri Innobase Oy [snip] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Transactions tutorial
Try Microsoft's Inside SQL Server 2000 by Delaney. -Original Message- From: Thomas Svenson [mailto:[EMAIL PROTECTED] Sent: 15 August 2003 01:44 To: MySQL list Subject: Transactions tutorial I'm looking for any online resources/tutorials and such about transactions. Preferable for MySQL, but others will do. Other than that, is there any good book about transactions. Again preferable for MySQL. /T -- 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
Thank you for pointing that out Kaarel you saved me some time. I wonder, Patric, if you ever read the source code of the OS you are developing for or if you ever read the source of the compilers you use because it is most likely that there are bugs and following your argumentation: a feature is there but you have to reimplement it every time you write an app because you are resposible as developer for the propper working of the app you'd have to check all this. But you don't because you expect these things to work as documentet. If I use a RDBMS which supports foreign keys I expect the developers that they have red the theory and standards about it and having them implemented. Because adding a feature to an app and saying - if any problems occure - was just a trail you have to reimplement it is imho absurd. best regards Stephan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Transactions
I'm reticent to consume any more of this lists bandwidth and trust this will end the thread, but here is my point. There are many 'gotchas' to consider when developing an application that uses any dynamic file structure from simple flat ascii files to engorged dbms's. Add multi-user and multi-tasking to the mix and you have a tiger by the tail. The bottom line is, it is the responsibility of the programmer to ensure that every tool they use is safely and correctly implemented. Transactions or commit and rollback levels help, but they are only a small piece of the package. It is dangerous to assume that because referential integrity has been maintained, that the data is as intended. Any application that allows its data to be manipulated in other than a read-only status is responsible for its integrity. Forgive me if I'm missing the point here I have only just finished a database programming course in university and do not have extensive real practice. However I was tought that databases are responsible (among other things) of data integrity and consistency. Yhe client's job is to add data and ask for data. The rest should be database's job. Database ensures there are no violation of business rules (constraints, foreign keys) and that full data not just part of it will be added (transactions). Of course a client must be programmed in a way to make use of these features. But all the things you were talking about really are part of RDBMS definition. appropriate result to the client. You certainly would not want to lock a region or even a row from a stateless client. If two people access the same record/row at the same time and change non-key information but commit the changes sequentially 2 seconds apart the referential integrity could be correct, but the data is not as the first person intended. In this scenario, the programmer needs to provide a mechanism to advise the first client that his changes were overwritten, thereby turning a stateless event into a stateful one and maintaining control of the application. This situation is one of the main reasons you want to use a database. It is database's job to handle multiple simultaneous accesses on the same data/row. This is not an easy task, there are several different locking mechanisms to implement this and different types of transactions for end users/client applications. Database creators have spent a lot of time on implementing these things so it is probably quite wise not to try to invent a bicycle here. A robust file handler or dbms is a wonderful tool but it is only a tool and does not relieve a programmer of their responsibility. That's my point. It is not the APPLICATION programmers responsibility to handle simultaneous accesses to the same data, to ensure data integrity. The application/database user only sends data to database and asks for data. The rest is the job of RDBMS. Thats the whole point of using a RDBMS imho. Kaarel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Transactions
Have you considered writing your own DBMS as well? -Original Message- From: Patrick [mailto:[EMAIL PROTECTED] Sent: 29 July 2003 23:03 To: Dan Nelson Cc: mysqllist Subject: Re: Transactions Well said Dan. While foreign keys, cascades and built-in transactions are convenient, atomicity and referential integrity are readily attained by proper implementation and the appropriate code. soap box Setting buffers,dirty flags and commit functions really is the responsibility of the application programmer. A lack of referential integrity is not the same as data corruption. More creative coding and less whining. /soap box No offense intended, I just need an occasional rant. Pat... - Original Message - From: Dan Nelson [EMAIL PROTECTED] To: b b [EMAIL PROTECTED] Cc: mysqllist [EMAIL PROTECTED] Sent: Tuesday, July 29, 2003 4:59 PM Subject: Re: Transactions In the last episode (Jul 29), b b said: Most web hosting companies run the mysql standard. Which means one can't run transactions. If that is the case, then how do you handle many to many relationships with truely normalized manner without risking data corruption For example: You have an org, ctry tables and a middle table orgCtry. The middle table has the two foriegn keys one from org and one from ctry. This way an org could be operating in many countries and a country could have many organizations. Now to insert an organization AAA that operates in USA, Canada, and Argentina one would need typically to do four sql statements: insert into org(name, . get the newly inserted org ID insert into orgCtry(ID for org, ID for USA) ... insert into orgCtry(ID for org, ID for Canada) ... insert into orgCtry(ID for org, ID for Argentina) ... To do the above securely one has to put it in a transaction. If You really mean To do the above atomically here. You can still do it securely, but you have to make the client alication smarter. If the server crashes after the 2nd insert, you end up with 1 record in orgName and 1 in orgCtry. So when the end-user retries the request, the client has to realize that AAA already exists and simply insert the remaining two records. When you decide to delete the AAA user, make sure you delete dependent records first. So remove the orgCtry records before removing the parent record in org. Otherwise, if the server crashes, you end up with dangling records that you have to clean up in a maintenence script (not difficult, but not necessary if you delete in the right order). Transactions are most important in places where you have to update multiple records or tables, and /cannot/ allow a partial update (double-entry bookkeeping, etc). Foreign keys (and subqueries) are handy to have, but you can always duplicate their functionality with extra code in the client. -- Dan Nelson [EMAIL PROTECTED] -- 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Transactions
I'm reticent to consume any more of this lists bandwidth and trust this will end the thread, but here is my point. There are many 'gotchas' to consider when developing an application that uses any dynamic file structure from simple flat ascii files to engorged dbms's. Add multi-user and multi-tasking to the mix and you have a tiger by the tail. The bottom line is, it is the responsibility of the programmer to ensure that every tool they use is safely and correctly implemented. Transactions or commit and rollback levels help, but they are only a small piece of the package. It is dangerous to assume that because referential integrity has been maintained, that the data is as intended. Any application that allows its data to be manipulated in other than a read-only status is responsible for its integrity. Your example of the web browser is a good one. Using the http protocol and html to hook-up heterogeneous systems has become common and is relatively simple. It is, as you point out, stateless. The browser does not know or care about the success or failure of an operation. It is the responsibility of the person writing the html and any other subsequent code to communicate the appropriate result to the client. You certainly would not want to lock a region or even a row from a stateless client. If two people access the same record/row at the same time and change non-key information but commit the changes sequentially 2 seconds apart the referential integrity could be correct, but the data is not as the first person intended. In this scenario, the programmer needs to provide a mechanism to advise the first client that his changes were overwritten, thereby turning a stateless event into a stateful one and maintaining control of the application. A robust file handler or dbms is a wonderful tool but it is only a tool and does not relieve a programmer of their responsibility. That's my point. Pat... BTW MySQL is one of the best SQL Servers out there, and our SQL Server of choice. - Original Message - From: b b [EMAIL PROTECTED] To: Dan Nelson [EMAIL PROTECTED] Cc: mysqllist [EMAIL PROTECTED] Sent: Tuesday, July 29, 2003 11:11 PM Subject: Re: Transactions Good point. However, I disagree with you on fundemental points. Subsystems within an application should remain indepedant for a easier maintanance and better software development process. Here we have a multi tiered system whereby the database can't guarantee it's integrity without good clients. Thats not very sustainable and defeats the purpose of having functionality distributed among different components. Deleting, updating and insertions should be done correctly on the database level. In the example i gave, there is no guarantee that the client will try to do the insertion again. What if we have more than one client? lets say a web browser. Should we rely on the user hitting reload? Now we are relying on the good practice of the user to keep the db integral Fortunately Scott Helms reminded us that mysql 4 does include the innodb which are transactional tables. I will give that a shot. Thanks for the comment. --- Dan Nelson [EMAIL PROTECTED] wrote: In the last episode (Jul 29), b b said: Most web hosting companies run the mysql standard. Which means one can't run transactions. If that is the case, then how do you handle many to many relationships with truely normalized manner without risking data corruption For example: You have an org, ctry tables and a middle table orgCtry. The middle table has the two foriegn keys one from org and one from ctry. This way an org could be operating in many countries and a country could have many organizations. Now to insert an organization AAA that operates in USA, Canada, and Argentina one would need typically to do four sql statements: insert into org(name, . get the newly inserted org ID insert into orgCtry(ID for org, ID for USA) ... insert into orgCtry(ID for org, ID for Canada) ... insert into orgCtry(ID for org, ID for Argentina) ... To do the above securely one has to put it in a transaction. If You really mean To do the above atomically here. You can still do it securely, but you have to make the client alication smarter. If the server crashes after the 2nd insert, you end up with 1 record in orgName and 1 in orgCtry. So when the end-user retries the request, the client has to realize that AAA already exists and simply insert the remaining two records. When you decide to delete the AAA user, make sure you delete dependent records first. So remove the orgCtry records before removing the parent record in org. Otherwise, if the server crashes, you end up with dangling records that you have to clean up in a maintenence script (not difficult, but not necessary if you delete in the right order). Transactions
Re: Transactions
In the past I have developed MS-SQSQL ASP applications and handed the ASP parts to a junior programmer knowing well that he wouldn't be able to wreck the database (given the triggers, Keys, constraints, indexes and stored procedures that were involved). Yes I agree with you that clients should be programmed correctly too. However, I believe that in the software development process, different components should try to remain as independent as possible with clear interfaces that link them together. In fact, I think that a programmer developing a component (the client for example) ideally wouldn't need to know the inner-workings of the components that other programmers are developing. So if a programmer developing the client has to worry about referential integrity, data corruption and the like which is the job of the db programmer, then I think that the software engineering concept has been violated. True enough, one programmer could be developing all aspects of the applications. But, in my opinion that doesn't mean he has to relax these concepts. That is just my opinion. I may well be wrong. I also agree that MySQL has come a long way and I am glad with the features introduced in version 4. Cheers. PS: by the way I am by no means an expert so to take the pressure of my shoulders, I will forecast asking neive questions in the future. In fact currently I am not able to grant privilages to users using the Grant command for some reason --- Patrick ShSherrillpapatrickococonetom wrote: I'm reticent to consume any more of this lists bandwidth and trust this will end the thread, but here is my point. There are many 'gotchas' to consider when developing an application that uses any dynamic file structure from simple flat asascii files to engorged dbdbms. Add multi-user and multi-tasking to the mix and you have a tiger by the tail. The bottom line is, it is the responsibility of the programmer to ensure that every tool they use is safely and correctly implemented. Transactions or commit and rollback levels help, but they are only a small piece of the package. It is dangerous to assume that because referential integrity has been maintained, that the data is as intended. Any application that allows its data to be manipulated in other than a read-only status is responsible for its integrity. Your example of the web browser is a good one. Using the hthttp protocol and hthtmlo hook-up heterogeneous systems has become common and is relatively simple. It is, as you point out, stateless. The browser does not know or care about the success or failure of an operation. It is the responsibility of the person writing the hthtmlnd any other subsequent code to communicate the appropriate result to the client. You certainly would not want to lock a region or even a row from a stateless client. If two people access the same record/row at the same time and change non-key information but commit the changes sequentially 2 seconds apart the referential integrity could be correct, but the data is not as the first person intended. In this scenario, the programmer needs to provide a mechanism to advise the first client that his changes were overwritten, thereby turning a stateless event into a ststatefulne and maintaining control of the application. A robust file handler or dbdbmss a wonderful tool but it is only a tool and does not relieve a programmer of their responsibility. That's my point. Pat... BTW MyMySQLs one of the best SQSQLervers out there, and our SQSQLerver of choice. - Original Message - From: b b [EMAIL PROTECTED] To: Dan Nelson dndnelsonlallantgroupom Cc: mymysqllistmymysqlists.mymysqlom Sent: Tuesday, July 29, 2003 11:11 PM Subject: Re: Transactions Good point. However, I disagree with you on fufundementaloints. Subsystems within an application should remain inindepedantor a easier mamaintanance and better software development process. Here we have a multi tiered system whereby the database can't guarantee it's integrity without good clients. Thats not very sustainable and defeats the purpose of having functionality distributed among different components. Deleting, updating and insertions should be done correctly on the database level. In the example i gave, there is no guarantee that the client will try to do the insertion again. What if we have more than one client? lets say a web browser. Should we rely on the user hitting reload? Now we are relying on the good practice of the user to keep the db integral Fortunately Scott Helms reminded us that mymysql does include the ininnodbhich are transactional tables. I will give that a shot. Thanks for the comment. --- Dan Nelson dndnelsonlallantgroupom wrote: In the last episode (Jul 29), b b said: Most web hosting companies run the mymysql standard. Which means one can't run
Re: Transactions
In the last episode (Jul 29), b b said: Most web hosting companies run the mysql standard. Which means one can't run transactions. If that is the case, then how do you handle many to many relationships with truely normalized manner without risking data corruption For example: You have an org, ctry tables and a middle table orgCtry. The middle table has the two foriegn keys one from org and one from ctry. This way an org could be operating in many countries and a country could have many organizations. Now to insert an organization AAA that operates in USA, Canada, and Argentina one would need typically to do four sql statements: insert into org(name, . get the newly inserted org ID insert into orgCtry(ID for org, ID for USA) ... insert into orgCtry(ID for org, ID for Canada) ... insert into orgCtry(ID for org, ID for Argentina) ... To do the above securely one has to put it in a transaction. If You really mean To do the above atomically here. You can still do it securely, but you have to make the client alication smarter. If the server crashes after the 2nd insert, you end up with 1 record in orgName and 1 in orgCtry. So when the end-user retries the request, the client has to realize that AAA already exists and simply insert the remaining two records. When you decide to delete the AAA user, make sure you delete dependent records first. So remove the orgCtry records before removing the parent record in org. Otherwise, if the server crashes, you end up with dangling records that you have to clean up in a maintenence script (not difficult, but not necessary if you delete in the right order). Transactions are most important in places where you have to update multiple records or tables, and /cannot/ allow a partial update (double-entry bookkeeping, etc). Foreign keys (and subqueries) are handy to have, but you can always duplicate their functionality with extra code in the client. -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Transactions
Well said Dan. While foreign keys, cascades and built-in transactions are convenient, atomicity and referential integrity are readily attained by proper implementation and the appropriate code. soap box Setting buffers,dirty flags and commit functions really is the responsibility of the application programmer. A lack of referential integrity is not the same as data corruption. More creative coding and less whining. /soap box No offense intended, I just need an occasional rant. Pat... - Original Message - From: Dan Nelson [EMAIL PROTECTED] To: b b [EMAIL PROTECTED] Cc: mysqllist [EMAIL PROTECTED] Sent: Tuesday, July 29, 2003 4:59 PM Subject: Re: Transactions In the last episode (Jul 29), b b said: Most web hosting companies run the mysql standard. Which means one can't run transactions. If that is the case, then how do you handle many to many relationships with truely normalized manner without risking data corruption For example: You have an org, ctry tables and a middle table orgCtry. The middle table has the two foriegn keys one from org and one from ctry. This way an org could be operating in many countries and a country could have many organizations. Now to insert an organization AAA that operates in USA, Canada, and Argentina one would need typically to do four sql statements: insert into org(name, . get the newly inserted org ID insert into orgCtry(ID for org, ID for USA) ... insert into orgCtry(ID for org, ID for Canada) ... insert into orgCtry(ID for org, ID for Argentina) ... To do the above securely one has to put it in a transaction. If You really mean To do the above atomically here. You can still do it securely, but you have to make the client alication smarter. If the server crashes after the 2nd insert, you end up with 1 record in orgName and 1 in orgCtry. So when the end-user retries the request, the client has to realize that AAA already exists and simply insert the remaining two records. When you decide to delete the AAA user, make sure you delete dependent records first. So remove the orgCtry records before removing the parent record in org. Otherwise, if the server crashes, you end up with dangling records that you have to clean up in a maintenence script (not difficult, but not necessary if you delete in the right order). Transactions are most important in places where you have to update multiple records or tables, and /cannot/ allow a partial update (double-entry bookkeeping, etc). Foreign keys (and subqueries) are handy to have, but you can always duplicate their functionality with extra code in the client. -- Dan Nelson [EMAIL PROTECTED] -- 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
Good point. However, I disagree with you on fundemental points. Subsystems within an application should remain indepedant for a easier maintanance and better software development process. Here we have a multi tiered system whereby the database can't guarantee it's integrity without good clients. Thats not very sustainable and defeats the purpose of having functionality distributed among different components. Deleting, updating and insertions should be done correctly on the database level. In the example i gave, there is no guarantee that the client will try to do the insertion again. What if we have more than one client? lets say a web browser. Should we rely on the user hitting reload? Now we are relying on the good practice of the user to keep the db integral Fortunately Scott Helms reminded us that mysql 4 does include the innodb which are transactional tables. I will give that a shot. Thanks for the comment. --- Dan Nelson [EMAIL PROTECTED] wrote: In the last episode (Jul 29), b b said: Most web hosting companies run the mysql standard. Which means one can't run transactions. If that is the case, then how do you handle many to many relationships with truely normalized manner without risking data corruption For example: You have an org, ctry tables and a middle table orgCtry. The middle table has the two foriegn keys one from org and one from ctry. This way an org could be operating in many countries and a country could have many organizations. Now to insert an organization AAA that operates in USA, Canada, and Argentina one would need typically to do four sql statements: insert into org(name, . get the newly inserted org ID insert into orgCtry(ID for org, ID for USA) ... insert into orgCtry(ID for org, ID for Canada) ... insert into orgCtry(ID for org, ID for Argentina) ... To do the above securely one has to put it in a transaction. If You really mean To do the above atomically here. You can still do it securely, but you have to make the client alication smarter. If the server crashes after the 2nd insert, you end up with 1 record in orgName and 1 in orgCtry. So when the end-user retries the request, the client has to realize that AAA already exists and simply insert the remaining two records. When you decide to delete the AAA user, make sure you delete dependent records first. So remove the orgCtry records before removing the parent record in org. Otherwise, if the server crashes, you end up with dangling records that you have to clean up in a maintenence script (not difficult, but not necessary if you delete in the right order). Transactions are most important in places where you have to update multiple records or tables, and /cannot/ allow a partial update (double-entry bookkeeping, etc). Foreign keys (and subqueries) are handy to have, but you can always duplicate their functionality with extra code in the client. -- Dan Nelson [EMAIL PROTECTED] __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Transactions
At 13:08 -0700 7/29/03, b b wrote: Most web hosting companies run the mysql standard. Which means one can't run transactions. If that is the Depends. As of MySQL 4, InnoDB (which provides transactional tables) is included by default. case, then how do you handle many to many relationships with truely normalized manner without risking data corruption -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: transactions with php
On 18-Jun-2003 Becoming Digital wrote:
...
if(!mysql_query($array[$i])) {
$flag = false;
break;
}
after the loop I do
if($flag)
mysql_query(commit);
else
mysql_query(rollback);
Be careful with that last if() statement. I would either change the
script to
read '$flag = true' or 'if( isset($flag) )'
It's quite possible that I'm mistaken, but I believe that 'if($flag)'
would
evaluate to false if '$flag = false'.
The constant FALSE (or false) will never evaluate as a TRUE condition.
if ($flag = true) // this assigns TRUE to $flag and then the
// value of $flag will be evaluated.
if (isset($flag)) // will evaluate as TRUE because $flag has a value
// assigned. It's not NULL.
// -- regardless of what the value is.
if ($flag) {
-- or (assuming $flag is initialized) --
if ($flag === TRUE) {
... so either would be the correct construct here.
Regards,
--
Don Read [EMAIL PROTECTED]
-- It's always darkest before the dawn. So if you are going to
steal the neighbor's newspaper, that's the time to do it.
(53kr33t w0rdz: sql table query)
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: transactions with php
If you're using a non-persistent connection, PHP will close the connection when the script terminates, and the MySQL server will roll back implicitly. For a non-persistent connection, the connection remains open and you may not see the rollback behavior your expect. I thought this was fixable now. A while back Monty said that changing users on a connection would reset the connection automatically. He was talking about the next version (which was several versions back of the 4 series). Resetting the connection (according to this theory) would set all the per connection variables to their default and rollback any non-committed transactions. My PHP Mysql extension is a bit hacked up (and I have more to do!), so I can't remember the default now. But I think it should add a 'change user' command when the page ends on any persistent connection. Change to a blank user. So in theory then, web pages would be safe for transactions. A really ugly hack (assuming a Mysql server version as described above) would be (in PHP) to connect persistently to mysql then change the user to a dummy, and then change the user to the one you want again. Doing this at the start of every page should then make it transaction safe. Can someone from MySQL confirm that changing users will reset the connection and rollback unfinished transactions? And starting in what version? -steve- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: transactions with php
In http://www.mysql.com/doc/en/News-4.0.6.html * mysql_change_user() will now reset the connection to the state of a fresh connect (Ie, ROLLBACK any active transaction, close all temporary tables, reset all user variables etc..) So it is in there, starting with version MySQL 4.0.6. -steve- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: transactions with php
At 10:44 -0700 6/17/03, Jonas Geiregat wrote: I'm trying to implement transaction (mysql) in my php code. Could anyone tell me if this is a good way of using them. I make an array with all the query's I loop through them. like this mysql_query($qarray[$i])or die($flag = false);. After the loop I do if($flag) commit; else rollback; Is this a good way of implementing them ? die() will terminate your script, so you will not ever actually get a chance to roll back. If you're using a non-persistent connection, PHP will close the connection when the script terminates, and the MySQL server will roll back implicitly. For a non-persistent connection, the connection remains open and you may not see the rollback behavior your expect. If you're looping through the statements, why not set the flag if any of them fail and exit the loop immediately? Then commit or roll back depending on the value of the flag at the end of the loop. -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: transactions with php
I found one item in the PHP Classes Repository that may be of use. Unfortunately, the comments are in Spanish, so it's up to you to figure out the code. http://promoxy.mirrors.phpclasses.org/browse.html/file/3077.html Edward Dudlik Becoming Digital www.becomingdigital.com - Original Message - From: Jonas Geiregat [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, 17 June, 2003 13:44 Subject: transactions with php I'm trying to implement transaction (mysql) in my php code. Could anyone tell me if this is a good way of using them. I make an array with all the query's I loop through them. like this mysql_query($qarray[$i])or die($flag = false);. After the loop I do if($flag) commit; else rollback; Is this a good way of implementing them ? -- 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 with php
Becoming Digital wrote:
I found one item in the PHP Classes Repository that may be of use.
Unfortunately, the comments are in Spanish, so it's up to you to figure out the
code.
http://promoxy.mirrors.phpclasses.org/browse.html/file/3077.html
Edward Dudlik
Becoming Digital
www.becomingdigital.com
- Original Message -
From: Jonas Geiregat [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Tuesday, 17 June, 2003 13:44
Subject: transactions with php
I'm trying to implement transaction (mysql) in my php code. Could anyone
tell me if this is a good way of using them. I make an array with all
the query's I loop through them. like this mysql_query($qarray[$i])or
die($flag = false);. After the loop I do if($flag) commit; else rollback;
Is this a good way of implementing them ?
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
This is a much better implementation then my previous one
I loop trough a array with query's.
in the loop I do:
if(!mysql_query($array[$i])) {
$flag = false;
break;
}
after the loop I do
if($flag)
mysql_query(commit);
else
mysql_query(rollback);
This should work haven't tested it yet. If anyone got better idea's Post
them Regards Jonas Geiregat
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: transactions with php
Becoming Digital wrote:
I found one item in the PHP Classes Repository that may be of use.
Unfortunately, the comments are in Spanish, so it's up to you to
figure out the
code.
http://promoxy.mirrors.phpclasses.org/browse.html/file/3077.html
Edward Dudlik
Becoming Digital
www.becomingdigital.com
- Original Message -
From: Jonas Geiregat [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Tuesday, 17 June, 2003 13:44
Subject: transactions with php
I'm trying to implement transaction (mysql) in my php code. Could anyone
tell me if this is a good way of using them. I make an array with all
the query's I loop through them. like this mysql_query($qarray[$i])or
die($flag = false);. After the loop I do if($flag) commit; else rollback;
Is this a good way of implementing them ?
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
This is a much better implementation then my previous one
I loop trough a array with query's.
in the loop I do:
if(!mysql_query($array[$i])) {
$flag = false;
break;
}
after the loop I do
if($flag)
mysql_query(commit);
else
mysql_query(rollback);
This should work haven't tested it yet. If anyone got better idea's Post
them Regards Jonas Geiregat
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: transactions with php
if(!mysql_query($array[$i])) {
$flag = false;
break;
}
after the loop I do
if($flag)
mysql_query(commit);
else
mysql_query(rollback);
Be careful with that last if() statement. I would either change the script to
read '$flag = true' or 'if( isset($flag) )'
It's quite possible that I'm mistaken, but I believe that 'if($flag)' would
evaluate to false if '$flag = false'.
Edward Dudlik
Becoming Digital
www.becomingdigital.com
- Original Message -
From: Jonas Geiregat [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Tuesday, 17 June, 2003 15:19
Subject: Re: transactions with php
Becoming Digital wrote:
I found one item in the PHP Classes Repository that may be of use.
Unfortunately, the comments are in Spanish, so it's up to you to
figure out the
code.
http://promoxy.mirrors.phpclasses.org/browse.html/file/3077.html
Edward Dudlik
Becoming Digital
www.becomingdigital.com
- Original Message -
From: Jonas Geiregat [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Tuesday, 17 June, 2003 13:44
Subject: transactions with php
I'm trying to implement transaction (mysql) in my php code. Could anyone
tell me if this is a good way of using them. I make an array with all
the query's I loop through them. like this mysql_query($qarray[$i])or
die($flag = false);. After the loop I do if($flag) commit; else rollback;
Is this a good way of implementing them ?
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
This is a much better implementation then my previous one
I loop trough a array with query's.
in the loop I do:
if(!mysql_query($array[$i])) {
$flag = false;
break;
}
after the loop I do
if($flag)
mysql_query(commit);
else
mysql_query(rollback);
This should work haven't tested it yet. If anyone got better idea's Post
them Regards Jonas Geiregat
--
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
Hi. On Sun 2003-03-09 at 11:34:33 -0500, [EMAIL PROTECTED] wrote: From what I understand, transactions are a kind of protection that prevents certain commands from executing if certain other conditions haven't been met. Not completely. They can do much more. Another way to look at transactions is to think about them as a possibility to execute several commands with the advantages (and guarantees) of a single statement, that includes as main points: - all or none of the statements are submitted (if an error occurs, all statements are rolled back) - other threads can only see the effects of any of the statements, when the transaction is committed. And this is particularly important for ecommerce, among other things. Do I more or less have that right? What is important for ecommerce are the ACID criteria (that are guarantees: Atomicity, Consistency, Integrity, Durability - you may want to look that term up), which require transactions to be implemented. Some people use the terms transactions in a wrong way by implying that an RDBMS with transaction support automatically complies with the ACID criteria, which is wrong. It just happens that most RDBMS which support the one also comply to the other. The most abused example is that a bank that wants to transfer money from one account to another. To update the balances you would do something like: UPDATE account SET balance=balance-100 WHERE id=2 UPDATE account SET balance=balance+100 WHERE id=1 Without transactions it may happen that you end up with changing only one of the two accounts. And there are a lot of possibilities why this could happen (power outage, error in the update, network problems, whatever). Some of them can be easily checked and worked-around in application code, some of them can only be handled by changing the database design and application logic a lot to accomodate the risks. My main question, tho', is: Does the latest version of MySQL available in production mode, not a beta or less, have this transactions capability yet? MySQL 3.23 is declared stable since Jan 2001, i.e. more than two years, and had transactions support even longer. HTH, Benjamin. -- [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: transactions...
From: Dana Diederich [EMAIL PROTECTED] Date: Mon, 23 Sep 2002 09:40:45 -0500 Perhaps the InnoDB/MyISAM gurus can comment on this. I want to switch from MyISAM to Innodb, because we have database tables that have many updates, inserts, deletes and selects going on at the same time. My belief is that InnoDB's better locking semantics (as compared to MyISAM) will give me better overall performance in such a high contention environment. For example, each update won't block other updates or selects or deletes. Is this a fair assumption? Honestly, the 'other' features of InnoDB (transactions and reliability) aren't as important to me as overall speed. Setup a test system and try it. I saw the benchmarks indicating a speed improvement with innoDB and tried it within my system. My longest query program took two times longer, not exactly the desired result [I used the stock settings, perhaps tweaking might have improved that result]. However, I'm doing SELECT primarily, and the results are not surprising apparently. Try it, YMMV of course. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: transactions...
On Mon, 23 Sep 2002 08:40:59 +0300 (EEST) Iikka Meriläinen [EMAIL PROTECTED] wrote: On Mon, 23 Sep 2002, Daniel Kiss wrote: Hi! At 00:56 2002.09.23._ -0300, you wrote: Do I loose too much performance using InnoDB tables in autocommit mode instead of using MyISAM tables? The real quiestion is: Why do you want to use InnoDB tables when you don't want to use its transaction safe features? The reason for prefering InnoDB over MyISAM? Performance. Most benchmarks show InnoDB is slightly faster than MyISAM, and not to mention its some other benefits. But the performance could be a reason on its own to use InnoDB. I don't mean that MyISAM is bad, not at all. It's simpler to use, after all. But what I mean is that InnoDB can be used in all tasks MyISAM is suitable for, and some tasks will be done faster using InnoDB. But some tasks are really bad in InnoDB, at most selects and worst if it comes to table-scans. Thomas mysql, query - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: transactions...
Perhaps the InnoDB/MyISAM gurus can comment on this. I want to switch from MyISAM to Innodb, because we have database tables that have many updates, inserts, deletes and selects going on at the same time. My belief is that InnoDB's better locking semantics (as compared to MyISAM) will give me better overall performance in such a high contention environment. For example, each update won't block other updates or selects or deletes. Is this a fair assumption? Honestly, the 'other' features of InnoDB (transactions and reliability) aren't as important to me as overall speed. Cheers. -Dana -Original Message- From: Thomas Seifert [SMTP:[EMAIL PROTECTED]] Sent: Monday, September 23, 2002 2:14 AM To: [EMAIL PROTECTED] Subject: Re: transactions... On Mon, 23 Sep 2002 08:40:59 +0300 (EEST) Iikka Meriläinen [EMAIL PROTECTED] wrote: On Mon, 23 Sep 2002, Daniel Kiss wrote: Hi! At 00:56 2002.09.23._ -0300, you wrote: Do I loose too much performance using InnoDB tables in autocommit mode instead of using MyISAM tables? The real quiestion is: Why do you want to use InnoDB tables when you don't want to use its transaction safe features? The reason for prefering InnoDB over MyISAM? Performance. Most benchmarks show InnoDB is slightly faster than MyISAM, and not to mention its some other benefits. But the performance could be a reason on its own to use InnoDB. I don't mean that MyISAM is bad, not at all. It's simpler to use, after all. But what I mean is that InnoDB can be used in all tasks MyISAM is suitable for, and some tasks will be done faster using InnoDB. But some tasks are really bad in InnoDB, at most selects and worst if it comes to table-scans. Thomas mysql, query - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php ** This email and any files transmitted with it are confidential and intended solely for the individual or entity to whom they are addressed. If you have received this email in error destroy it immediately. ** Wal-Mart Stores, Inc. Confidential ** - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: transactions...
At 9:40 -0500 9/23/02, Dana Diederich wrote: Perhaps the InnoDB/MyISAM gurus can comment on this. I want to switch from MyISAM to Innodb, because we have database tables that have many updates, inserts, deletes and selects going on at the same time. My belief is that InnoDB's better locking semantics (as compared to MyISAM) will give me better overall performance in such a high contention environment. For example, each update won't block other updates or selects or deletes. Is this a fair assumption? Yes. InnoDB's row-level locking fares better when your query mix contains many updates, rather than just a lot of selects. MyISAM tables use table-level locking, which works well for a mix of selects, but degrades when you start throwing updates into the mix. Honestly, the 'other' features of InnoDB (transactions and reliability) aren't as important to me as overall speed. Cheers. -Dana - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: transactions...
We're probably 98 percent selects. But that's part of the problem: the selects build up at a fantastic rate when there are a flury of updates. Hopefully, once we get to InnoDB, this won't be as likely to happen. Cheers. -Dana -Original Message- From: Paul DuBois [SMTP:[EMAIL PROTECTED]] Sent: Monday, September 23, 2002 10:26 AM To: Dana Diederich; [EMAIL PROTECTED] Subject: RE: transactions... At 9:40 -0500 9/23/02, Dana Diederich wrote: Perhaps the InnoDB/MyISAM gurus can comment on this. I want to switch from MyISAM to Innodb, because we have database tables that have many updates, inserts, deletes and selects going on at the same time. My belief is that InnoDB's better locking semantics (as compared to MyISAM) will give me better overall performance in such a high contention environment. For example, each update won't block other updates or selects or deletes. Is this a fair assumption? Yes. InnoDB's row-level locking fares better when your query mix contains many updates, rather than just a lot of selects. MyISAM tables use table-level locking, which works well for a mix of selects, but degrades when you start throwing updates into the mix. Honestly, the 'other' features of InnoDB (transactions and reliability) aren't as important to me as overall speed. Cheers. -Dana ** This email and any files transmitted with it are confidential and intended solely for the individual or entity to whom they are addressed. If you have received this email in error destroy it immediately. ** Wal-Mart Stores, Inc. Confidential ** - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: transactions...
Joao, I think I've already asked this before, but I will try again. Do I loose too much performance using InnoDB tables in autocommit mode instead of using MyISAM tables? Possibly, but too much is really relative on what your needs are. What are you trying to achieve and what is more important to you: * speed * reliability * ability to roll back commits mysql,query ?? DSL -- I reniad lin ne mor, nuithannen In gwidh ristennin, i fae narchannen I lach Anor ed ardhon gwannen (Soundtrack LOTR) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: transactions...
Hi! At 00:56 2002.09.23._ -0300, you wrote: Do I loose too much performance using InnoDB tables in autocommit mode instead of using MyISAM tables? The real quiestion is: Why do you want to use InnoDB tables when you don't want to use its transaction safe features? Anyway yes. I'm sure it is not worth using InnoDB tables in autocommit mode. If you want to do this use MyISAM instead. Bye, Daniel (mysql) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: transactions...
On Mon, 23 Sep 2002, Daniel Kiss wrote: Hi! At 00:56 2002.09.23._ -0300, you wrote: Do I loose too much performance using InnoDB tables in autocommit mode instead of using MyISAM tables? The real quiestion is: Why do you want to use InnoDB tables when you don't want to use its transaction safe features? The reason for prefering InnoDB over MyISAM? Performance. Most benchmarks show InnoDB is slightly faster than MyISAM, and not to mention its some other benefits. But the performance could be a reason on its own to use InnoDB. I don't mean that MyISAM is bad, not at all. It's simpler to use, after all. But what I mean is that InnoDB can be used in all tasks MyISAM is suitable for, and some tasks will be done faster using InnoDB. Best regards, Iikka ** * Iikka Meriläinen * * E-mail: [EMAIL PROTECTED] * * Vaala, Finland * ** sql - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Transactions not supported by database - Perl
I have expereience the same behaviour when using the ppm from
activestate. Try using the one below, seem to work for me...
ppm install http://theoryx5.uwinnipeg.ca/ppmpackages/DBD-mysql.ppd
-Original Message-
From: Gerald Clark [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, August 14, 2002 9:33 PM
To: Bob Boden
Cc: [EMAIL PROTECTED]
Subject: Re: Transactions not supported by database - Perl
Send the set autocommit=0 just as you send any other query in perl.
Bob Boden wrote:
Attempts to disable auto-commit mode in Perl using the DBI command
$dbh-{AutoCommit} = 0; returns the following message, Transactions
not
supported by database.
I am using the latest version of MySQL-Max (3.23.51) as well as the
latest
version of DBD::mysql.
Executing the following command at the mysql prompt verifies that BDB
Transaction enabled tables are supported and turned on.
mysql SHOW VARIABLES LIKE have_%;
+---+--+
| Variable_name | Value|
+---+--+
| have_bdb | YES |
| have_gemini | NO |
| have_innodb | DISABLED |
| have_isam | YES |
| have_raid | NO |
| have_openssl | NO |
+---+--+
6 rows in set (0.00 sec)
Executing SET AUTOCOMMIT=0; at the mysql prompt works fine as well.
However,
I can not get Perl scripts to recognize the disable auto-commit
command.
These symptoms are occurring in a Linux/apache web server as well as a
Windows 2000 environment running as localhost.
Any suggestions would be greatly appreciated.
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail
[EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail
[EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Transactions not supported by database - Perl
Anyone know of an email marketing software suite that works well with MySQL on the backend? I've seen stuff that works with ODBC db connectivity but I'm not interested in moving to MS SQL or Access. Any feedback is appreciated Thanks Mark - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Transactions not supported by database - Perl
Send the set autocommit=0 just as you send any other query in perl.
Bob Boden wrote:
Attempts to disable auto-commit mode in Perl using the DBI command
$dbh-{AutoCommit} = 0; returns the following message, Transactions not
supported by database.
I am using the latest version of MySQL-Max (3.23.51) as well as the latest
version of DBD::mysql.
Executing the following command at the mysql prompt verifies that BDB
Transaction enabled tables are supported and turned on.
mysql SHOW VARIABLES LIKE have_%;
+---+--+
| Variable_name | Value|
+---+--+
| have_bdb | YES |
| have_gemini | NO |
| have_innodb | DISABLED |
| have_isam | YES |
| have_raid | NO |
| have_openssl | NO |
+---+--+
6 rows in set (0.00 sec)
Executing SET AUTOCOMMIT=0; at the mysql prompt works fine as well. However,
I can not get Perl scripts to recognize the disable auto-commit command.
These symptoms are occurring in a Linux/apache web server as well as a
Windows 2000 environment running as localhost.
Any suggestions would be greatly appreciated.
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail
[EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: transactions on MySQL (innoDB)
please let us know the SQL query you are trying, and a DESCRIBE TABLENAME so we can see how your table(s) are laid out. also, please tell us what you actually changed in your my.cnf file by the way, before you can do transactions, you must set the AUTOCOMMIT variable to 0 -Ryan Hatch a a wrote: Hello I can't create innoDB tables in db MySQL to do rollback and commit transactions. I try to modify the my.cnf file with the instructions that I find in manuel.pdf but it don't work. Can you help me please ? Thanks. Ps : i'm french. :-) ___ Do You Yahoo!? -- Une adresse @yahoo.fr gratuite et en français ! Yahoo! Mail : http://fr.mail.yahoo.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: transactions on MySQL (innoDB)
First question: did you set autocommit=0? --Walt Weaver Bozeman, Montana -Original Message- From: a a [mailto:[EMAIL PROTECTED]] Sent: Wednesday, May 22, 2002 9:27 AM To: [EMAIL PROTECTED] Subject: transactions on MySQL (innoDB) Hello I can't create innoDB tables in db MySQL to do rollback and commit transactions. I try to modify the my.cnf file with the instructions that I find in manuel.pdf but it don't work. Can you help me please ? Thanks. Ps : i'm french. :-) ___ Do You Yahoo!? -- Une adresse @yahoo.fr gratuite et en français ! Yahoo! Mail : http://fr.mail.yahoo.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: transactions
John, Thursday, May 02, 2002, 3:17:59 PM, you wrote: JN I would greately appreciate if someone could advise me on which version of JN MySQL supports transactions ? Transaction is supported with transaction-safe tables (InnoDB, BDB). http://www.mysql.com/doc/T/a/Table_types.html JN Thanks, JN John -- For technical support contracts, goto https://order.mysql.com/ This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: transactions
Dear all, The message I enclose is dated almost a year back. I would greately appreciate if you let me know if transaction support in MySQL has stabilised in sub-sequent releases ? secondly, if one does use transactions by converting to BDB of InnoDB what is the impact on indexing/ speed of access, etc. v/s ISAM ? Thanks, John PT FirstWAP, Jakarta, Indonesia - Original Message - From: Don Read [EMAIL PROTECTED] To: Simon Green [EMAIL PROTECTED] Cc: [EMAIL PROTECTED]; Pete Kuczynski [EMAIL PROTECTED] Sent: Monday, July 16, 2001 10:26 PM Subject: RE: transactions On 16-Jul-01 Simon Green wrote: MySQL Will not do transactions till V4... But there are ways to get around this.. If you use BDB or Innobase table types, then transactions _are_ supported. Since ~ 3.23.26 IIR; no idea if the code is considered stable ... http://www.mysql.com/doc/C/O/COMMIT.html http://www.mysql.com/doc/S/E/SET_TRANSACTION.html Regards, -- Don Read [EMAIL PROTECTED] -- It's always darkest before the dawn. So if you are going to steal the neighbor's newspaper, that's the time to do it. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: --Transactions
On Wed, 10 Apr 2002 13:27:30 +0300 maxim [EMAIL PROTECTED] wrote: Hello , i've just started work with MySQL. Does anybody know how can i use transaction with MySQL? Is this capability supported? I write web-shop... Sory my eng is bed... what term do you mean with transaction ? if you mean ... you want to build web application using mysql, the answer is absolutely YES ! ;-) :D -- How should I know if it works? That's what beta testers are for. I only coded it. -- Attributed to Linus Torvalds, somewhere in a posting - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: --Transactions
On Wed, 10 Apr 2002, maxim wrote: Hello , i've just started work with MySQL. Does anybody know how can i use transaction with MySQL? Is this capability supported? I write web-shop... Sory my eng is bed... Maxim, MySQL supports different table types. Some of them (BDB and InnoDB) support transactions. Others (like MyISAM) do not. Being new to MySQL is a good reason to study the manual. It's all there. Cheers, Thomas - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: transactions, referntial integrity
On Wed, Apr 03, 2002 at 01:58:55PM -0500, Kevin D wrote: I've been reading the docs but I just want to verify. It seems like the latest version of MySQL support transactions and referential integrity. Is this correct? Does MySQL now also support stored procedures? BDB and InnoDB tables both have transactions. InnoDB also has referential integrity. Nobody has bloated MySQL with stored procedures yet. :-) Jeremy -- Jeremy D. Zawodny, [EMAIL PROTECTED] Technical Yahoo - Yahoo Finance Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936 MySQL 3.23.47-max: up 55 days, processed 1,528,903,333 queries (320/sec. avg) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: transactions, referntial integrity
no stored procedures yet. maybe vers 4.1 -Original Message- From: Kevin D [mailto:[EMAIL PROTECTED]] Sent: Wednesday, April 03, 2002 12:59 PM To: [EMAIL PROTECTED] Subject: transactions, referntial integrity I've been reading the docs but I just want to verify. It seems like the latest version of MySQL support transactions and referential integrity. Is this correct? Does MySQL now also support stored procedures? Thanks, Kevin - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: transactions support in mysql
On Wed, Jul 18, 2001 at 11:40:15AM +0530, Chandrashekhar wrote: Are transactions supported in mysql.Please guide me. They are. See the manual: http://www.mysql.com/doc/ Jeremy -- Jeremy D. Zawodny, [EMAIL PROTECTED] Technical Yahoo - Yahoo Finance Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936 -- NEW MySQL 3.23.29: up 31 days, processed 239,255,297 queries (86/sec. avg) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: transactions support in mysql
Thanks for your prompt reply. I would like to know which version of mysql does supports transactions. and is it stable? Thanks again. with regards, chandrashekhar - Original Message - From: Jeremy Zawodny [EMAIL PROTECTED] To: Chandrashekhar [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Wednesday, July 18, 2001 12:01 PM Subject: Re: transactions support in mysql On Wed, Jul 18, 2001 at 11:40:15AM +0530, Chandrashekhar wrote: Are transactions supported in mysql.Please guide me. They are. See the manual: http://www.mysql.com/doc/ Jeremy -- Jeremy D. Zawodny, [EMAIL PROTECTED] Technical Yahoo - Yahoo Finance Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936 -- NEW MySQL 3.23.29: up 31 days, processed 239,255,297 queries (86/sec. avg) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: transactions support in mysql
The various table types are discussed here: http://www.mysql.com/doc/T/a/Table_types.html Maurice On Wed, Jul 18, 2001 at 02:12:37PM +0530, Chandrashekhar wrote: Thanks for your prompt reply. I would like to know which version of mysql does supports transactions. and is it stable? Thanks again. with regards, chandrashekhar - Original Message - From: Jeremy Zawodny [EMAIL PROTECTED] To: Chandrashekhar [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Wednesday, July 18, 2001 12:01 PM Subject: Re: transactions support in mysql On Wed, Jul 18, 2001 at 11:40:15AM +0530, Chandrashekhar wrote: Are transactions supported in mysql.Please guide me. They are. See the manual: http://www.mysql.com/doc/ Jeremy -- Jeremy D. Zawodny, [EMAIL PROTECTED] Technical Yahoo - Yahoo Finance Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936 -- NEW MySQL 3.23.29: up 31 days, processed 239,255,297 queries (86/sec. avg) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: transactions
Thanks Monty! will this work on NT as well? or do I need to move to Linux. I don't much care if the tables are BDB or InnoDB, just that I have transaction support. Good luch with the Nusphere thing! Pete Michael Widenius wrote: hi! Pete == Pete Kuczynski [EMAIL PROTECTED] writes: Pete Hi, Pete I updated my tables to BDB, but it still shows MYISAM. Pete Do I need to remove mysql and reinstall it compiled for BDB like the Pete manual suggests? I'm using nusphere's mysql on NT4 server, so I'm not Pete shure that's even possible. Pete http://www.mysql.com/doc/B/D/BDB_install.html Pete Pete I don't think NuSphere MySQL version supports BDB tables. If you want to try these out, you should download MySQL-Max from http://www.mysql.com/downloads/mysql-max-3.23.html With NuSphere's distribution, you should be able to use GEMINI tables instead of BDB tables. Have you tried this? Regards, Monty - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php -- ___ Pete Kuczynski Sr. Field Engineer DHL Airways Inc. Infrastructure Technology Services (773)-462-9758 24/7 Helpdesk 1-800-434-5767 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: transactions
Pete Kuczynski writes: Thanks Monty! will this work on NT as well? or do I need to move to Linux. I don't much care if the tables are BDB or InnoDB, just that I have transaction support. Good luch with the Nusphere thing! Pete -- ___ Pete Kuczynski Sr. Field Engineer DHL Airways Inc. Infrastructure Technology Services (773)-462-9758 24/7 Helpdesk 1-800-434-5767 Download MySQL-Max binary for NT ... That will do ... -- Regards, __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Sinisa Milivojevic [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, FullTime Developer /_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: transactions
Hi! Pete == Pete Kuczynski [EMAIL PROTECTED] writes: Pete Thanks Monty! Pete will this work on NT as well? or do I need to move to Linux. Yes; We have MySQL-Max for both NT and Linux. Pete I don't much care if the tables are BDB or InnoDB, just that I have Pete transaction support. ok. Pete Good luch with the Nusphere thing! Thanks! Regards, Monty - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: transactions
MySQL supports transactions if you are using either InnoDB or Gemini table types. Cal * * Cal Evans * Senior Internet Dreamer * http://www.calevans.com * - Original Message - From: Pete Kuczynski [EMAIL PROTECTED] To: [EMAIL PROTECTED]; Carsten H. Pedersen [EMAIL PROTECTED] Sent: Monday, July 16, 2001 09:57 Subject: transactions Can anyone tell me for shure if my version of mysql supports transactions. Version: 3.23.37 The bitbybit web site states that is does not, but someone told me that it does, help! MySQL doesn't support transactions. If you don't know what transactions are, chances are you'll never need them. Transactions are most widely used in systems where complex updates of many tables concurrently is important and you must be absolutely sure that all things either go right or don't happen at all. That's why banking systems are heavily dependent on transactions. The rest of us, however, can usually live without 'em. -- Hi Pete To emable logging use the --log option eg --log-update when stating the server... Simon ___ Pete Kuczynski Sr. Field Engineer DHL Airways Inc. Infrastructure Technology Services (773)-462-9758 24/7 Helpdesk 1-800-434-5767 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: transactions
MySQL Will not do transactions till V4... But there are ways to get around this.. Simon -Original Message- From: Pete Kuczynski [mailto:[EMAIL PROTECTED]] Sent: 16 July 2001 15:57 To: [EMAIL PROTECTED]; Carsten H. Pedersen Subject: transactions Can anyone tell me for shure if my version of mysql supports transactions. Version: 3.23.37 The bitbybit web site states that is does not, but someone told me that it does, help! MySQL doesn't support transactions. If you don't know what transactions are, chances are you'll never need them. Transactions are most widely used in systems where complex updates of many tables concurrently is important and you must be absolutely sure that all things either go right or don't happen at all. That's why banking systems are heavily dependent on transactions. The rest of us, however, can usually live without 'em. -- Hi Pete To emable logging use the --log option eg --log-update when stating the server... Simon ___ Pete Kuczynski Sr. Field Engineer DHL Airways Inc. Infrastructure Technology Services (773)-462-9758 24/7 Helpdesk 1-800-434-5767 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: transactions
On Mon, 16 Jul 2001 09:57:26 -0500, Pete Kuczynski [EMAIL PROTECTED] wrote: Can anyone tell me for shure if my version of mysql supports transactions. Version: 3.23.37 The bitbybit web site states that is does not, but someone told me that it does, help! We use, on a shared MySQL server for another account, 3.23.32 w/BDB tables which supports Transaction and has worked with no problems. As far using transactions with InnoDB, not sure about that one. but it does come with the lastest release I believe. Mike(mickalo)Blezien =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Thunder Rain Internet Publishing Providing Internet Solutions that work! http://www.thunder-rain.com Tel: 1(225)686-2002 =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: transactions
And Berkeley BDB tables! On Mon, 16 Jul 2001 10:04:41 -0500, Cal Evans [EMAIL PROTECTED] wrote: MySQL supports transactions if you are using either InnoDB or Gemini table types. Cal * * Cal Evans * Senior Internet Dreamer * http://www.calevans.com * - Original Message - From: Pete Kuczynski [EMAIL PROTECTED] To: [EMAIL PROTECTED]; Carsten H. Pedersen [EMAIL PROTECTED] Sent: Monday, July 16, 2001 09:57 Subject: transactions Can anyone tell me for shure if my version of mysql supports transactions. Version: 3.23.37 The bitbybit web site states that is does not, but someone told me that it does, help! MySQL doesn't support transactions. If you don't know what transactions are, chances are you'll never need them. Transactions are most widely used in systems where complex updates of many tables concurrently is important and you must be absolutely sure that all things either go right or don't happen at all. That's why banking systems are heavily dependent on transactions. The rest of us, however, can usually live without 'em. -- Hi Pete To emable logging use the --log option eg --log-update when stating the server... Simon ___ Pete Kuczynski Sr. Field Engineer DHL Airways Inc. Infrastructure Technology Services (773)-462-9758 24/7 Helpdesk 1-800-434-5767 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php Mike(mickalo)Blezien =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Thunder Rain Internet Publishing Providing Internet Solutions that work! http://www.thunder-rain.com Tel: 1(225)686-2002 =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
