MYSQL :: Using Transactions With InnoDB

InnoDB And Transactions
I have a problem with InnoDB and Transactions. I have a WebApplication running ColdFusion MX 6.1 and MySQL 4.0.17.
I have many changes to database and all changes which belongs together a put in a transaction, because when errors occours, mysql rolls back the changes made. This works great.
But now I have the following problem. When the user submits the form two times, both transaction are parallel. For example the first step is to select a number, and at the end of the transaction I insert a new line with "number+1". Normally it is not possible to get the same number in two lines. But when the user submits the form twice quickly and the first transaction is not ready yet, both transactions put a line with the same number. How can I make Mysql to wait with the second transaction on the same table until the first transaction is ready? I tried with "SELECT FOR UPDATE" and with the transaction-parameter "serializable", but both don`t work

Using Transactions
The sample code (in php) I've seen looks like this...

mysql_query("BEGIN");
mysql_query("INSERT ... ");

if($error)
mysql_query("ROLLBACK");
else
mysql_query("COMMIT");

Using Transactions
I am a mssql programmer now using mysql for a project. Is there a way in mysql to use transactions. meaning i would

begin transaction

run a few sql statemenets

end transaction

with a way to rollback all the statements if any fail. can this be done in mysql?

Transactions
this is apart of a php script

first i BEGIN the transaction

then i have a mysql_query()

then call a function which inside it has a mysql transaction (which is a seperate one)

then if that function i called returns true (meaning its transaction was successfull) i commit my original transaction

will this somehow casue conflicts as being i have a weird nested transaction type thing.

Transactions
I want to start using transactions in our mysql project but I was wondering how a following situation could be converted from MYISAM as an example (simplyfied):

$query1="insert into table1 ";
$query2="select ID from table1 order by ID DESC limit 1";//get last inserted ID
$query3="insert into table1 ";//use the retrieved ID to insert in table2

Since, as far as I know, using transactions, this last ID doesn't even exist until you COMMIT.

Transactions
I am developing a multi-user database and I have a few questions about this.
Will I need to use transactions or locking for single simple SQL statements?
Or is MySQL smart enough to know not to let two people edit the same record at the same time?

Transactions
if you call a BEGIN and then you process some data, am i right in saying that you must call a COMMIT to actually have any effect on the database?

How To Learn Transactions
i have a problem to study transaction log in mysql, please any bady have example aplication to help me ?

CURRENT_TIMESTAMP And Transactions
MySQL lets CURRENT_TIMESTAMP tick on inside transactions. I'm wondering why? doesn't this violate the atomicity of transactions?

Can I Do Transactions In MySQL?
I tried to change my sql queries for a program i originally wrote in SQL Server 2000 into MySQL, what irks me is that if i try to run two consecutive insert queries the first query is run but the second one gets ignored.WHY? The code reads thus:

<?php
$sql = "INSERT INTO smsusers (FNAME , LNAME , USERNAME , PASSWORD , TITLE , PHONENO) VALUES ('$thisFNAME' , '$thisLNAME' ,'$thisUSERMAIL' , password('$thisPASSWORD') , '$thisTITLE' , '$thisPHONENO')";

$result = MYSQL_QUERY($sql);
?>

<?php
$sql = "INSERT INTO profilecontents (CONTENTIDID , PROFILEIDNAME , CHECKER ) VALUES ('ACCTINFO' ,'$thisUSERMAIL')";

$result = MYSQL_QUERY($sql);
?>

Perhaps there is sumthin' wrong with ma code????

MySQL And Transactions
Does someone knows how transactions are made in MySQL on Berkeley DB
type tables?

I need to insert a new entry in a table but one of its field is an
order field so i use the MAX of current entries in the table.
To lock tables to be sure that 2 persons can't have the same MAX at
the same time, I use transactions.

Nevertheless, How to be sure to get a WRITE lock on this table if my
first statement is a select ?

I'm doing this :

BEGIN;
SELECT MAX(ESSAILOCK_ORDER) AS MAX FROM ESSAILOCK;
INSERT INTO ESSAILOCK (ESSAILOCK_TITRE;ESSAILOCK_ORDER) VALUES
('essai3',MAX);
COMMIT;

If the select is in first, am i just getting a reading lock, which
could allow someone else to insert a new entry with higher order
maybe.

Do I havbe to start with a fake update on the table to get a write
lock?

MySQL And Transactions
I need to insert a new entry in a table but one of its field is an
order field so i use the MAX of current entries in the table.
To lock tables to be sure that 2 persons can't have the same MAX at
the same time, I use transactions.

Nevertheless, How to be sure to get a WRITE lock on this table if my
first statement is a select ?

I'm doing this :

BEGIN;
SELECT MAX(ESSAILOCK_ORDER) AS MAX FROM ESSAILOCK;
INSERT INTO ESSAILOCK (ESSAILOCK_TITRE;ESSAILOCK_ORDER) VALUES
('essai3',MAX);
COMMIT;

If the select is in first, am i just getting a reading lock, which
could allow someone else to insert a new entry with higher order
maybe.

Do I havbe to start with a fake update on the table to get a write
lock ????

Set AutoIncrement And Transactions
in MySQL how is it possible to manage transactions and set manually auto_increment start value? With InnoDB Tables it's not possible!

Nested Transactions
If a transaction is started in a stored procedure 'A' that calls stored procedure 'B' which has its own start transaction... commit statements, can 'A' rollback and undo the changes made by 'B' if it decides not to commit any longer?

CURRENT_TIMESTAMP And Transactions
MySQL lets CURRENT_TIMESTAMP tick on inside transactions. I'm
wondering why? doesn't this violate the atomicity of transactions?

MySQL And Transactions
Does someone knows how transactions are made in MySQL on Berkeley DB
type tables

I need to insert a new entry in a table but one of its field is an
order field so i use the MAX of current entries in the table.
To lock tables to be sure that 2 persons can't have the same MAX at
the same time, I use transactions.
Nevertheless, How to be sure to get a WRITE lock on this table if my
first statement is a select ?
I'm doing this :

BEGIN;
SELECT MAX(ESSAILOCK_ORDER) AS MAX FROM ESSAILOCK;
INSERT INTO ESSAILOCK (ESSAILOCK_TITRE;ESSAILOCK_ORDER) VALUES
('essai3',MAX);
COMMIT;

If the select is in first, am i just getting a reading lock, which
could allow someone else to insert a new entry with higher order
maybe.
Do I havbe to start with a fake update on the table to get a write
lock?

Transactions Not Supported
Transactions not supported by database at /usr/lib/perl5/site_perl/5.8.5/i386-linux-thread-multi/DBI.pm line 672.

I was told that I need to recompile MySQL libmysqlclient as it doesn't look like it supports transactions. I tried to recompile with ./configure --without-server and replaced libmysqlclient.so.15.0.0 and libmysqlclient.a in the appropriate directories. I am still having the same problem.

Auditing Transactions
With regard to auditing transactions :

- Does MySQL have a table of users similar to Oracle's "ALL_USERS", allowing me to insert a user_id (rather than the full user_name string) into an audit column such as "last_updated_by" or "created_by" ?

- Is there a function similar to Oracle's SYSDATE ?

MySQL Transactions
I found a pretty good(and simplified) article on transactions for the new people to sql out there, however, you will have to have some prior knowledge of howto use commands, And why you would want to implement something of this nature. So, I guess, Intermediates in MySQL, go here.

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.

Transactions VS Fulltext
I've got a bit of a problems.
I'm using transactions to insert information into a couple of tables. Its an "all or nothing" procedure, and data integrety is important.
However, I've come across a stumbling block. The information which is being inserted needs to be fully searchable with relevance. Which means fulltext indexing. Which I can't use on InnoDB tables.

Transactions Supported
Looking at the docs, it says 3.23 supported transactions through using InnoDB tables. A person Im building a website for has already chosen a host that is using this version of MySQL. We may or may not need transactions, but Im just getting ready.

Here is an article talking about their use with MySQL, but it states the example requires ver 4
http://www.devarticles.com/c/a/MySQ...QL-4.0-and-PHP/

Do Triggers Support Transactions?
Do triggers in MySQL support transactions?

Also, are there any pros/cons of using triggers as opposed to stored procedures?

Transactions And Race Conditions
In an application I'm developing, I'm at a point where there needs to be tight integration of application-level logic sprinkled in between several consecutive SQL statements. This negates the option to use stored procedures since subsequent SQL statements depend on application logic that cannot be transferred into the database. I need the ability to rollback on errors (i.e. I need to use a transaction), but I also need to avoid race conditions.

Reading the MySQL documentation, START TRANSACTION will implicitly issue an UNLOCK TABLES statement; conversely, LOCK TABLES will implicitly COMMIT any active transaction. At this point it appears that I have to choose between transactions and locks, but I need the functionality of both.

Transactions With Java And MySQL
I'm trying to port the use of postgresql db to mysql. I'm using
Java and having the following problem - I can't seem to write more
than 1 query per execution. Easier to explain with an example that
worked under postgresql and does not using mysql:

aStatement.executeQuery("BEGIN; DELETE FROM table1; DELETE FROM
table1; DELETE FROM table3; COMMIT;");

If I separate this into 5 different queries it works fine... Any
ideas??

MyIsan Will Support Transactions?
I would like to know if mysql 5.0 myisan type will support transactions?

Group Transactions By Week
I am having problems finding how to group transactions by week.

Stored Procedures & Transactions
I wonder whether it is possible to use transactions like this:

CREATE PROCEDURE testproc

BEGIN
START TRANSACTION;
CALL another_proc();
COMMIT;

END;

Or is there anything I need to take care of?

The reason I am asking is, I have a project with lots of SPs and I need to add transactions now.

Pull The Last 10 Records From A Transactions From A Table
I'm trying to pull the last 10 records from a transactions from a table using this query:

SELECT * FROM transactions ORDER BY timestamp DESC LIMIT 10

But I want to display the rows in ascending order by timestamp. I can't get the subquery below to work and not sure why:

SELECT *
FROM (SELECT *
FROM transactions
ORDER BY timestamp DESC
LIMIT 10)
ORDER BY timestamp ASC;

Mixed Table Types In Transactions?
If I have a transaction that performs INSERTs or UPDATEs on a bunch of
tables, some InnoDB and some MyISAM, and after performing a bunch of
modifications on each I issue a ROLLBACK, does the fact that a MyISAM
table is involved in the process cause any problems with the ROLLBACK at
all?

Using MySQL With Hibernate, Transactions For Selects
I have a general question about transactions around reads. I'm using hibernate and mysql 5.0. What are people's thoughts on wrapping select statements with transactions? I don't believe this will add significant overhead, but is there a disadvantage to not using a txn in this case?

Sql Timing With Transactions - Delete Vs Select
I have a sql command I want to run and time but rollback the results afterwards.
I tried the code below but I don't get any timing output. If I do a select instead of delete I get output.

Any ideas what's going on?

start transaction;
DELETE FROM testtable WHERE col1 = 1;
rollback;
start transaction;
DELETE FROM testtable WHERE col1 = 2;
DELETE FROM testtable WHERE col1 = 3;
rollback;

I don't get any output from the delete but here's the select output:
count(*)
0
count(*)
0
count(*)
19841

Tracking MySQL Table Transactions
I am trying to understand how I could manage MySQL transactions. I have this database that contains a very important table. Its data changes a lot and contains student grades.
I could have another table where I would keep track of the original table's transactions. I make a backup of this table on a weekly basis and I'm thinking that if I keep track of the sql queries I could recreate the data at any point. So, if a user tels me that he/she overwrote the data, I would restore the portion of the table from the weekly backup, and then go to the transaction table and issue the queries up the point where the data is valid. Is this a good way of restoring data? Is there a better way? Does MySQL have a buil-in system of restoing data?

Does MYSQL Support Transactions Like Rollback And Commit Now?
Does MYSQL support transactions like rollback and commit now? How about
stored procedures and subqueries?

MySQL Consuming Lots Of Memory During Transactions W/ Many Statements
I am having a problem with MySQL consuming a lot of memory and
eventually throwing an Out of Memory error and restarting itself. The
symptoms are that swap usage continues to rise until some breaking
point. The application is a typical web application w/ 2 web servers
running Apache/Tomcat connecting to a dedicated DB server running only
MySQL.

This seems to occur as a result of running many statements in a single
transaction, both against InnoDB tables and MyISAM tables. In one
case, I'm writing all user actions to an audit table (MyISAM), all from
a single application thread doing approximately 5K inserts (as single
INSERTs on a single connection) every few seconds. In the other, I'm
doing a variety of select/update/insert/delete statements against
InnoDB tables, but again totaling several thousand in one transaction.
Both of these problems occur even when there is relatively low activity
elsewhere on the system. Code:

Estimate The "unreplicated" Transactions
I am trying to estimate how many transactions the slave needs to catch up to the master. I have read the note about estimating how many seconds since the last event, but this does not assist me in my quest. Is there a relation between the position in the relay log and transactions replicated?

I have put in a script to monitor that the slave 'Read_Master_Log_Pos' position does not fall too far behind the Master position. I am concerned that if I lose my Master server, how do I know how many unreplicated transactions have been lost?

Help On Innodb
I have read few articles on innodb and quite confuse about those stuff. So said that you need to do some start up setting on innod buffer pool and etc. Where can I like get this list of things which need to be done on innodb. I need some optimization tips for innodb.

Innodb
I've always managed dependencies accross tables with a 'link' table:

Eg:

Users belong to Groups so I'd have a User2Groups linking table to manage the connection.

This works OK, but I have to manage the data integerity myself (if I delete a Group, I must also delete the record in the user2Groups table).

I've recently heard of innodb and the CASCADE features - allowing the db to reatain referential integrity.

Can someone please summarize the pros and cons of this - perhaps give reasons when to use and when not to use, and point me in the right direction for using innodb -

BDB Vs INNODB
Are there any big differences between using BDB and INNODB table types? I know they both support transactions and BDB is generally slower than INNODB. I'm talking more about the implementation rather than performance.

For instance, I developed this application which at the time I was writing it was for INNODB tables. Should the same app work fine using the BDB table type.

3.23.x To 4.0.x INNODB
Can I take multiple 2GB files in /var/lib/innodb and the corresponding
files in /var/lib/iblogs created with 3.32.55 (INNODB) and upgrade to
4.0.13 and let it use those existing files without a problem? Without
dumping and re-importing? I'd only need to additionally run the
mysql_privs_fix(?) script as part of a normal 3.23->4.0 upgrade?

I did this as a test and it appeared to work but only on a test
machine.. Want to make 100% certain before trying it on a production
box.

64-Bit And INNODB
With an Opteron (or Itanium for that matter) using let's say the RedHat 9 or
AS 3 betas available for those CPUs, how will MySQL/INNODB function?
Specifically does the ability to allocate a bigger chunk of memory than 2GB
just work "out of the box"? Can I set innodb_buffer_pool_size to 12GB for
instance if I have 16GB in a box?

Now then, along the same lines and not really MySQL oriented but someone
here will likely know. I see lots of Opteron boards with 2 CPUs being
advertised that have 4 DIMM slots "per CPU". Is it one large flat memory
space or do they do SMP in some strange fashion where each CPU has access to
it's own memory? Therefore not giving a flat 16GB memory space if 8 slots
were populated with 2GB DIMMs? I hope not but the way they're being
described it is somewhat confusing.

Even more off-topic, anyone know of a good 1U or 2U Opteron or Itanium
system with >8 DIMM slots? 64-bit is great but unless you can stuff a _lot_
of memory in it, a lot of the advantage is lost. I wish someone like
SuperMicro would offer a high end Opteron SuperServer. I love their dual
Xeon systems..

How To Set Up INNODB
I want MySQL to use INNODB tables.I created the tables using innodb as stated on mysql website but not having any luck. I am interested in maintaining referential integrity between primary and foreign keys..meaning..

"if i update the value in one table the other tables gets updated automatically too"..and if not wrong Mysql doesnt support this feature but innodb does.

so how i can basically setup MySQL to use INNODB on linux.

InnoDB
I am struggling a bit to get going with mysql and my tutorial works through InnoDB tables without a clear explanation of the setup.
To date I have modified the /etc/my.cnf file. mysqld starts without complaining. I have modified my.cnf to include:

innodb_data_file_path = /data/mysqldata/innodb_data/innodata:10M:autoextend
innodb_data_home_dir=

It turns out that logged in as a mysql user if I "use innodta' it mounts as a the database. So am I correct in assuming that identifying the tablespace name in innodb_data_file_path is the equivalent of 'create database <databaseName> with a MyISAM table?

Also once having 'use innodata' I can issue a create tables command and then see <tablename>.frm .MYD, and MYI listed in the innodb_data directory. However when <tblename> was created I could not use the 'engine = innodb; ' as the last line of the create table sql statement without getting an error. This suggests that the table that I made without 'engine=innodb' is just the usual MyISAM based table. Right? Wrong? Huh?
I think the documentation is a little lacking in this area as are two other MySQL books that I purchased

Specifying INNODB
Is it a bug or a feature that with the conditions

1. INNODB is not configured in my.cnf
2. Table type is specified as INNODB

that when you create the table there is no warning,
and no syntax error, and the table type silently
and Microsoft-like, defaults to MyISAM?

Now, if you specify table type as TYPE=bullshit,
you get a nice (and expected) syntax error.

Innodb
I have a table with approx. 150,000,000 ++ rows. It should be approx
80byte for each row. Now I'm planning to use it with mysql/innodb.
Can you guys suggest anything about this.
I have many questions... Can Innodb work well with such tables?
Will queries (select/update) be slow?
What hardware do you suggest, cpu/ram?

Innodb
I'm using mysql 5.0.22 as packaged (and updated) with Ubuntu Dapper.
I would like to use Innodb, but "show innodb status" says "ERROR 1235
(42000): Cannot call SHOW INNODB STATUS because skip-innodb is
defined". However, there is no skip-innodb in anywhere in
/etc/mysql/my.cnf. I found a file called /etc/mysql/debian.cnf which
appears to be used as a defaults file for behind-the-scenese stuff. It
doesn't mention skip-innodb. I've checked /etc/init.d/mysql and it has
no reference to skip-innodb.
I've googled the issue, and can't find anything. Am I missing
something?

No InnoDB
OS Fedora Core 3
I have been trying to set InnoDB engine on mysql 4.0 and 5.0 as default with no joy so far.
I have edited my.cnf file and put it in the datadir /usr/lib/.
Everytime I create a table the engine is MyIsam.