Re: transaction problem

[Baron Schwartz]

Hi,

[EMAIL PROTECTED] wrote:

Hi list!
I've a problem with transaction
I initiate a transaction with "start transaction". Soon i execute 
following query:
* update t1 set t=12; and by I complete I execute rollback but when I do 
it I obtain the following thing:
"non-transactional Some changed tables couldn't be rolled back" and 
update I execute myself updating the data

Why do it?
I'm use MySQL 4.1.20 and MyISAM tables


MyISAM doesn't support transactions.  You probably need to use InnoDB. 
(There are some other storage engines with transaction support, but they 
are not usually distributed with most MySQL servers).


There is more information on this in the MySQL manual.

Baron

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: transaction problem

[Martijn Tonies]

Hi,

> I've a problem with transaction
> I initiate a transaction with "start transaction". Soon i execute 
> following query:
> * update t1 set t=12; and by I complete I execute rollback but when I do 
> it I obtain the following thing:
> "non-transactional Some changed tables couldn't be rolled back" and 
> update I execute myself updating the data
> Why do it?
> I'm use MySQL 4.1.20 and MyISAM tables

Have you been reading any documentation on Transactions in MySQL?

MyISAM tables don't support transactions!

For each table, you can assign a different "storage engine", which
is the implementation that physically stores your data. Depending
on the storage engine you're using for your table, MySQL supports
different features, like transactions.

Read the docs on the different engines.

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: Transaction/locking confusion

[Michael Dykman]

The difference here, is that any row locked via the SELECT .. FOR
UPDATE will not even allows readers access to that record until, so
you will not have processes concurrently trrying to process.  This
means that, for each row, any processes acting on it are force to be
seqential each each subsequent phase needs to wait for the previous
one to complete.

- michael

On 3/14/07, Marcus Bointon <[EMAIL PROTECTED]> wrote:

On 14 Mar 2007, at 18:02, Michael Dykman wrote:

> SELECT * from process WHERE WHERE id = 123 AND status =  'init' FOR
> UPDATE;
>
> -- do a bunch of other stuff ...
>
> UPDATE process SET status = 'ready' WHERE id = 123 AND status =
> 'init';

I get what you're doing here, but I don't see why it's necessary -
the update will automatically acquire locks on matched rows while
it's doing the update - at least that's the impression I've had from
the docs:

"A SELECT ... FOR UPDATE reads the latest available data, setting
exclusive locks on each row it reads. Thus, it sets the same locks a
searched SQL UPDATE would set on the rows."

"UPDATE ... WHERE ... sets an exclusive next-key lock on every record
the search encounters."

So it sounds like the select for update will effectively be the same
as what I'm already doing, and thus suffer the same problem.

Is it just that locks don't apply outside the transaction? If
transactions can't solve synchronisation problems between processes,
what are they for??!

Marcus
--
Marcus Bointon
Synchromedia Limited: Creators of http://www.smartmessages.net/
UK resellers of [EMAIL PROTECTED] CRM solutions
[EMAIL PROTECTED] | http://www.synchromedia.co.uk/






--
- michael dykman
- [EMAIL PROTECTED]

- All models are wrong.  Some models are useful.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Transaction/locking confusion

[Marcus Bointon]

On 14 Mar 2007, at 18:02, Michael Dykman wrote:

SELECT * from process WHERE WHERE id = 123 AND status =  'init' FOR  
UPDATE;


-- do a bunch of other stuff ...

UPDATE process SET status = 'ready' WHERE id = 123 AND status =  
'init';


I get what you're doing here, but I don't see why it's necessary -  
the update will automatically acquire locks on matched rows while  
it's doing the update - at least that's the impression I've had from  
the docs:


"A SELECT ... FOR UPDATE reads the latest available data, setting  
exclusive locks on each row it reads. Thus, it sets the same locks a  
searched SQL UPDATE would set on the rows."


"UPDATE ... WHERE ... sets an exclusive next-key lock on every record  
the search encounters."


So it sounds like the select for update will effectively be the same  
as what I'm already doing, and thus suffer the same problem.


Is it just that locks don't apply outside the transaction? If  
transactions can't solve synchronisation problems between processes,  
what are they for??!


Marcus
--
Marcus Bointon
Synchromedia Limited: Creators of http://www.smartmessages.net/
UK resellers of [EMAIL PROTECTED] CRM solutions
[EMAIL PROTECTED] | http://www.synchromedia.co.uk/



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Transaction/locking confusion

[Marcus Bointon]

On 13 Mar 2007, at 13:44, JamesDR wrote:

With what you've provided us: You can not prevent this. You are  
running

in a transaction which is isolated from any others.


But doesn't that isolation provide atomicity, i.e. the first  
transaction to commit will act as if all of its component queries  
occurred before the first query of the second one, even if they are  
actually issued in an overlapping order? Consequently, the first  
query of the second transaction in my example should not succeed in  
finding anything. Or are transactions not atomic this way??


Marcus
--
Marcus Bointon
Synchromedia Limited: Creators of http://www.smartmessages.net/
UK resellers of [EMAIL PROTECTED] CRM solutions
[EMAIL PROTECTED] | http://www.synchromedia.co.uk/



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Transaction/locking confusion

[JamesDR]

Marcus Bointon wrote:
> Hi,
> 
> (repeat posting)
> 
> I have a simple PHP function that initialises a process definition. To
> prevent it happening more than once, I'm wrapping it in a transaction,
> however, it doesn't seem to be working and I get multiple
> initialisations. In pseudocode:
> 
> BEGIN;
> UPDATE process SET status = 'ready' WHERE id = 123 AND status = 'init'
> LIMIT 1;
> ...do other stuff including some INSERTs
> if other stuff is OK:
> COMMIT;
> else
> ROLLBACK;
> 
> If I have two simultaneous processes running this script, somehow they
> are both able to initialise. I guess that if the overlapping
> transactions are isolated, then both see the process in the 'init'
> status and that the 'other stuff' part takes long enough that it's
> reasonably likely to happen. I was under the impression that the UPDATE
> inside the transaction would lock the row and prevent the later query
> from succeeding, but it seems that's not how it works.
> 
> How can I prevent this situation? Do I need to lock the row explicitly?
> Why doesn't the transaction provide sufficient isolation?
> 
> Marcus
> --Marcus Bointon
> Synchromedia Limited: Creators of http://www.smartmessages.net/
> [EMAIL PROTECTED] | http://www.synchromedia.co.uk/

With what you've provided us: You can not prevent this. You are running
in a transaction which is isolated from any others. One way to prevent
this may be to write a pid file in your script, then check for its
existence. If it does exist then just die out (script was already running.)

I have scripts that perform tasks on regular intervals. Some times the
script runs long and may overlap into the next run time. I check for the
file's existence, if it does exist just print a message and exit. If it
does not exist, touch the file to create it and move on... deleting it
later of course.


-- 
Thanks,
James

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: transaction in mysql 5

[George-Cristian Bîrzan]

On Tue, Oct 31, 2006 at 08:58:04PM +0300, Ahmad Al-Twaijiry wrote:
> is it possible that one of my script process will select the same data
> that the other process is selecting or updating ? or if I run a second
> process of my script it will not be able to read/write anything until
> the first script commit to the database or rollback ?

http://dev.mysql.com/doc/refman/5.0/en/innodb-transaction-isolation.html

-- 
George-Cristian Bîrzan

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: transaction

[Pooly]

Hi,

2006/7/22, João Cândido de Souza Neto <[EMAIL PROTECTED]>:

Ok Dan.

Thanks a lot for your answer.

An other doubt about transaction is in the foreign key case. If in a
transaction i insert a certain register and get the last insert id and after
i try to insert another register using this id because this table has a
foreign key, it gives me a contstraint fail for the foreign key, that is, in
a transaction, the inserts do not get inserted until commit and i can't
insert registers in tables that has foreign key in the previous table.


You should test it :


mysql> CREATE TABLE t1(id integer auto_increment, field text,primary key(id));

mysql> CREATE TABLE t2(id integer auto_increment, next_id integer,
field text,primary key(id), foreign key (next_id) references t1(id))
ENGINE=InnoDB;


mysql> insert into t2 values(1,1,'nope');
ERROR 1452 (23000): Cannot add or update a child row: a foreign key
constraint fails (`test/t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY
(`next_id`) REFERENCES `t1` (`id`))
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t1(field) values('my data');
Query OK, 1 row affected (0.00 sec)

mysql> insert into t2(next_id,field) values(last_insert_id(),'my data');
Query OK, 1 row affected (0.07 sec)

mysql> select * from t2;
++-+-+
| id | next_id | field   |
++-+-+
|  1 |   1 | my data |
++-+-+
1 row in set (0.05 sec)

mysql> rollback;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t2;
Empty set (0.00 sec)


In the table T2 which depends on the table T1, you are able to insert
rows even if you didn't commit your inserts (hopefully :-).
HIMH

--
http://www.w-fenec.org/

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: transaction

[Jo�o C�ndido de Souza Neto]

Ok Dan.

Thanks a lot for your answer.

An other doubt about transaction is in the foreign key case. If in a 
transaction i insert a certain register and get the last insert id and after 
i try to insert another register using this id because this table has a 
foreign key, it gives me a contstraint fail for the foreign key, that is, in 
a transaction, the inserts do not get inserted until commit and i can't 
insert registers in tables that has foreign key in the previous table.

Do you know how can i do this?

Thanks.


"Dan Nelson" <[EMAIL PROTECTED]> escreveu na mensagem 
news:[EMAIL PROTECTED]
> In the last episode (Jul 22), Jo?o C?ndido de Souza Neto said:
>> I've getting something wrong in transaction in Mysql 5.0.22 on
>> windows that i don't know way it's happen.
>>
>> I've got a script in php which starts a transaction and when if fails
>> and rollbacks, the autoincrement do not back.
>>
>> e.g.
>>
>> If in transaction some field auto increment gets the value 4 and this
>> transaction fails, in the next transaction it gets the value 5.
>
> The autoincrement value is metadata outside of any table values and is
> not subject to transaction rollback.  Consider the case where you
> insert one row in each of two separate connections, getting, say,
> values 4 and 5. Then you rollback the first one.  The autoincrement
> counter is now at 6 and you're never going to get another 4.  I guess
> mysql could decrement the autoincrement counter if only one new row has
> been reserved but not committed, but that's a lot of work for little
> gain.
>
> -- 
> 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: transaction

[Dan Nelson]

In the last episode (Jul 22), Joπo CΓndido de Souza Neto said:
> I've getting something wrong in transaction in Mysql 5.0.22 on
> windows that i don't know way it's happen.
> 
> I've got a script in php which starts a transaction and when if fails
> and rollbacks, the autoincrement do not back.
> 
> e.g.
> 
> If in transaction some field auto increment gets the value 4 and this
> transaction fails, in the next transaction it gets the value 5.

The autoincrement value is metadata outside of any table values and is
not subject to transaction rollback.  Consider the case where you
insert one row in each of two separate connections, getting, say,
values 4 and 5. Then you rollback the first one.  The autoincrement
counter is now at 6 and you're never going to get another 4.  I guess
mysql could decrement the autoincrement counter if only one new row has
been reserved but not committed, but that's a lot of work for little
gain.

-- 
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: transaction isolation level

[sheeri kritzer]

Apologies for a late reply.

1) The query that tries to insert the invalid entry into Table2 fails.
Therefore, if you have 3 separate queries as in the first case, the
last one fails, but the first 2 are successful.  In the second case,
they're all in one query, and if one fails, they all fail.

For optimization purposes, MySQL doesn't turn a query in extended
insert syntax into multiple queries.  The whole point of extended
insert is that it batch processes inserts faster than individual
inserst.

2) Sure, there are plenty of ways. Look up IF(), user variables, and
most importanly, ROLLBACK.  Basically, you want to find out if the
inserts were successful, and if any one insert wasn't successful, you
rollback your transaction.  Your example never actually has a decision
point where you decide whether or not to commit or rollback.

3) There is no way to figure out which value to be inserted made an error.

On 6/14/06, Konrad Baginski <[EMAIL PROTECTED]> wrote:

Hi.

I have a few questions regarding the transaction levels in mysql 5.0.20
using InnoDB tables.
we are trying to populate two tables in the two following ways, we thought
that they would  be equivalent, apparently they are not.
have a look at the following (questions last).



FIRST METHOD:


create database test10;
use test10;


DROP TABLE IF EXISTS Table2;
DROP TABLE IF EXISTS Table1;
CREATE TABLE Table1 (
  id BIGINT NOT NULL AUTO_INCREMENT,
  logid VARCHAR(32) NULL,
  PRIMARY KEY(id),
  UNIQUE KEY log_id_key(logid)
)ENGINE=InnoDB;


DROP TABLE IF EXISTS Table2;
CREATE TABLE Table2 (
  id BIGINT NOT NULL AUTO_INCREMENT,
  table1id BIGINT,
  PRIMARY KEY(id),
  FOREIGN KEY (table1id) REFERENCES Table1(id) ON DELETE CASCADE
)ENGINE=InnoDB;


START TRANSACTION;

INSERT INTO Table1(logid) VALUES('1');
INSERT INTO Table1(logid) VALUES('2');

COMMIT;

START TRANSACTION;

INSERT INTO Table2(table1id) VALUES('1');
INSERT INTO Table2(table1id) VALUES('2');
INSERT INTO Table2(table1id) VALUES('3');

COMMIT;


select * from Table1; select * from Table2;

++---+
| id | logid |
++---+
| 1  | 1 |
| 2  | 2 |
++---+
2 rows in set (0.00 sec)

++--+
| id | table1id |
++--+
| 1  | 1|
| 2  | 2|
++--+
2 rows in set (0.00 sec)


### END FIRST METHOD ###





SECOND METHOD:


create database test10;
use test10;

DROP TABLE IF EXISTS Table2;
DROP TABLE IF EXISTS Table1;
CREATE TABLE Table1 (
  id BIGINT NOT NULL AUTO_INCREMENT,
  logid VARCHAR(32) NULL,
  PRIMARY KEY(id),
  UNIQUE KEY log_id_key(logid)
)ENGINE=InnoDB;


DROP TABLE IF EXISTS Table2;
CREATE TABLE Table2 (
  id BIGINT NOT NULL AUTO_INCREMENT,
  table1id BIGINT,
  PRIMARY KEY(id),
  FOREIGN KEY (table1id) REFERENCES Table1(id) ON DELETE CASCADE
)ENGINE=InnoDB;



START TRANSACTION;
INSERT INTO Table1(logid) VALUES('1'), ('2');
COMMIT;

START TRANSACTION;
INSERT INTO Table2(table1id) VALUES('1'), ('2'), ('3');
COMMIT;



select * from Table1; select * from Table2;
++---+
| id | logid |
++---+
| 1  | 1 |
| 2  | 2 |
++---+
2 rows in set (0.00 sec)

Empty set (0.00 sec)


### END SECOND METHOD ###




Questions
1. Why are the two ways of adding rows not equivalent, after all, they both
happen in a trancation?
2. Is there some way to make both of them either add the two first rows to
Table2 or not to add any row?

3. If we look at the second method to insert values, how can i find out
exacly which of the values made an error?
   (in this case, the third value has no matching row in Table1).




/konrad baginski



--
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: transaction with bdb table gives error "Lock table is out of available locks"

[Marco Baroetto]

Maybe i got it! For the chronicle:

I resolved the problem decreasing the max_bdb_lock to the default value
and creating an index on the column used in the where clause of my
query. Probably mysql tried to lock the whole table because the primary
key wasn't in the where clause.

Cheers,
Marco

Marco Baroetto wrote:

>Hi, here's my create table:
>
>mysql> show create table parametrivaloriplc;
>++-+
>| Table  | Create Table|
>++-+
>| parametrivaloriplc | CREATE TABLE `parametrivaloriplc` (
>  `idParametroPLC` int(11) NOT NULL auto_increment,
>  `name` varchar(100) NOT NULL default '',
>  `idDataType` int(11) NOT NULL default '0',
>  `unit` varchar(20) default NULL,
>  `molt` int(11) default NULL,
>  `div` int(11) default NULL,
>  `accesso` char(3) NOT NULL default '',
>  `idCategoriaParametriPLC` int(11) NOT NULL default '0',
>  `max` int(11) default NULL,
>  `min` int(11) default NULL,
>  `idParametroPadre` int(11) default NULL,
>  `idParametroMax` int(11) default NULL,
>  `idParametroMin` int(11) default NULL,
>  `invalidable` tinyint(1) default NULL,
>  `id_centralina` int(10) unsigned NOT NULL default '0',
>  `valore` varchar(50) NOT NULL default '',
>  PRIMARY KEY  (`idParametroPLC`)
>) ENGINE=BerkeleyDB DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC |
>++-+
>1 row in set (0.00 sec)
>
>Tonight i had the following unexpected restart, may it be caused by the
>high value of bdb_max_lock?
>
>---
>mysqld got signal 11;
>This could be because you hit a bug. It is also possible that this binary
>or one of the libraries it was linked against is corrupt, improperly built,
>or misconfigured. This error can also be caused by malfunctioning hardware.
>We will try our best to scrape up some info that will hopefully help
>diagnose
>the problem, but since we have already crashed, something is definitely
>wrong
>and this may fail.
>
>key_buffer_size=8388600
>read_buffer_size=131072
>max_used_connections=12
>max_connections=100
>threads_connected=2
>It is possible that mysqld could use up to
>key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections
>= 225791 K
>bytes of memory
>Hope that's ok; if not, decrease some variables in the equation.
>
>thd=0x9964008
>Attempting backtrace. You can use the following information to find out
>where mysqld died. If you see no messages after this, something went
>terribly wrong...
>Cannot determine thread, fp=0xae3fa304, backtrace may not be correct.
>Stack range sanity check OK, backtrace follows:
>0x813198c
>0x8a07c8
>0x82770aa
>0x82770aa
>0x827651c
>0x8276adb
>0x824ee13
>0x824f6d2
>0x824fb82
>0x81b6566
>0x81a8bac
>0x81855fa
>0x814514a
>0x8148dfa
>0x8149638
>0x814a263
>0x814ab5a
>0x89a341
>0x71a6fe
>New value of fp=(nil) failed sanity check, terminating stack trace!
>Please read http://dev.mysql.com/doc/mysql/en/Using_stack_trace.html and
>follow instructions on how to resolve the stack trace. Resolved
>stack trace is much more helpful in diagnosing the problem, so please do
>resolve it
>Trying to get some variables.
>Some pointers may be invalid and cause the dump to abort...
>thd->query at 0x996d548 = delete from parametrivaloriplc where
>id_centralina=343
>thd->thread_id=1
>The manual page at http://www.mysql.com/doc/en/Crashing.html contains
>information that should help you find out what is causing the crash.
>
>Memory status:
>Non-mmapped space allocated from system: 6262784
>Number of free chunks:   18
>Number of fastbin blocks:0
>Number of mmapped regions:   11
>Space in mmapped regions:86974464
>Maximum total allocated space:   0
>Space available in freed fastbin blocks: 0
>Total allocated space:   5625120
>Total free space:637664
>Top-most, releasable space:  16
>Estimated memory (with thread stack):93433856
>
>Number of processes running now: 0
>051209 01:00:31  mysqld restarted
>051209  1:00:32  InnoDB: Database was not shut down normally!
>InnoDB: Starting crash recovery.
>InnoDB: Reading tablespace information from the .ibd files...
>InnoDB: Restoring possible half-written data pages from the doublewrite
>InnoDB: buffer...
>051209  1:00:33  InnoDB: Starting log scan based on checkpoint at
>InnoDB: log sequence number 0 52400171.
>InnoDB: Doing recovery: scanned up to log sequence number 0 52400171
>051209  1:00:33  InnoDB: Flushing modified pages from the buffer pool...
>051209  1:00:33  InnoDB: Started; log sequence number 0 52400171
>051209  1:00:33 [Warning] Can't open and lock time zone table: Table
>'mysql.time_zone_leap_second' doesn't exist trying to live without them
>/usr/libexec/mysqld: ready for connections.
>Version: '4.1.12'  socket: '/var/lib/mysql/mysql.sock'  port: 3306 
>Source distributi

Re: transaction with bdb table gives error "Lock table is out of available locks"

[Marco Baroetto]

Hi, here's my create table:

mysql> show create table parametrivaloriplc;
++-+
| Table  | Create Table|
++-+
| parametrivaloriplc | CREATE TABLE `parametrivaloriplc` (
  `idParametroPLC` int(11) NOT NULL auto_increment,
  `name` varchar(100) NOT NULL default '',
  `idDataType` int(11) NOT NULL default '0',
  `unit` varchar(20) default NULL,
  `molt` int(11) default NULL,
  `div` int(11) default NULL,
  `accesso` char(3) NOT NULL default '',
  `idCategoriaParametriPLC` int(11) NOT NULL default '0',
  `max` int(11) default NULL,
  `min` int(11) default NULL,
  `idParametroPadre` int(11) default NULL,
  `idParametroMax` int(11) default NULL,
  `idParametroMin` int(11) default NULL,
  `invalidable` tinyint(1) default NULL,
  `id_centralina` int(10) unsigned NOT NULL default '0',
  `valore` varchar(50) NOT NULL default '',
  PRIMARY KEY  (`idParametroPLC`)
) ENGINE=BerkeleyDB DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC |
++-+
1 row in set (0.00 sec)

Tonight i had the following unexpected restart, may it be caused by the
high value of bdb_max_lock?

---
mysqld got signal 11;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
We will try our best to scrape up some info that will hopefully help
diagnose
the problem, but since we have already crashed, something is definitely
wrong
and this may fail.

key_buffer_size=8388600
read_buffer_size=131072
max_used_connections=12
max_connections=100
threads_connected=2
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections
= 225791 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

thd=0x9964008
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
Cannot determine thread, fp=0xae3fa304, backtrace may not be correct.
Stack range sanity check OK, backtrace follows:
0x813198c
0x8a07c8
0x82770aa
0x82770aa
0x827651c
0x8276adb
0x824ee13
0x824f6d2
0x824fb82
0x81b6566
0x81a8bac
0x81855fa
0x814514a
0x8148dfa
0x8149638
0x814a263
0x814ab5a
0x89a341
0x71a6fe
New value of fp=(nil) failed sanity check, terminating stack trace!
Please read http://dev.mysql.com/doc/mysql/en/Using_stack_trace.html and
follow instructions on how to resolve the stack trace. Resolved
stack trace is much more helpful in diagnosing the problem, so please do
resolve it
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort...
thd->query at 0x996d548 = delete from parametrivaloriplc where
id_centralina=343
thd->thread_id=1
The manual page at http://www.mysql.com/doc/en/Crashing.html contains
information that should help you find out what is causing the crash.

Memory status:
Non-mmapped space allocated from system: 6262784
Number of free chunks:   18
Number of fastbin blocks:0
Number of mmapped regions:   11
Space in mmapped regions:86974464
Maximum total allocated space:   0
Space available in freed fastbin blocks: 0
Total allocated space:   5625120
Total free space:637664
Top-most, releasable space:  16
Estimated memory (with thread stack):93433856

Number of processes running now: 0
051209 01:00:31  mysqld restarted
051209  1:00:32  InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
051209  1:00:33  InnoDB: Starting log scan based on checkpoint at
InnoDB: log sequence number 0 52400171.
InnoDB: Doing recovery: scanned up to log sequence number 0 52400171
051209  1:00:33  InnoDB: Flushing modified pages from the buffer pool...
051209  1:00:33  InnoDB: Started; log sequence number 0 52400171
051209  1:00:33 [Warning] Can't open and lock time zone table: Table
'mysql.time_zone_leap_second' doesn't exist trying to live without them
/usr/libexec/mysqld: ready for connections.
Version: '4.1.12'  socket: '/var/lib/mysql/mysql.sock'  port: 3306 
Source distribution
--


More details about my environment follows:

# cat /proc/version
Linux version 2.6.9-22.0.1.ELsmp ([EMAIL PROTECTED]) (gcc version
3.4.4 20050721 (Red Hat 3.4.4-2)) #1 SMP Thu Oct 27 13:14:25 CDT 2005

# mysql --version
mysql  Ver 14.7 Distrib 4.1.12, for redhat-linux-gnu (i686) using
readline 4.3

# cat /etc/init.d/mysqld
 /usr/bin/mysqld_safe  --defaults-file=/etc/my.cnf
--pid-file="$mypidfile" -O b

Re: transaction with bdb table gives error "Lock table is out of available locks"

[Marco Baroetto]

Bdb tables locks are at page level, how can I determine the number of
pages of my table?

mysql> show variables like '%binlog_cache_size%';
+---++
| Variable_name | Value  |
+---++
| binlog_cache_size | 32768  |
| max_binlog_cache_size | 4294967295 |
+---++

How can I estimate the binlog chache size I need? The manual is very
vague about this issue..

Thanx,
Marco

sheeri kritzer wrote:

>Hi Marco,
>
>My hint is to read the BDB section of the manual:
>
>http://dev.mysql.com/doc/refman/5.0/en/bdb-start.html
>
>specifically the part that says:
>
>" With the bdb_max_lock variable, you can specify the maximum number
>of locks that can be active on a BDB table. The default is 10,000. You
>should increase this if errors such as the following occur when you
>perform long transactions or when mysqld has to examine many rows to
>execute a query:
>
>bdb: Lock table is out of available locks
>Got error 12 from ...
>
>You may also want to change the binlog_cache_size and
>max_binlog_cache_size variables if you are using large
>multiple-statement transactions. See Section 5.11.3, "The Binary Log".
>"
>
>What is your binlog_cache_size and max_binlog_cache_size?
>
>-Sheeri
>
>On 12/7/05, Marco Baroetto <[EMAIL PROTECTED]> wrote:
>  
>
>>Hi,
>>I have a berkeley db table containing about 5 rows where I do this
>>transaction (pseudocode follows):
>>
>>begin work
>>delete from mytable where myfield='boo' /*delete about 100 rows*/
>>for (i=0; i<=100; i++){
>>insert into mytable values(...);
>>}
>>commit
>>
>>During the insert command i get the following error:
>>"Lock table is out of available locks"
>>
>>I tried to resolve the problem starting mysqld with -O
>>bdb_max_lock=6  and later with -O bdb_max_lock=12 but i still
>>receive the same error.
>>
>>dbd related variables follows:
>>
>>mysql> show variables like "%bdb%";
>>+-++
>>| Variable_name   |
>>Value  |
>>+-++
>>| bdb_cache_size  |
>>8388600|
>>| bdb_home|
>>/var/lib/mysql/|
>>| bdb_log_buffer_size |
>>32768  |
>>| bdb_logdir
>>||
>>| bdb_max_lock|
>>12 |
>>| bdb_shared_data |
>>OFF|
>>| bdb_tmpdir  |
>>/tmp/  |
>>| have_bdb|
>>YES|
>>| version_bdb | Sleepycat Software: Berkeley DB 4.1.24: (May 13,
>>2005) |
>>+-++
>>9 rows in set (0.00 sec)
>>
>>Any hint is welcome..
>>
>>Thanx in advance for the help,
>>Marco
>>
>>--
>>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: transaction with bdb table gives error "Lock table is out of available locks"

[Jim Winstead]

On 12/7/05, Marco Baroetto <[EMAIL PROTECTED]> wrote:
> Hi,
> I have a berkeley db table containing about 5 rows where I do this
> transaction (pseudocode follows):
>
> begin work
> delete from mytable where myfield='boo' /*delete about 100 rows*/
> for (i=0; i<=100; i++){
> insert into mytable values(...);
> }
> commit
>
> During the insert command i get the following error:
> "Lock table is out of available locks"
>
> I tried to resolve the problem starting mysqld with -O
> bdb_max_lock=6  and later with -O bdb_max_lock=12 but i still
> receive the same error.

Hi Marco.

What does 'SHOW CREATE TABLE' look like for your table?

You may want to file a bug about this and attach your data and the
actual queries being run. I was not able to come up with a test case to
reproduce this problem, but you haven't provided much information to go
on.

Jim Winstead
MySQL Inc.

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: transaction with bdb table gives error "Lock table is out of available locks"

[sheeri kritzer]

Hi Marco,

My hint is to read the BDB section of the manual:

http://dev.mysql.com/doc/refman/5.0/en/bdb-start.html

specifically the part that says:

" With the bdb_max_lock variable, you can specify the maximum number
of locks that can be active on a BDB table. The default is 10,000. You
should increase this if errors such as the following occur when you
perform long transactions or when mysqld has to examine many rows to
execute a query:

bdb: Lock table is out of available locks
Got error 12 from ...

You may also want to change the binlog_cache_size and
max_binlog_cache_size variables if you are using large
multiple-statement transactions. See Section 5.11.3, "The Binary Log".
"

What is your binlog_cache_size and max_binlog_cache_size?

-Sheeri

On 12/7/05, Marco Baroetto <[EMAIL PROTECTED]> wrote:
> Hi,
> I have a berkeley db table containing about 5 rows where I do this
> transaction (pseudocode follows):
>
> begin work
> delete from mytable where myfield='boo' /*delete about 100 rows*/
> for (i=0; i<=100; i++){
> insert into mytable values(...);
> }
> commit
>
> During the insert command i get the following error:
> "Lock table is out of available locks"
>
> I tried to resolve the problem starting mysqld with -O
> bdb_max_lock=6  and later with -O bdb_max_lock=12 but i still
> receive the same error.
>
> dbd related variables follows:
>
> mysql> show variables like "%bdb%";
> +-++
> | Variable_name   |
> Value  |
> +-++
> | bdb_cache_size  |
> 8388600|
> | bdb_home|
> /var/lib/mysql/|
> | bdb_log_buffer_size |
> 32768  |
> | bdb_logdir
> ||
> | bdb_max_lock|
> 12 |
> | bdb_shared_data |
> OFF|
> | bdb_tmpdir  |
> /tmp/  |
> | have_bdb|
> YES|
> | version_bdb | Sleepycat Software: Berkeley DB 4.1.24: (May 13,
> 2005) |
> +-++
> 9 rows in set (0.00 sec)
>
> Any hint is welcome..
>
> Thanx in advance for the help,
> Marco
>
> --
> 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: Transaction question - no rollback needed?

[Lou Olsten]

See below
- Original Message - 
From: "Jeremy Smith" <[EMAIL PROTECTED]>
To: "[EMAIL PROTECTED] Mysql. Com" <[EMAIL PROTECTED]>
Sent: Sunday, May 09, 2004 9:31 PM
Subject: Transaction question - no rollback needed?


> Does it make sense to use a transaction just for the row locking
properties,
> and then not needing to error check?
>
> I have a situation where I have seperate files that are being run very
often
> in realtime by many different users.  One is calling a list of 12 football
> players.  The other is updating specific information about that player at
a
> given time.  When the update takes place, occasionally the call for the 12
> players will only return 11 (presumably it is in the middle of an UPDATE).

I don't understand why this would happen under any circumstance. Either your
query should be blocked or it should read a snapshot of the data as it
exists depending on your isolation level.  I can't imagine a scenario where
this would be good (desired) behavior by any RDBMS and it sounds like a
problem.

> So if I set autocommit = 0, begin work, perform the update, and then
commit
> will I in effect guarantee that the other read will wait until the update
is
> done before trying to find its 12 players?

Yes and No.  Using InnoDB, writers do not block readers, so they will still
be able to read committed data, but they won't be able to update or delete
it until your transaction commits.

Lou

>
> I hope I didn't make that too confusing, and I appreciate any help that
can
> be offered.
>
> Thanks,
> Jeremy
>
>
> -- 
> 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: Transaction Not supported

[Mike Blezien]

Thx's for all the responses... are coding was correct, and the database was set 
up correct. The problem was the Perl DBI and DBD::mysql modules where way 
out-dated and didn't support the 'rollback' and 'commit' yet... problem solved 
after updating the modules.

appologizes as this was not related to this mailing list.
--
MikeBlezien
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Thunder Rain Internet Publishing
Providing Internet Solutions that work!
http://www.thunder-rain.com
Quality Web Hosting
http://www.justlightening.net
MSN: [EMAIL PROTECTED]
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=


Joshua J. Kugler wrote:
Hmm...but it *should* work.  DBI::mysql should implement those calls and 
transform them to BEGIN/COMMIT calls.  I wonder why it doesn't.

j- k-

On Tuesday 30 March 2004 11:24 pm, Jonas Lindén said something like:

Sorry Mike, Joshua is absolutly correct. Dont listen to my nonses ;)

What I did was trying to use something like this which  to my knowledge
doesnt work on MySQL servers.
$dbh->commit();
$dbh->rollback();
/Jonas


- Original Message -
From: "Joshua J. Kugler" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Wednesday, March 31, 2004 9:02 AM
Subject: Re: Transaction Not supported
On Tuesday 30 March 2004 09:35 pm, Jonas Lindén said something like:

I dont think that transactions are supported (yet?) by the PERL DBI.
to  "Mike Blezien" <[EMAIL PROTECTED]>.  My response follows.

Yes they are...at least I have used them in a project before using
Perl::DBI.
[EMAIL PROTECTED] ~]$ perl
use DBI;
print $DBI::VERSION, "\n";
1.37
[EMAIL PROTECTED] ~]$

My mysql.pm shows $VERSION = '2.0419'

Are you sure you are connecting to an InnoDB database and that you are
doing


transactions on InnoDB tables?

My code is rather simple:

#Begins the transaction
$dbh->do('BEGIN');
.
.
.
.
$dbh->do('COMMIT');
What does your code look like?

j- k-

--
Joshua J. Kugler
Fairbanks, Alaska
Computer Consultant--Systems Designer
.--- --- ...  ..- .--.- ..- --. .-.. . .-.
[EMAIL PROTECTED]
ICQ#:13706295
Every knee shall bow, and every tongue confess, in heaven, on earth, and
under
the earth, that Jesus Christ is LORD -- Count on it!
--
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: Transaction Not supported

[Joshua J. Kugler]

Hmm...but it *should* work.  DBI::mysql should implement those calls and 
transform them to BEGIN/COMMIT calls.  I wonder why it doesn't.

j- k-

On Tuesday 30 March 2004 11:24 pm, Jonas Lindén said something like:
>  Sorry Mike, Joshua is absolutly correct. Dont listen to my nonses ;)
>
>  What I did was trying to use something like this which  to my knowledge
>  doesnt work on MySQL servers.
>
>  $dbh->commit();
> $dbh->rollback();
>
>  /Jonas
>
> > - Original Message -
> > From: "Joshua J. Kugler" <[EMAIL PROTECTED]>
> > To: <[EMAIL PROTECTED]>
> > Sent: Wednesday, March 31, 2004 9:02 AM
> > Subject: Re: Transaction Not supported
> >
> > On Tuesday 30 March 2004 09:35 pm, Jonas Lindén said something like:
> > > I dont think that transactions are supported (yet?) by the PERL DBI.
> >
> > to  "Mike Blezien" <[EMAIL PROTECTED]>.  My response follows.
> >
> > Yes they are...at least I have used them in a project before using
> > Perl::DBI.
> >
> > [EMAIL PROTECTED] ~]$ perl
> > use DBI;
> >
> > print $DBI::VERSION, "\n";
> > 1.37
> >
> > [EMAIL PROTECTED] ~]$
> >
> >
> > My mysql.pm shows $VERSION = '2.0419'
> >
> > Are you sure you are connecting to an InnoDB database and that you are
>
> doing
>
> > transactions on InnoDB tables?
> >
> > My code is rather simple:
> >
> > #Begins the transaction
> > $dbh->do('BEGIN');
> > .
> > .
> > .
> > .
> > $dbh->do('COMMIT');
> >
> > What does your code look like?
> >
> > j- k-
> >
> > --
> > Joshua J. Kugler
> > Fairbanks, Alaska
> > Computer Consultant--Systems Designer
> > .--- --- ...  ..- .--.- ..- --. .-.. . .-.
> > [EMAIL PROTECTED]
> > ICQ#:13706295
> > Every knee shall bow, and every tongue confess, in heaven, on earth, and
> > under
> > the earth, that Jesus Christ is LORD -- Count on it!
> >
> > --
> > MySQL General Mailing List
> > For list archives: http://lists.mysql.com/mysql
> > To unsubscribe:
> > http://lists.mysql.com/[EMAIL PROTECTED]

-- 
Joshua J. Kugler
Fairbanks, Alaska
Computer Consultant--Systems Designer
.--- --- ...  ..- .--.- ..- --. .-.. . .-.
[EMAIL PROTECTED]
ICQ#:13706295
Every knee shall bow, and every tongue confess, in heaven, on earth, and under 
the earth, that Jesus Christ is LORD -- Count on it!

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Transaction Not supported

[Jonas Lindén]

 Sorry Mike, Joshua is absolutly correct. Dont listen to my nonses ;)

 What I did was trying to use something like this which  to my knowledge
 doesnt work on MySQL servers.

 $dbh->commit();
$dbh->rollback();

 /Jonas


> - Original Message - 
> From: "Joshua J. Kugler" <[EMAIL PROTECTED]>
> To: <[EMAIL PROTECTED]>
> Sent: Wednesday, March 31, 2004 9:02 AM
> Subject: Re: Transaction Not supported
>
>
> On Tuesday 30 March 2004 09:35 pm, Jonas Lindén said something like:
> > I dont think that transactions are supported (yet?) by the PERL DBI.
>
> to  "Mike Blezien" <[EMAIL PROTECTED]>.  My response follows.
>
> Yes they are...at least I have used them in a project before using
> Perl::DBI.
>
> [EMAIL PROTECTED] ~]$ perl
> use DBI;
>
> print $DBI::VERSION, "\n";
> 1.37
>
> [EMAIL PROTECTED] ~]$
>
>
> My mysql.pm shows $VERSION = '2.0419'
>
> Are you sure you are connecting to an InnoDB database and that you are
doing
> transactions on InnoDB tables?
>
> My code is rather simple:
>
> #Begins the transaction
> $dbh->do('BEGIN');
> .
> .
> .
> .
> $dbh->do('COMMIT');
>
> What does your code look like?
>
> j- k-
>
> -- 
> Joshua J. Kugler
> Fairbanks, Alaska
> Computer Consultant--Systems Designer
> .--- --- ...  ..- .--.- ..- --. .-.. . .-.
> [EMAIL PROTECTED]
> ICQ#:13706295
> Every knee shall bow, and every tongue confess, in heaven, on earth, and
> under
> the earth, that Jesus Christ is LORD -- Count on it!
>
> -- 
> 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: Transaction Not supported

[Joshua J. Kugler]

On Tuesday 30 March 2004 09:35 pm, Jonas Lindén said something like:
> I dont think that transactions are supported (yet?) by the PERL DBI.

to  "Mike Blezien" <[EMAIL PROTECTED]>.  My response follows.

Yes they are...at least I have used them in a project before using Perl::DBI.

[EMAIL PROTECTED] ~]$ perl
use DBI;

print $DBI::VERSION, "\n";
1.37

[EMAIL PROTECTED] ~]$


My mysql.pm shows $VERSION = '2.0419'

Are you sure you are connecting to an InnoDB database and that you are doing 
transactions on InnoDB tables?

My code is rather simple:

#Begins the transaction
$dbh->do('BEGIN');
.
.
.
.
$dbh->do('COMMIT');

What does your code look like?

j- k-

-- 
Joshua J. Kugler
Fairbanks, Alaska
Computer Consultant--Systems Designer
.--- --- ...  ..- .--.- ..- --. .-.. . .-.
[EMAIL PROTECTED]
ICQ#:13706295
Every knee shall bow, and every tongue confess, in heaven, on earth, and under 
the earth, that Jesus Christ is LORD -- Count on it!

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Transaction Not supported

[Jonas Lindén]

I dont think that transactions are supported (yet?) by the PERL DBI.

/Jonas


- Original Message - 
From: "Mike Blezien" <[EMAIL PROTECTED]>
To: "MySQL List" <[EMAIL PROTECTED]>
Sent: Tuesday, March 30, 2004 9:42 PM
Subject: Transaction Not supported


> Hello,
>
> I keep getting this error when attempting to utilize a database that has
Innodb
> tables, and using transactions. This is from a Perl/DBI script too:
>
> Error:
> Transactions not supported by database at module. bla..bla
>
> I realize this maybe a DBI question, but I thought I'd check here first
incase
> we missed something is the settings for the MySQL server
>
> Version: 4.0.17-standard - Official MySQL-standard binary
> and it has innodb enabled, the autocommit is set to 1, then when the
transaction
> is executed, the autocommit is set to 0 untill the commit is called
>
> Is there something in the variables I can check to make sure transaction
have
> been enabled ??
>
> TIA
> -- 
> MikeBlezien
> =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
> Thunder Rain Internet Publishing
> Providing Internet Solutions that work!
> http://www.thunder-rain.com
> Quality Web Hosting
> http://www.justlightening.net
> MSN: [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: transaction

[Daniel Kasak]

Luiz Rafael Culik Guimaraes wrote:

  Daniel

I searched  all myql 4.0 what new page and did found any reference, is their
other page

Regards
Luiz

  

Press Release:
http://www.mysql.com/press/release_2002_11.html

Documentation:
http://www.mysql.com/doc/en/InnoDB_transaction_model.html
http://www.mysql.com/doc/en/InnoDB_overview.html

Basically, if you want transactions, you should use the InnoDB table
type.

You do this by adding 'type=innodb' at the end of your 'create table'
command:

create table MyTable ( enter table definition ) type=innodb;

You can alter existing tables to innodb by issuing the command:

alter table MyTable type=innodb;

Or you can use the GUI MySQLCC to change table types.

-- 

signature
Daniel Kasak

IT Developer

NUS Consulting Group

Level 5, 77 Pacific Highway

North Sydney, NSW, Australia 2060

T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989

email: [EMAIL PROTECTED]

website: http://www.nusconsulting.com.au



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: transaction

[Luiz Rafael Culik Guimaraes]

CanIt Vote for ID 3429Daniel

I searched  all myql 4.0 what new page and did found any reference, is their
other page

Regards
Luiz


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.588 / Virus Database: 372 - Release Date: 14/2/2004


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: transaction

[Daniel Kasak]

Luiz Rafael Culik Guimaraes wrote:

Dear Friends

Does mysql version 4.0.x support the follow sql commands
begin transaction
commit
rollback
Regards
Luiz
 

Yes.
Your time would have been better spent searching on the MySQL web site 
or in the documentation that posting that question here.

--
Daniel Kasak
IT Developer
NUS Consulting Group
Level 5, 77 Pacific Highway
North Sydney, NSW, Australia 2060
T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989
email: [EMAIL PROTECTED]
website: http://www.nusconsulting.com.au
-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: transaction support

[Bryan Koschmann - GKT]

Hi Chris,

My apologies, I didn't correctly explain what I was looking for. I mean
more of a solution to creating a client program. I'm thinking of php-gtk
but not sure how well this works under windows, especially for printing
reports.

Thanks,

Bryan

On Sat, 17 Jan 2004, Chris Nolan wrote:

> Hmm...have you looked at Rekall?
>
> www.total-rekall.co.uk
>
> Also, you might want to check out OpenOffice.org's database interface
> features
>
> Regards,
>
> Chris
>


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: transaction support

[Chris Nolan]

Hmm...have you looked at Rekall?

www.total-rekall.co.uk

Also, you might want to check out OpenOffice.org's database interface
features

Regards,

Chris

On Sat, 2004-01-17 at 11:12, Bryan Koschmann - GKT wrote:
> I wanted to thank everyone for their responses and information regarding
> this. I apologize, I thought I had already replied.
> 
> Anyhow, I proved my point to our software guy, enough that he is willing
> to look into it further. Although I have a feeling he isn't going to want
> to do it (but at least he sees what MySQL can do) so I'll probably be
> trying to do it myself.
> 
> So if anyone knows of a way to get a full interface under windows (that
> can print nice invoices) and has a direct brain-input for learning, let me
> know :)
> 
> Thanks again,
> 
>   Bryan
> 


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: transaction support

[Bryan Koschmann - GKT]

I wanted to thank everyone for their responses and information regarding
this. I apologize, I thought I had already replied.

Anyhow, I proved my point to our software guy, enough that he is willing
to look into it further. Although I have a feeling he isn't going to want
to do it (but at least he sees what MySQL can do) so I'll probably be
trying to do it myself.

So if anyone knows of a way to get a full interface under windows (that
can print nice invoices) and has a direct brain-input for learning, let me
know :)

Thanks again,

Bryan


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: transaction support

[Needham, Duane]

On Tuesday, January 06, 2004 2:54 PM, Patrick Shoaf wrote:

> At 09:37 PM 1/5/2004, Bryan Koschmann - GKT wrote:
> >Now, I know there are a few discrepancies there but I just don't know 
> >enough to argue it. I * need* to use MySQL as the server because of 
> >cost reasons. I *WANT* to use MySQL because I don't care for MS choose 
> >not to run their products.
> >
> >If you can give me any information to help me argue this I would really 
> >appreciate it.

> I have previously used Borland's Delphi for various programs I needed to 
> write in the M$ environment.
> ...
> When I am forced to use M$ platform, I use every means at my 
> disposal to use non-M$ products to accomplish the job.  I use Apache for 
> Web Servers, Perl for Web Scripting, ColdFusion for dynamic web sites 
> accessing various DBs, Bind for DNS, MySQL for all new DBs, etc.  For
those 
> of us that prefer to not use M$, but are forced to use the OS, I always
try 
> my best to make sure I can at least program in something other than 
> MS.

I've used MS Visual Basic quite a bit over the years. But I do a lot of
cross-platform stuff now so I had to research alternatives. Consequently,
I've spent a good deal of time with other languages (C++ and Java), looked
at various IDEs (Borland and Metrowerks), etc. However, though it would be
considered relatively new to Windows, Real Basic is a visual development
language and IDE that you might want to take a look at. It has been around
for several years on the Macintosh platform, and, as I said, has the ability
to compile cross-platform from the same code base (similar to Java). It's
object-oriented language is like a cross between MS Visual Basic and Java.
The distinguishing feature of the "pro" version is that it comes with
database plug-ins for the IDE (including one for MySQL). These plug-ins are
compiled code add-ins (written in C++) that basically encapsulate most of
the functionality of the MySQL C API and mapping the calls to the Real Basic
language. I'm pretty sure you can get a demo version to evaluate whether or
not it supports all the functionality you want. I don't personally have any
experience creating solutions that require transaction support, but I
believe it supports at least the basic elements of transactions with MySQL.

Duane

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: transaction support

[Patrick Shoaf]

At 09:37 PM 1/5/2004, Bryan Koschmann - GKT wrote:
Hi,

I'm trying to get a software designer to write us some software using
MySQL as the database server (he currently requires MS SQL). It is all
windows based software (written in VB).
So far his arguments against it are this (not my words):

-No explicit transactional support
-MySQL is still buggy
-MyODBC is buggy and not used in production environments
-Only way to connect using ODBC is third party drivers that cost over
half as much as MS SQL
This is just for our current software, the new software he is bidding on
says he would use .NET so that supposedely causes other problems.
Now, I know there are a few discrepancies there but I just don't know
enough to argue it. I * need* to use MySQL as the server because of cost
reasons. I *WANT* to use MySQL because I don't care for MS choose not to
run their products.
If you can give me any information to help me argue this I would really
appreciate it.
Thanks,

Bryan
I have previously used Borland's Delphi for various programs I needed to 
write in the M$ environment.  This program was very solid and had very good 
ODBC support.  While it has been awhile since I used it (version 2.0, they 
are now at or beyond 6.0)  I have had many individuals claim that the 
Borland programming languages/compilers are the best and most stable 
compilers running under M$ Windows.  Borland has various Visual programming 
languages available.  you might want to suggest your programming try one of 
Borland's programs over the MS programs, if you really don't care to use M$ 
products.  When I am forced to use M$ platform, I use every means at my 
disposal to use non-M$ products to accomplish the job.  I use Apache for 
Web Servers, Perl for Web Scripting, ColdFusion for dynamic web sites 
accessing various DBs, Bind for DNS, MySQL for all new DBs, etc.  For those 
of us that prefer to not use M$, but are forced to use the OS, I always try 
my best to make sure I can at least program in something other than 
MS.  Whenever I am forced to program using a M$ compiler, I always add a 
line to all agreements, that code broken is not the fault of the 
programmer, but rather the fault of M$ and their constant change in 
procedures, compilers, and bugs.  I have never heard and programmer who 
uses M$ C Compiler ever say they have had there program work right and stay 
working after each and every patch, or upgrade of the M$ compiler and 
operating system.  Most programmers who use M$ C compiler constantly 
complain they must write code that works around a bug in the compiler or 
OS, only to have to rewrite the code after M$ tries to fix their bug.



Patrick J. Shoaf, Systems Engineer
[EMAIL PROTECTED]


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: transaction support

[Jochem van Dieten]

Morten Gulbrandsen wrote:
http://www.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&q=MySQL+toy+marston

Subject: Re: Can MySQL table handle 3 million+ entries?

Newsgroups: comp.lang.php
Date: 2003-04-11 15:20:10 PST
MySQL is NOT a toy database - it is far superior to many I have used in my
long career. The lack of constraints is NOT a weakness. It is eminently
possible to create reliable applications without the need for database
constraints - I should know because I have designed and built many
applications that did not use database constraints (mainly because they were
not available). Developers only rely on database constraints to circumvent
their sloppy code. Anything that can be done within the database can also be
done within application code.
As an application developer, I don't trust employees (including 
myself) to enter the data correctly and hence don't let it pass 
unchecked but validate it in the application layer. I belief that 
is a rather common mindset amongst application developers, which 
matches best practice recommendations:
http://www-106.ibm.com/developerworks/library/l-sp2.html

As a database administrator, why should I trust application 
developers (including myself) to validate the data correctly and 
let the data pass unchecked?

Jochem

--
I don't get it
immigrants don't work
and steal our jobs
- Loesje
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: transaction support

[robert_rowe]

Direct your developer to www.vbmysql.com. This site is dedicated to supporting 
developers who want to use MySQL with VB. We will be happy to help him with anything 
he runs into while writing your system for you.

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: transaction support

[Peter Lovatt]

Hi

We have been running MySql since 1998 and have never had any data corruption

We have servers running millions of queries a day and they are bullet proof.

>
> > Hi,
> >
> > I'm trying to get a software designer to write us some software using
> > MySQL as the database server (he currently requires MS SQL). It is all
> > windows based software (written in VB).
> >
> > So far his arguments against it are this (not my words):
> >
> > -No explicit transactional support

wrong

http://www.mysql.com/doc/en/ANSI_diff_Transactions.html



> > -MySQL is still buggy

wrong

MySql code audit showed it was 6 times less buggy than most commercial
software

http://www.reasoning.com/newsevents/pr/12_15_03.html

just whisper the word 'slammer' in his ear and see what response you get :)


> > -MyODBC is buggy and not used in production environments

don't know - we use php, but I have used MyODBC occasionally without
problems


> > -Only way to connect using ODBC is third party drivers that cost over
> > half as much as MS SQL
> >

wrong
MyODBC is available under GPL
http://www.mysql.com/products/myodbc/index.html

unless you sell your software in which case licence fees may be due, but
they are very reasonable.


HTH

Peter






> > This is just for our current software, the new software he is bidding on
> > says he would use .NET so that supposedely causes other problems.
> >
> > Now, I know there are a few discrepancies there but I just don't know
> > enough to argue it. I * need* to use MySQL as the server because of cost
> > reasons. I *WANT* to use MySQL because I don't care for MS choose not to
> > run their products.
> >
> > If you can give me any information to help me argue this I would really
> > appreciate it.
> >
> > Thanks,
> >
> > Bryan
> >
> >
> > --
> > 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]




-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: transaction support

[PeterWR]

Hi,

I have been using MySQL since 3.2x and now 4.0.1x in Windows NT and Windows
2000 IIS / .asp environment including MyODBC for more than 4 years now, and
never lost any data. Setup problems have been solved by reading and
following instruction in the manual, or asking in the support / newsgroups.

So, you can have a pricewise cheap database (remember to contribute to the
company), with "free" worldwide support, or a expensive SQL server with only
payable support - if you contribute the same amount to MySQL as to MS, you
might probably (almost) get your personal supportline.

Best regards
Peter
Copenhagen denmark



>
> - Original Message - 
> From: "Bryan Koschmann - GKT" <[EMAIL PROTECTED]>
> To: "MySQL List" <[EMAIL PROTECTED]>
> Sent: Tuesday, January 06, 2004 3:37 AM
> Subject: transaction support
>
>
> > Hi,
> >
> > I'm trying to get a software designer to write us some software using
> > MySQL as the database server (he currently requires MS SQL). It is all
> > windows based software (written in VB).
> >
> > So far his arguments against it are this (not my words):
> >
> > -No explicit transactional support
> > -MySQL is still buggy
> > -MyODBC is buggy and not used in production environments
> > -Only way to connect using ODBC is third party drivers that cost over
> > half as much as MS SQL
> >
> > This is just for our current software, the new software he is bidding on
> > says he would use .NET so that supposedely causes other problems.
> >
> > Now, I know there are a few discrepancies there but I just don't know
> > enough to argue it. I * need* to use MySQL as the server because of cost
> > reasons. I *WANT* to use MySQL because I don't care for MS choose not to
> > run their products.
> >
> > If you can give me any information to help me argue this I would really
> > appreciate it.
> >
> > Thanks,
> >
> > Bryan
> >
> >
> > -- 
> > 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: transaction support

[Daniel Kasak]

Bryan Koschmann - GKT wrote:

[MySQL]
1: START TRANSACTION
2: WITHDRAW $50 from account 32146.
3: DEPOSIT $50 into account 12345.
4: LOG transfer (date/time/teller/etc...) for auditing.
5: COMMIT TRANSACTION
if this failed at step 3 the transaction would be hung and even if rolled
back by server admin the money would still be gone from account 32146
 

It depends why step 3 failed.
Have a look at http://www.innodb.com/ibman.php#Error_handling
If you want to rollback the transaction to the beginning on any error, 
you simply use an 'on error' statement in VB to trap the error, and send 
a rollback command when the error is detected.

--
Daniel Kasak
IT Developer
NUS Consulting Group
Level 5, 77 Pacific Highway
North Sydney, NSW, Australia 2060
T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989
email: [EMAIL PROTECTED]
website: http://www.nusconsulting.com.au
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: transaction support

[Michael D Schleif]

Bryan Koschmann - GKT <[EMAIL PROTECTED]> [2004:01:05:18:58:12-0800] scribed:


> Here is the explanation I just received when asking for the difference
> between transaction support:
> 
> -
> MySQL uses single-action implicit and explicit transactions, but they do
> not
> automatically roll back on failure.
> 
> SQL Server user single action implicit and batch explicit transactions as
> well as supporting transaction nesting (transactions inside of parent
> transactions) and on failure the entire batch and any parent / peer
> transactions are rolled back.

   
   
   

hth

-- 
Best Regards,

mds
mds resource
877.596.8237
-
Dare to fix things before they break . . .
-
Our capacity for understanding is inversely proportional to how much
we think we know.  The more I know, the more I know I don't know . . .
--


pgp0.pgp
Description: PGP signature

Re: transaction support

[Morten Gulbrandsen]

Well,
I think this statement does it all,

http://www.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&q=MySQL+toy+marston

Subject: Re: Can MySQL table handle 3 million+ entries?

Newsgroups: comp.lang.php
Date: 2003-04-11 15:20:10 PST


MySQL is NOT a toy database - it is far superior to many I have used in my
long career. The lack of constraints is NOT a weakness. It is eminently
possible to create reliable applications without the need for database
constraints - I should know because I have designed and built many
applications that did not use database constraints (mainly because they were
not available). Developers only rely on database constraints to circumvent
their sloppy code. Anything that can be done within the database can also be
done within application code. I have seen what happens when poor programmers
try to shift logic from their code into the database - they get it wrong and
then blame the database for their incompetence.

I am used to designing and building applications without relying on database
'features', so I write my code accordingly. It also means that the logic is
maintained in one place and not it bits and pieces here and there.

Tony Marston

http://www.tonymarston.co.uk/php-mysql/index.html


some advantage does MySQL have,
Rock stable,
fast,
good support,

I got much respone from a comparison about what other developers feel here,

http://groups.google.com/groups?q=mysql+gulbrandsen+rdbms&hl=en&lr=&ie=UTF-8&oe=UTF-8&selm=60ca69db.0308210016.822e230%40posting.google.com&rnum=1

Yours sincerely

Morten Gulbrandsen


- Original Message - 
From: "Bryan Koschmann - GKT" <[EMAIL PROTECTED]>
To: "MySQL List" <[EMAIL PROTECTED]>
Sent: Tuesday, January 06, 2004 3:37 AM
Subject: transaction support


> Hi,
>
> I'm trying to get a software designer to write us some software using
> MySQL as the database server (he currently requires MS SQL). It is all
> windows based software (written in VB).
>
> So far his arguments against it are this (not my words):
>
> -No explicit transactional support
> -MySQL is still buggy
> -MyODBC is buggy and not used in production environments
> -Only way to connect using ODBC is third party drivers that cost over
> half as much as MS SQL
>
> This is just for our current software, the new software he is bidding on
> says he would use .NET so that supposedely causes other problems.
>
> Now, I know there are a few discrepancies there but I just don't know
> enough to argue it. I * need* to use MySQL as the server because of cost
> reasons. I *WANT* to use MySQL because I don't care for MS choose not to
> run their products.
>
> If you can give me any information to help me argue this I would really
> appreciate it.
>
> Thanks,
>
> Bryan
>
>
> -- 
> 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: transaction support

[Bryan Koschmann - GKT]

On Mon, 5 Jan 2004, Craig Vincent wrote:

>   Although this doesn't answer your initial request...why are you wanting to
> 'argue' with this guy over the database to use.  If he won't code for the
> application to use MySQL (which isn't all that hard in VB regardless of what
> he says) then get another coder...as simple as that.  There's a plethora of
> coding contractor sites out there any of which probably have a good base of
> coders with the skills you need capable of coding your software using VB and
> MySQL.


Craig (and others)

It's a good question and implied by Daniel as well. I should have stated
the reason in the beginning. The coder is not only an old employee but a
good friend as well, and basically writes the software in exchange for
discounts on computer parts.

While he is set on M$ stuff, he has always been open to MySQL to a point.
The problem is he is perfectly comfortable with M$ products and just
doesn't see the need to look at MySQL.

Here is the explanation I just received when asking for the difference
between transaction support:

-
MySQL uses single-action implicit and explicit transactions, but they do
not
automatically roll back on failure.

SQL Server user single action implicit and batch explicit transactions as
well as supporting transaction nesting (transactions inside of parent
transactions) and on failure the entire batch and any parent / peer
transactions are rolled back.

EXAMPLE (bank transfer):
[SqlServer]
1: BEGIN TRANSACTION
2: WITHDRAW $50 from account 32146.
3: DEPOSIT $50 into account 12345.
4: LOG transfer (date/time/teller/etc...) for auditing.
5: COMMIT TRANSACTION

if this failed at step 3 due to an error, every change would be undone
including the withdrawal and everything would be ok.  The transaction
could
be retried later.

[MySQL]
1: START TRANSACTION
2: WITHDRAW $50 from account 32146.
3: DEPOSIT $50 into account 12345.
4: LOG transfer (date/time/teller/etc...) for auditing.
5: COMMIT TRANSACTION

if this failed at step 3 the transaction would be hung and even if rolled
back by server admin the money would still be gone from account 32146
---

Is this actually correct, or is it correct but there is a different way to
do this with MySQL?

Thanks,

Bryan


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: transaction support

[Ed Leafe]

On Jan 5, 2004, at 9:37 PM, Bryan Koschmann - GKT wrote:

-No explicit transactional support
	Wrong. InnoDB tables support transactions.

-MySQL is still buggy
	Care to have him specify what bugs he is referring to? I've been using 
MySQL for 2 years now, and use Microsoft SQL Server for about 3 years 
before that. I ran into some MSSQL bugs, but have yet to run into 
anything more than documentation bugs in MySQL.

-MyODBC is buggy and not used in production environments
	There are some problems with MyODBC when used with some Microsoft 
products. The cynic in me feels that Microsoft deliberately creates 
these bugs, since I've used the exact same ODBC drivers with 
non-Microsoft products without a problem. I've used the MyODBC drivers 
in productions apps with Microsoft Visual FoxPro as the front end for 
over a year now, and have yet to have a problem.

-Only way to connect using ODBC is third party drivers that cost over
half as much as MS SQL
	See above. This is a total crock.

	Overall, sounds more like he is comfortable with Microsoft SQL Server, 
and is fabricating these arguments as excuses he can use to stay in his 
comfort zone. As a consultant myself, I would be very suspicious of 
anyone who tailors a solutions to his needs instead of yours. I'd not 
only consider using MySQL, I'd consider looking for a different 
developer.

 ___/
/
   __/
  /
 /
 Ed Leafe
Linux Love:
unzip;strip;touch;finger;mount;fsck;more;yes;umount;sleep
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: transaction support

[Craig Vincent]

Bryan,

Although this doesn't answer your initial request...why are you wanting to
'argue' with this guy over the database to use.  If he won't code for the
application to use MySQL (which isn't all that hard in VB regardless of what
he says) then get another coder...as simple as that.  There's a plethora of
coding contractor sites out there any of which probably have a good base of
coders with the skills you need capable of coding your software using VB and
MySQL.

Craig


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: transaction support

[Daniel Kasak]

Bryan Koschmann - GKT wrote:

Hi,

I'm trying to get a software designer to write us some software using
MySQL as the database server (he currently requires MS SQL). It is all
windows based software (written in VB).
So far his arguments against it are this (not my words):

-No explicit transactional support
 

InnoDB tables have transaction support.

-MySQL is still buggy
 

No it's not. That's a bit of a nebulous claim.

-MyODBC is buggy and not used in production environments
 

Not for us. We've been using it in a production environment for 4 years 
with no issues.

-Only way to connect using ODBC is third party drivers that cost over
half as much as MS SQL
 

Absolute bulldust. The drivers are free, and not 3rd party drivers, but 
developed by MySQL, alongside the server product.

It sounds like you have a stubbord software developer on your hands who 
doesn't want to use anything other than M$ toys. I suggest you get 
another software developer, or at least threaten to.

--
Daniel Kasak
IT Developer
NUS Consulting Group
Level 5, 77 Pacific Highway
North Sydney, NSW, Australia 2060
T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989
email: [EMAIL PROTECTED]
website: http://www.nusconsulting.com.au
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Transaction newbie question

[Mike Gollub]

Thanks Peter.

I strongly suspect that I'm getting deadlocks where it's simply a lock contention.  
Grrr.

I chose BDB because my client has it available on his installation.  I'll configure 
InnoDB support
if necessary.  It looks like a LOT less headache.

Thanks.

- Mike.

__
Do you Yahoo!?
Free Pop-Up Blocker - Get it now
http://companion.yahoo.com/

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Transaction newbie question

[Peter Sap]

Hi Mike,

1. It has already been rolled back, so do a start transaction again.
2. -
3. It depends, 3 to 5 times would be reasonable. Before resubmitting the
transaction wait some time (let's say half a second) to let the conflicting
transaction finish.

When you keep getting deadlocks, try switching to InnoDB (row locks in stead
of page locks) and take another look at the tranactions themselve like the
sequence of insert/delete/update/select.

Hope this helps.

Peter Sap.

- Original Message -
From: "Mike Gollub" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Friday, November 21, 2003 12:38 AM
Subject: Re: Transaction newbie question


> OK, I don't seem to be getting a response.  Let me try to simplify:
>
> I'm running a PHP application with MySQL.
> I'm in a transaction involving only BDB tables.  Actually the whole
database has nothing but BDB
> tables.
> I attempt a query which fails with error number 1213.  The full text
message is something like:
> Can't get lock because of a deadlock.
>
> My questions:
> 1. When the query fails am I still inside the transaction, or has it
already been rolled back ?
> 2. If the transaction has not been rolled back, how many times should I
re-try the query before
> giving up and re-starting the transaction ?
> 3. If the transaction has been rolled back, what is a reasonable number of
times to re-start the
> transaction ?
>
> - Mike.



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Transaction newbie question

[Mike Gollub]

OK, I don't seem to be getting a response.  Let me try to simplify:

I'm running a PHP application with MySQL.
I'm in a transaction involving only BDB tables.  Actually the whole database has 
nothing but BDB
tables.
I attempt a query which fails with error number 1213.  The full text message is 
something like:
Can't get lock because of a deadlock.

My questions:
1. When the query fails am I still inside the transaction, or has it already been 
rolled back ?
2. If the transaction has not been rolled back, how many times should I re-try the 
query before
giving up and re-starting the transaction ?
3. If the transaction has been rolled back, what is a reasonable number of times to 
re-start the
transaction ?

- Mike.

__
Do you Yahoo!?
Free Pop-Up Blocker - Get it now
http://companion.yahoo.com/

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Transaction

[Dathan Vance Pattishall]

- Dathan Vance Pattishall
  - Sr. Programmer and mySQL DBA for FriendFinder Inc.
  - http://friendfinder.com/go/p40688


-->-Original Message-
-->From: Arnoldus Th.J. Koeleman [mailto:[EMAIL PROTECTED]
-->Sent: Thursday, November 06, 2003 1:24 PM
-->To: [EMAIL PROTECTED]
-->Subject: Transaction
-->
-->I am new in MySQL and am used to work with Oracle.
-->
-->
-->
-->If you work with MyISAM tables is it possible to rollback
transaction.


No not at the db level with code yes.

-->
-->
-->
-->And what is a better way to install a production application with
MyISAM
-->tables or InnoDB?


Depends on what your system needs. If you need transactions use innodb.
If you need full text searches use myISAM etc.
 




--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: transaction savepoints

[Egor Egorov]

"Robert Morgan" <[EMAIL PROTECTED]> wrote:
> 
> Hi I'm running mysql 4.0.14
> I'm doing an update from a webpage involving 3 tabIes, using 3 update queries and 
> need to be able to roll back to
> before the first update query if the second or third query go pear shape. So I need 
> to set a savepoint to rollback to
> if anyone could show me a an example of the code necessary it would be appreciated.
> 

Before the first UPDATE query create a savepoint

SAVEPOINT savepoin_name

Then execute UPDATE queries. If you want to do rollback use 

ROLLBACK TO SAVEPOINT savepoint_name

You can find info about savepoints at:
http://www.mysql.com/doc/en/Savepoints.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: Transaction support

[Egor Egorov]

Palaparthy <[EMAIL PROTECTED]> wrote:
>   Does MYSQL support transaction concept, which includes issues 
> like commiting data , rollbacking etc???

Yes.
http://www.mysql.com/doc/en/ANSI_diff_Transactions.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: Transaction support

[Roman Neuhauser]

# [EMAIL PROTECTED] / 2003-06-24 12:29:33 +0200:
> # [EMAIL PROTECTED] / 2003-06-24 15:15:33 +0530:
> >   Does MYSQL support transaction concept, which includes issues 
> > like commiting data , rollbacking etc???
> 
> Did you know MySQL has documentation?

# [EMAIL PROTECTED] / 2003-06-24 04:00:02 -0700:
> Roman Neuhauser wrote:
> ># [EMAIL PROTECTED] / 2003-06-24 15:15:33 +0530:
> >> Does MYSQL support transaction concept, which includes issues 
> >>like commiting data , rollbacking etc???
> >
> >   Did you know MySQL has documentation?
>
> Yes mysql supports transactions ...
> they only work on innodb bdb and gemini table types

# [EMAIL PROTECTED] / 2003-06-24 19:07:46 +0800:
> Hello, Roman Neuhauser,
> 
> Install Innodb,mysql really has transaction,u may use java code with
> it or run sql statment directly in mysql.
> 
> === At 2003-06-24, 12:29:00 you wrote: ===
> 
> ># [EMAIL PROTECTED] / 2003-06-24 15:15:33 +0530:
> >>   Does MYSQL support transaction concept, which includes issues 
> >> like commiting data , rollbacking etc???
> >
> >Did you know MySQL has documentation?

I have sent a followup to a post to this list, and received two
off-the-list replies as if I had asked the original question instead
of answering it. This is not the first time such thing has happened,
and given this behavior is specific to this list I wonder whether
MySQL attracts people with reading problems?

-- 
If you cc me or remove the list(s) completely I'll most likely ignore
your message.see http://www.eyrie.org./~eagle/faqs/questions.html

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Transaction support

[Roman Neuhauser]

# [EMAIL PROTECTED] / 2003-06-24 15:15:33 +0530:
>   Does MYSQL support transaction concept, which includes issues 
> like commiting data , rollbacking etc???

Did you know MySQL has documentation?

-- 
If you cc me or remove the list(s) completely I'll most likely ignore
your message.see http://www.eyrie.org./~eagle/faqs/questions.html

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Transaction Support with MyISAM

[Dathan Vance Pattishall]

Mr. Zawodny is right MyISAM natively does not support Transactions, but
Transactions can be done with a set of myISAM tables and a lot of code
specific to supporting transactions w/o a race condition. Basically it's
a long a tedious process of writing code to support a "ticket server" (a
unique id for every row in any table separated out into a different
table or database), then use this to simulate a commit and rollback. If
you go this route (don't: use INNODB) you'll have to worry about race
conditions, bugs etc.

-Original Message-
From: Jeremy Zawodny [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, March 26, 2003 11:48 PM
To: Nitin Nanivadekar
Cc: [EMAIL PROTECTED]
Subject: Re: Transaction Support with MyISAM

On Wed, Mar 26, 2003 at 09:03:35PM -0800, Nitin Nanivadekar wrote:
> Dear Friends,
>
> The last help I got was truly worth a zillion.

Such a bargin!

> 1. How can i have commit/rollback functions using
> MyIsam database which is default database engine for
> MySql? i am using vb

You cannot.  MyISAM tables are not transactional.  BDB and InnoDB are.

Jeremy
-- 
Jeremy D. Zawodny |  Perl, Web, MySQL, Linux Magazine, Yahoo!
<[EMAIL PROTECTED]>  |  http://jeremy.zawodny.com/

MySQL 4.0.8: up 51 days, processed 1,775,767,802 queries (396/sec. avg)

-- 
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: Transaction Support with MyISAM

[Jeremy Zawodny]

On Wed, Mar 26, 2003 at 09:03:35PM -0800, Nitin Nanivadekar wrote:
> Dear Friends,
>
> The last help I got was truly worth a zillion.

Such a bargin!

> 1. How can i have commit/rollback functions using
> MyIsam database which is default database engine for
> MySql? i am using vb

You cannot.  MyISAM tables are not transactional.  BDB and InnoDB are.

Jeremy
-- 
Jeremy D. Zawodny |  Perl, Web, MySQL, Linux Magazine, Yahoo!
<[EMAIL PROTECTED]>  |  http://jeremy.zawodny.com/

MySQL 4.0.8: up 51 days, processed 1,775,767,802 queries (396/sec. avg)

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Transaction State handling in Mysql.

[Jeff Kilpatrick]

Hello Ramanan-

First off, make sure you create tables of type InnoDB, Berkeley, or
Gemini.  [My]ISAM tables don't do transactions.

The simplest way to do a transaction is
mysql> BEGIN;
mysql> # successful edit
mysql> COMMIT;

or
mysql> BEGIN;
mysql> # failed edit, or dropped connection implies
mysql> ROLLBACK;

To create non-isam tables, append
TYPE = InnoDB
or similar to create statements.  You can convert current tables using
ALTER TABLE, but each type must be compiled in and setup in my.cnf

One caveat: be sure all the tables affected are of one of the above
types or you won't get a full rollback, leaving you in a crazy state
with some badly updated tables (right guys?  I think I read that in the
docs a couple years ago).


-jeff

On Wed, 2003-03-26 at 22:33, [EMAIL PROTECTED] wrote:
> Hi all,
> 
> I want to execute a set of Insert,Update and Delete if any one of the
> statement fails then I need to rollback all the statements.
> 
> How do we achieve the same in MySQL.
> 
> Thanks in advance.
> 
> Ramanan
> 
> 

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Transaction problems using InnoDB, "not locked with LOCKTABLES"

[Steff]

Hi Mark,
 Good call. The way we understand the SQL log it looks like all 
of the SQL  commands we had expected to be part of the 
transaction are on connection 7, while the counter incrementation 
which is done outside of the transaction was done on connection 
12.

  At one point we thought that maybe we where "sharing" a 
connection with something else in the application, but the log 
seems to show that only the transaction is using the connection in 
question.

  Thanks for asking the questions. The more questions 
everybody asks the better the chance that the light bulb will go on 
and show me some stupid thing I am doing without realizing it. :)

   By the way, thanks for the code snippet on the counter. We 
have already modified ourbase class to incorporate this. The code 
is undergoing test today and tomorrow.

Thanks
Steff


On 13 Feb 2003 at 15:10, Mark Matthews wrote:

Date sent:  Thu, 13 Feb 2003 15:10:25 -0600
From:   Mark Matthews <[EMAIL PROTECTED]>
To: [EMAIL PROTECTED]
Copies to:  Heikki Tuuri <[EMAIL PROTECTED]>, 
[EMAIL PROTECTED],
[EMAIL PROTECTED], 
[EMAIL PROTECTED]
Subject:    Re: Transaction problems using InnoDB, "not 
locked with LOCKTABLES"

> -BEGIN PGP SIGNED MESSAGE-
> Hash: SHA1
> 
> [EMAIL PROTECTED] wrote:
> > Heikki,
> >  The application which is having this problem is used to read an
> >  
> > XML document and update a database. The application is part of a
> > website, so there are always other interactions  with the database
> > while the "loader" is running. The log below was created at a time
> > when no one was using the website, so the only thing we had running
> > was 1 instance of the loader. This allowed us to get a view of the
> > actual SQL being submitted by the loader.
> > 
> >   The error did NOT show up when creating this log, and has 
> > never shown up in a non production environment. This is what is
> > leading us to think that there are some other  interactions going on
> > which we are not aware of.  
> > 
> >   In production the loader will cause the "lock" error while
> >   loading 
> > a particular XML file. Then if you immediately try loading the same
> > file again, no error will occur. The table name which shows up in
> > the error can be any one of about 5 tables which are used in this
> > block of SQL statements.  Running multiple loaders at the same time
> > in a test environment never produces the error.  
> > 
> [snip]
> >>Our SQL in this application follows the following pattern. (the
> >>following is a section from the MysQL log with just one instance of
> >>the application running):
> >>
> >>030125 20:24:29   7 Query   SET autocommit=0
> >>  7 Query   BEGIN
> >>  7 Query   Select * from accountsprovided
> >>  Where
> >>ExternalID='I06'AND FinServProvID = 'C33'
> >>  7 Query   UPDATE accountsprovided SET
> >>Enabled='Y' WHERE  AccountID = 'CKCBSBF2994309'
> >>  7 Query   Select * from account Where
> >>  AccountID
> >>=  'CKCBSBF2994309'
> >>  7 Query   UPDATE account SET
> >>PreLimit=1.00 WHERE  AccountID = 'CKCBSBF2994309'
> >>
> >> 12 Query   Lock Table Control Write
> >> 12 Query   Select * From Control
> >> 12 Query   Update Control set NextID =
> >> 6999244 12 Query   Unlock Tables
> >>
> >>  7 Query   INSERT INTO productsowned (
> >>VestedValue, Quantity ) VALUES ( 7293.90, 7293.9)
> >>
> >>...  About another 40 lines of SQL following this same general
> >>pattern but using different tables.
> 
> Your query log actually shows 2 _different_ connections doing 
> work...Thread id 7 and 12. Maybe your application is not using just
> one connection, but you think it is?
> 
>  -Mark
> 
> - -- 
> MySQL 2003 Users Conference -> http://www.mysql.com/events/uc2003/
> 
> For technical support contracts, visit
> https://order.mysql.com/?ref=mmma
> 
>  __  ___ ___   __
> /  |/  /_ __/ __/ __ \/ /  Mark Matthews <[EMAIL PROTECTED]>
>/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer -
>JDBC/Java
>   /_/  /_/\_, /___/\___\_\___/ Flossmoor (Chicago

Re: Transaction problems using InnoDB, "not locked with LOCKTABLES"

[Mark Matthews]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

[EMAIL PROTECTED] wrote:

Heikki,
 The application which is having this problem is used to read an 
XML document and update a database. The application is part of a 
website, so there are always other interactions  with the database 
while the "loader" is running. The log below was created at a time 
when no one was using the website, so the only thing we had 
running was 1 instance of the loader. This allowed us to get a view 
of the actual SQL being submitted by the loader.

  The error did NOT show up when creating this log, and has 
never shown up in a non production environment. This is what is 
leading us to think that there are some other  interactions going on 
which we are not aware of.  

  In production the loader will cause the "lock" error while loading 
a particular XML file. Then if you immediately try loading the same 
file again, no error will occur. The table name which shows up in 
the error can be any one of about 5 tables which are used in this 
block of SQL statements.  Running multiple loaders at the same 
time in a test environment never produces the error.  

[snip]

Our SQL in this application follows the following pattern. (the
following is a section from the MysQL log with just one instance of
the application running):

030125 20:24:29   7 Query   SET autocommit=0
 7 Query   BEGIN
 7 Query   Select * from accountsprovided
 Where
ExternalID='I06'AND FinServProvID = 'C33'
 7 Query   UPDATE accountsprovided SET
Enabled='Y' WHERE  AccountID = 'CKCBSBF2994309'
 7 Query   Select * from account Where
 AccountID
=  'CKCBSBF2994309'
 7 Query   UPDATE account SET
PreLimit=1.00 WHERE  AccountID = 'CKCBSBF2994309'

12 Query   Lock Table Control Write
12 Query   Select * From Control
12 Query   Update Control set NextID =
6999244 12 Query   Unlock Tables

 7 Query   INSERT INTO productsowned (
VestedValue, Quantity ) VALUES ( 7293.90, 7293.9)

...  About another 40 lines of SQL following this same general
pattern but using different tables.


Your query log actually shows 2 _different_ connections doing 
work...Thread id 7 and 12. Maybe your application is not using just one 
connection, but you think it is?

	-Mark

- -- 
MySQL 2003 Users Conference -> http://www.mysql.com/events/uc2003/

For technical support contracts, visit https://order.mysql.com/?ref=mmma

__  ___ ___   __
   /  |/  /_ __/ __/ __ \/ /  Mark Matthews <[EMAIL PROTECTED]>
  / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer - JDBC/Java
 /_/  /_/\_, /___/\___\_\___/ Flossmoor (Chicago), IL USA
<___/ www.mysql.com
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.1.90 (MingW32)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQE+TAnJtvXNTca6JD8RArAgAJ0RwFm6CpqzQoF6I+X5ySHIXWuA5ACgl94k
LQsIJ7K4C/Krjo8SfKiCXYs=
=XJJp
-END PGP SIGNATURE-


-
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: Transaction problems using InnoDB, "not locked with LOCKTABLES"

[Steff]

Heikki,
 The application which is having this problem is used to read an 
XML document and update a database. The application is part of a 
website, so there are always other interactions  with the database 
while the "loader" is running. The log below was created at a time 
when no one was using the website, so the only thing we had 
running was 1 instance of the loader. This allowed us to get a view 
of the actual SQL being submitted by the loader.

  The error did NOT show up when creating this log, and has 
never shown up in a non production environment. This is what is 
leading us to think that there are some other  interactions going on 
which we are not aware of.  

  In production the loader will cause the "lock" error while loading 
a particular XML file. Then if you immediately try loading the same 
file again, no error will occur. The table name which shows up in 
the error can be any one of about 5 tables which are used in this 
block of SQL statements.  Running multiple loaders at the same 
time in a test environment never produces the error.  

  The loaders contain the only code in the system which uses 
explicit transactions. We set the Autocommitt=0 just prior to 
beginning a transaction. Within the transaction block we do not 
issue any of the SQL commands (on this same connection) listed in 
the reference section 8.5. 

   The more I think about this problem the more it feels like we 
are getting a SQL error along the way and not realizing our 
transaction is no longer active. The code which is doing these 
updates is spread across 15 or so methods. I suspect an error 
which is thrown in one method is not setting any attribute in our 
code to tell the other methods not to bother doing there part 
because the transaction has been cancelled due to some SQL 
exception.

  Is there a way to ask a conception if there is an open 
transaction ?

Thanks
Steff


On 13 Feb 2003 at 21:59, Heikki Tuuri wrote:

From:   "Heikki Tuuri" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Copies to:  <[EMAIL PROTECTED]>,
<[EMAIL PROTECTED]>,
    <[EMAIL PROTECTED]>
Subject:Re: Transaction problems using InnoDB, "not 
locked with LOCKTABLES"
Date sent:  Thu, 13 Feb 2003 21:59:22 +0200

> Steff,
> 
> a note on terminology: every query inside InnoDB always happens inside
> a transaction. In the AUTOCOMMIT=1 case there just is an automatic
> commit done at the end of each SQL statement.
> 
> In your log below I cannot see how connection 7 could ever receive the
> error "Table 'productsprovided'  was not locked with LOCK TABLES". If
> that happens, then it is a bug in either your application code or
> MySQL.
> 
> Can you say which SQL statement in the log below received that error?
> 
> Note also:
> http://www.innodb.com/ibman.html#InnoDB_transaction_model
> "
> 8.5 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 (if MySQL binlogging is used),
> ALTER TABLE, BEGIN, 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 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. "
> 
> Regards,
> 
> Heikki
> sql query
> 
> ...
> Our SQL in this application follows the following pattern. (the
> following is a section from the MysQL log with just one instance of
> the application running):
> 
> 030125 20:24:29   7 Query   SET autocommit=0
>   7 Query   BEGIN
>   7 Query   Select * from accountsprovided
>   Where
> ExternalID='I06'AND FinServProvID = 'C33'
>   7 Query   UPDATE accountsprovided SET
> Enabled='Y' WHERE  AccountID = 'CKCBSBF2994309'
>   7 Query   Select * from account Where
>   AccountID
> =  'CKCBSBF2994309'
> 

Re: Transaction problems using InnoDB, "not locked with LOCKTABLES"

[Heikki Tuuri]

Steff,

a note on terminology: every query inside InnoDB always happens inside a
transaction. In the AUTOCOMMIT=1 case there just is an automatic commit done
at the end of each SQL statement.

In your log below I cannot see how connection 7 could ever receive the error
"Table 'productsprovided'  was not locked with LOCK TABLES". If that
happens, then it is a bug in either your application code or MySQL.

Can you say which SQL statement in the log below received that error?

Note also:
http://www.innodb.com/ibman.html#InnoDB_transaction_model
"
8.5 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 (if MySQL binlogging is used), ALTER
TABLE, BEGIN, 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 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.
"

Regards,

Heikki
sql query

...
Our SQL in this application follows the following pattern. (the
following is a section from the MysQL log with just one instance of
the application running):

030125 20:24:29   7 Query   SET autocommit=0
  7 Query   BEGIN
  7 Query   Select * from accountsprovided Where
ExternalID='I06'AND FinServProvID = 'C33'
  7 Query   UPDATE accountsprovided SET
Enabled='Y' WHERE  AccountID = 'CKCBSBF2994309'
  7 Query   Select * from account Where AccountID
=  'CKCBSBF2994309'
  7 Query   UPDATE account SET
PreLimit=1.00 WHERE  AccountID = 'CKCBSBF2994309'

 12 Query   Lock Table Control Write
 12 Query   Select * From Control
 12 Query   Update Control set NextID = 6999244
 12 Query   Unlock Tables

  7 Query   INSERT INTO productsowned (
VestedValue, Quantity ) VALUES ( 7293.90, 7293.9)

...  About another 40 lines of SQL following this same general
pattern but using different tables.

  7 Query   Update cachestatus Set UpdatedOn =
null, UpdatedBy =  'XMLWarehouseLoader' Where PrimaryID =
'CKCBSBF2994310' AND SecondaryID = 'CKCBSBF2994311'
  7 Query   COMMIT
  7 Query   SET autocommit=1

- Original Message -
From: <[EMAIL PROTECTED]>
To: "Heikki Tuuri" <[EMAIL PROTECTED]>
Cc: <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>;
<[EMAIL PROTECTED]>
Sent: Thursday, February 13, 2003 8:54 PM
Subject: Re: Transaction problems using InnoDB, "not locked with LOCKTABLES"


> Heikki,
>   I wish I could reproduce this outside of production. To this point
> the only place we have seen this is in production.  I did turn the
> logging on for MySql and the connection number assigned for all
> the SQL which we would have expected to be within a transaction
> did not change. Within the same time the connection number used
> in the lock table code was always a different connection from the
> connection in use by the "transaction" code.
>
>   Is there an easy way we could check to see if we are within a
> transaction prior to issuing a SQL call?  My current guess is that
> something  is causing us to "terminate" the transaction but we are
> not aware of it, so we continue  doing database updates thinking we
> are within a transaction and thus the "table" does not need to be
> locked.  If we could detect from within our code the existence of an
> open transaction on the connection we are using, then we might be
> able to narrow down  the situation which causes the transaction to
> "break". The checking would have to be a real check back to the
> database, not just some  boolean we keep in our program.
>
>  Any ideas you have on how to narrow the focus of our research
> would be greatly appreciated.
>
> Thanks
> Steff
>
> On 13 Feb 2003 at 20:03, Heikki Tuuri wrote:
>
> From:   "Heikki Tuuri" <[EMAIL PROTECTED]>
&g

Re: Transaction problems using InnoDB, "not locked with LOCKTABLES"

[Steff]

Heikki,
  I wish I could reproduce this outside of production. To this point 
the only place we have seen this is in production.  I did turn the 
logging on for MySql and the connection number assigned for all 
the SQL which we would have expected to be within a transaction 
did not change. Within the same time the connection number used 
in the lock table code was always a different connection from the 
connection in use by the "transaction" code.

  Is there an easy way we could check to see if we are within a 
transaction prior to issuing a SQL call?  My current guess is that 
something  is causing us to "terminate" the transaction but we are 
not aware of it, so we continue  doing database updates thinking we 
are within a transaction and thus the "table" does not need to be 
locked.  If we could detect from within our code the existence of an 
open transaction on the connection we are using, then we might be 
able to narrow down  the situation which causes the transaction to 
"break". The checking would have to be a real check back to the 
database, not just some  boolean we keep in our program.

 Any ideas you have on how to narrow the focus of our research 
would be greatly appreciated.

Thanks
Steff

On 13 Feb 2003 at 20:03, Heikki Tuuri wrote:

From:   "Heikki Tuuri" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Copies to:      <[EMAIL PROTECTED]>
Subject:Re: Transaction problems using InnoDB, "not 
locked with LOCKTABLES"
Date sent:  Thu, 13 Feb 2003 20:03:37 +0200

> Steff,
> 
> - Original Message -
> From: <[EMAIL PROTECTED]>
> To: "Heikki Tuuri" <[EMAIL PROTECTED]>
> Cc: <[EMAIL PROTECTED]>
> Sent: Thursday, February 13, 2003 7:21 PM
> Subject: Re: Transaction problems using InnoDB, "not locked with
> LOCKTABLES"
> 
> 
> > Heikki,
> >Thanks for the reply.
> >
> > My confusion is that we are only using 1 table within our lock
> > area of the code.  The error is coming back on the connection
> > which is not doing any table locks. The error comes when we are in
> > the middle of a transaction block.
> >
> >   Is there some reason a table lock on one connection would
> > affect a transaction tacking place on another connection, provided
> > none of the SQL statements within the transaction touch the table
> > being locked ?
> 
> 
> no. It is a bug either in your application or MySQL. Are you sure you
> are using client connections in the right way, not mixing them?
> 
> Can you create a repeatable test case?
> 
> 
> > Thanks in advance for your guidance.
> >
> > Steff
> 
> Regards,
> 
> Heikki
> 
> sql query
> 
> 
> 
> > On 13 Feb 2003 at 16:48, Heikki Tuuri wrote:
> >
> > From:   "Heikki Tuuri" <[EMAIL PROTECTED]>
> > To: "Mark Matthews" <[EMAIL PROTECTED]>,
> > <[EMAIL PROTECTED]>
> > Copies to:  <[EMAIL PROTECTED]>
> > Subject:Re: Transaction problems using InnoDB, "not
> > locked with LOCKTABLES"
> > Date sent:  Thu, 13 Feb 2003 16:48:42 +0200
> >
> > > Mark, Steff,
> > >
> > > - Original Message -
> > > From: "Mark Matthews" <[EMAIL PROTECTED]>
> > > To: <[EMAIL PROTECTED]>
> > > Cc: <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>
> > > Sent: Wednesday, February 12, 2003 3:09 AM
> > > Subject: Re: Transaction problems using InnoDB, "not locked with
> > > LOCKTABLES"
> > >
> > >
> > > > -BEGIN PGP SIGNED MESSAGE-
> > > > Hash: SHA1
> > > >
> > > > [EMAIL PROTECTED] wrote:
> > > > > Hello,
> > > > > We are experiencing severe problems when running MySql
> > > > > with
> > > > > INNODB in a  production environment.  Applications which work
> > > > > fine under light load fail when under production load.
> > > > >
> > > > > Our MySql environment is as follows:
> > > > > OS Platform: Windows 2000 Service Pack 2
> > > > > Machine description:
> > > > > Compiler   : VC++ 6.0
> > > > > Architecture   : i686
> > > > > Total Memory   : 2097151 KB RAM
> > > > > Server Info3.23.54-max-nt-log
> > > > > have_innodbYES
> > > > > innodb_additional_mem_pool_size104857600

Re: Transaction problems using InnoDB, "not locked with LOCKTABLES"

[Heikki Tuuri]

Steff,

- Original Message -
From: <[EMAIL PROTECTED]>
To: "Heikki Tuuri" <[EMAIL PROTECTED]>
Cc: <[EMAIL PROTECTED]>
Sent: Thursday, February 13, 2003 7:21 PM
Subject: Re: Transaction problems using InnoDB, "not locked with LOCKTABLES"


> Heikki,
>Thanks for the reply.
>
> My confusion is that we are only using 1 table within our lock
> area of the code.  The error is coming back on the connection
> which is not doing any table locks. The error comes when we are in
> the middle of a transaction block.
>
>   Is there some reason a table lock on one connection would
> affect a transaction tacking place on another connection, provided
> none of the SQL statements within the transaction touch the table
> being locked ?


no. It is a bug either in your application or MySQL. Are you sure you are
using client connections in the right way, not mixing them?

Can you create a repeatable test case?


> Thanks in advance for your guidance.
>
> Steff

Regards,

Heikki

sql query



> On 13 Feb 2003 at 16:48, Heikki Tuuri wrote:
>
> From:   "Heikki Tuuri" <[EMAIL PROTECTED]>
> To: "Mark Matthews" <[EMAIL PROTECTED]>,
> <[EMAIL PROTECTED]>
> Copies to:  <[EMAIL PROTECTED]>
> Subject:Re: Transaction problems using InnoDB, "not
> locked with LOCKTABLES"
> Date sent:  Thu, 13 Feb 2003 16:48:42 +0200
>
> > Mark, Steff,
> >
> > - Original Message -
> > From: "Mark Matthews" <[EMAIL PROTECTED]>
> > To: <[EMAIL PROTECTED]>
> > Cc: <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>
> > Sent: Wednesday, February 12, 2003 3:09 AM
> > Subject: Re: Transaction problems using InnoDB, "not locked with
> > LOCKTABLES"
> >
> >
> > > -BEGIN PGP SIGNED MESSAGE-
> > > Hash: SHA1
> > >
> > > [EMAIL PROTECTED] wrote:
> > > > Hello,
> > > > We are experiencing severe problems when running MySql with
> > > > INNODB in a  production environment.  Applications which work fine
> > > > under light load fail when under production load.
> > > >
> > > > Our MySql environment is as follows:
> > > > OS Platform: Windows 2000 Service Pack 2
> > > > Machine description:
> > > > Compiler   : VC++ 6.0
> > > > Architecture   : i686
> > > > Total Memory   : 2097151 KB RAM
> > > > Server Info3.23.54-max-nt-log
> > > > have_innodbYES
> > > > innodb_additional_mem_pool_size104857600
> > > > innodb_buffer_pool_size1048576000
> > > > innodb_data_file_path  ibdata1
> > > > innodb_data_home_dir
> > > > innodb_file_io_threads 4
> > > > innodb_force_recovery  0
> > > > innodb_thread_concurrency  8
> > > > innodb_flush_log_at_trx_commit 1
> > > > innodb_fast_shutdown   ON
> > > > innodb_flush_method
> > > > innodb_lock_wait_timeout   50
> > > > innodb_log_arch_dir
> > > > innodb_log_archive OFF
> > > > innodb_log_buffer_size 1048576
> > > > innodb_log_file_size   5242880
> > > > innodb_log_files_in_group  2
> > > > innodb_log_group_home_dir  .\
> > > > innodb_mirrored_log_groups 1
> > > >
> > > >
> > > > We have a large block of SQL statements (about 50) which we
> > > > would like to treat as  one transaction.  The software works fine
> > > > when running in a limited environment,  but issues intermittent
> > > > errors when running in production.
> > > >
> > > > All the errors follow the pattern of "java.sql.SQLException:
> > > > General error: Table 'productsprovided'  was not locked with LOCK
> > > > TABLES".
> >
> >
> > this is a MySQL error. In MySQL there is no deadlock detection of
> > table level locks. That is why you have to acquire in one shot  locks
> > to all tables you are going to use inside your LOCK TABLES.
> >
> >
> > ...
> > > I suggest taking a look at Paul Dubois' excellent book, 'MySQL
> > > Cookbook' from O'reiley, as he has a whole section on generating
> > > sequences, a few of which require _NO_ locking on your part :)
> > >
> > > -

Re: Transaction problems using InnoDB, "not locked with LOCKTABLES"

[Steff]

Heikki,
   Thanks for the reply.  

My confusion is that we are only using 1 table within our lock 
area of the code.  The error is coming back on the connection 
which is not doing any table locks. The error comes when we are in 
the middle of a transaction block. 

  Is there some reason a table lock on one connection would 
affect a transaction tacking place on another connection, provided 
none of the SQL statements within the transaction touch the table 
being locked ?

Thanks in advance for your guidance.

Steff


On 13 Feb 2003 at 16:48, Heikki Tuuri wrote:

From:   "Heikki Tuuri" <[EMAIL PROTECTED]>
To: "Mark Matthews" <[EMAIL PROTECTED]>,
<[EMAIL PROTECTED]>
Copies to:  <[EMAIL PROTECTED]>
Subject:Re: Transaction problems using InnoDB, "not 
locked with LOCKTABLES"
Date sent:  Thu, 13 Feb 2003 16:48:42 +0200

> Mark, Steff,
> 
> - Original Message -
> From: "Mark Matthews" <[EMAIL PROTECTED]>
> To: <[EMAIL PROTECTED]>
> Cc: <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>
> Sent: Wednesday, February 12, 2003 3:09 AM
> Subject: Re: Transaction problems using InnoDB, "not locked with
> LOCKTABLES"
> 
> 
> > -BEGIN PGP SIGNED MESSAGE-
> > Hash: SHA1
> >
> > [EMAIL PROTECTED] wrote:
> > > Hello,
> > > We are experiencing severe problems when running MySql with
> > > INNODB in a  production environment.  Applications which work fine
> > > under light load fail when under production load.
> > >
> > > Our MySql environment is as follows:
> > > OS Platform: Windows 2000 Service Pack 2
> > > Machine description:
> > > Compiler   : VC++ 6.0
> > > Architecture   : i686
> > > Total Memory   : 2097151 KB RAM
> > > Server Info3.23.54-max-nt-log
> > > have_innodbYES
> > > innodb_additional_mem_pool_size104857600
> > > innodb_buffer_pool_size1048576000
> > > innodb_data_file_path  ibdata1
> > > innodb_data_home_dir
> > > innodb_file_io_threads 4
> > > innodb_force_recovery  0
> > > innodb_thread_concurrency  8
> > > innodb_flush_log_at_trx_commit 1
> > > innodb_fast_shutdown   ON
> > > innodb_flush_method
> > > innodb_lock_wait_timeout   50
> > > innodb_log_arch_dir
> > > innodb_log_archive OFF
> > > innodb_log_buffer_size 1048576
> > > innodb_log_file_size   5242880
> > > innodb_log_files_in_group  2
> > > innodb_log_group_home_dir  .\
> > > innodb_mirrored_log_groups 1
> > >
> > >
> > > We have a large block of SQL statements (about 50) which we
> > > would like to treat as  one transaction.  The software works fine
> > > when running in a limited environment,  but issues intermittent
> > > errors when running in production.
> > >
> > > All the errors follow the pattern of "java.sql.SQLException:
> > > General error: Table 'productsprovided'  was not locked with LOCK
> > > TABLES".
> 
> 
> this is a MySQL error. In MySQL there is no deadlock detection of
> table level locks. That is why you have to acquire in one shot  locks
> to all tables you are going to use inside your LOCK TABLES.
> 
> 
> ...
> > I suggest taking a look at Paul Dubois' excellent book, 'MySQL
> > Cookbook' from O'reiley, as he has a whole section on generating
> > sequences, a few of which require _NO_ locking on your part :)
> >
> > -Mark
> > - --
> > MySQL 2003 Users Conference -> http://www.mysql.com/events/uc2003/
> >
> > For technical support contracts, visit
> > https://order.mysql.com/?ref=mmma
> >
> >  __  ___ ___   __
> > /  |/  /_ __/ __/ __ \/ /  Mark Matthews <[EMAIL PROTECTED]>
> >/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer -
> >JDBC/Java
> >   /_/  /_/\_, /___/\___\_\___/ Flossmoor (Chicago), IL USA
> >  <___/ www.mysql.com
> 
> Best regards,
> 
> Heikki
> Innobase Oy
> sql query
> 
> 


--
Steff McGonagle
Envisage Information Systems, Inc.

Phone (315) 497-9202 x16
www.EnvisageSystems.com
www.Enveritus.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: Transaction problems using InnoDB, "not locked with LOCKTABLES"

[Heikki Tuuri]

Mark, Steff,

- Original Message -
From: "Mark Matthews" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Cc: <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>
Sent: Wednesday, February 12, 2003 3:09 AM
Subject: Re: Transaction problems using InnoDB, "not locked with LOCKTABLES"


> -BEGIN PGP SIGNED MESSAGE-
> Hash: SHA1
>
> [EMAIL PROTECTED] wrote:
> > Hello,
> > We are experiencing severe problems when running MySql with
> > INNODB in a  production environment.  Applications which work
> > fine under light load fail when under production load.
> >
> > Our MySql environment is as follows:
> > OS Platform: Windows 2000 Service Pack 2
> > Machine description:
> > Compiler   : VC++ 6.0
> > Architecture   : i686
> > Total Memory   : 2097151 KB RAM
> > Server Info3.23.54-max-nt-log
> > have_innodbYES
> > innodb_additional_mem_pool_size104857600
> > innodb_buffer_pool_size1048576000
> > innodb_data_file_path  ibdata1
> > innodb_data_home_dir
> > innodb_file_io_threads 4
> > innodb_force_recovery  0
> > innodb_thread_concurrency  8
> > innodb_flush_log_at_trx_commit 1
> > innodb_fast_shutdown   ON
> > innodb_flush_method
> > innodb_lock_wait_timeout   50
> > innodb_log_arch_dir
> > innodb_log_archive OFF
> > innodb_log_buffer_size 1048576
> > innodb_log_file_size   5242880
> > innodb_log_files_in_group  2
> > innodb_log_group_home_dir  .\
> > innodb_mirrored_log_groups 1
> >
> >
> > We have a large block of SQL statements (about 50) which we
> > would like to treat as  one transaction.  The software works fine
> > when running in a limited environment,  but issues intermittent
> > errors when running in production.
> >
> > All the errors follow the pattern of "java.sql.SQLException: General
> > error: Table 'productsprovided'  was not locked with LOCK
> > TABLES".


this is a MySQL error. In MySQL there is no deadlock detection of table
level locks. That is why you have to acquire in one shot  locks to all
tables you are going to use inside your LOCK TABLES.


...
> I suggest taking a look at Paul Dubois' excellent book, 'MySQL Cookbook'
> from O'reiley, as he has a whole section on generating sequences, a few
> of which require _NO_ locking on your part :)
>
> -Mark
> - --
> MySQL 2003 Users Conference -> http://www.mysql.com/events/uc2003/
>
> For technical support contracts, visit https://order.mysql.com/?ref=mmma
>
>  __  ___ ___   __
> /  |/  /_ __/ __/ __ \/ /  Mark Matthews <[EMAIL PROTECTED]>
>/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer - JDBC/Java
>   /_/  /_/\_, /___/\___\_\___/ Flossmoor (Chicago), IL USA
>  <___/ www.mysql.com

Best regards,

Heikki
Innobase Oy
sql 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: Transaction problems using InnoDB, "not locked with LOCKTABLES"

[Steff]

Hi Mark,
Thanks for taking the time to reply.

 I like the idea of changing our counter model to reduce the 
contention around the one file. I will talk to the other developers 
about this.

 I re-read the reference you mentioned 
"http://www.innodb.com/ibman.html#InnoDB_restrictions";  and do 
not see the problem regarding using table level locks on the 
counter table, while we use transaction control on all the rest of the 
tables.  I can see the potential for problems if we mix table locks 
and transaction control on the same table, but in our case the 
counter table is never referenced except by one function and that 
function uses the table level locks, and always uses its own 
connection. 

By re-asking my question I am not trying to say you are wrong, 
I am simply trying to understand things to make sure we can 
remove any other  potential problems we have lurking in our code. 

 Thanks again for taking the time to reply. This problem has 
been haunting us for two months now and yours is the first contact 
we have had with someone who works with MySql in a production 
environment. 

Thanks
Steff

On 11 Feb 2003 at 19:09, Mark Matthews wrote:

Date sent:  Tue, 11 Feb 2003 19:09:16 -0600
From:   Mark Matthews <[EMAIL PROTECTED]>
To: [EMAIL PROTECTED]
Copies to:  [EMAIL PROTECTED], [EMAIL PROTECTED]
Subject:        Re: Transaction problems using InnoDB, "not 
locked with LOCKTABLES"

> -BEGIN PGP SIGNED MESSAGE-
> Hash: SHA1
> 
> [EMAIL PROTECTED] wrote:
> > Hello,
> > We are experiencing severe problems when running MySql with 
> > INNODB in a  production environment.  Applications which work 
> > fine under light load fail when under production load.
> > 
> > Our MySql environment is as follows:
> > OS Platform: Windows 2000 Service Pack 2
> > Machine description:
> > Compiler   : VC++ 6.0
> > Architecture   : i686
> > Total Memory   : 2097151 KB RAM
> > Server Info3.23.54-max-nt-log
> > have_innodbYES
> > innodb_additional_mem_pool_size104857600
> > innodb_buffer_pool_size1048576000
> > innodb_data_file_path  ibdata1
> > innodb_data_home_dir   
> > innodb_file_io_threads 4
> > innodb_force_recovery  0
> > innodb_thread_concurrency  8
> > innodb_flush_log_at_trx_commit 1
> > innodb_fast_shutdown   ON
> > innodb_flush_method
> > innodb_lock_wait_timeout   50
> > innodb_log_arch_dir
> > innodb_log_archive OFF
> > innodb_log_buffer_size 1048576
> > innodb_log_file_size   5242880
> > innodb_log_files_in_group  2
> > innodb_log_group_home_dir  .\
> > innodb_mirrored_log_groups 1
> > 
> > 
> > We have a large block of SQL statements (about 50) which we 
> > would like to treat as  one transaction.  The software works fine
> > when running in a limited environment,  but issues intermittent
> > errors when running in production.
> > 
> > All the errors follow the pattern of "java.sql.SQLException: General
> > error: Table 'productsprovided'  was not locked with LOCK TABLES". 
> > 
> > The table name will be different at various times (even though the
> > code being executed is always the same), but the error is always
> > talking about a failure to lock the tables.
> > 
> >  We are using  two different connections within this one processing 
> > loop. The one connection shows up as number 7 below, while
> > the other is number 12. Our expectation is that these two 
> > connections will operate independently, even though the Java code is
> > working with them both within our "transaction".  Our expectation is
> > that a rollback on connection 7 will NOT effect the activities which
> > had taken place on connection 12 across this same timespan. Along
> > the same lines we would expect the lock and unlock on connection 12
> > will not affect the transaction under way on connection 7. 
> > 
> > Our SQL in this application follows the following pattern. (the
> > following is a section from the MysQL log with just one instance of
> > the application running):
> > 
> > 030125 20:24:29   7 Query   SET autocommit=0
> >   7 Query   BEGIN
> >   7 Query   Select * from accountsprovided
> >   Where
> > ExternalID='I06'

Re: Transaction problems using InnoDB, "not locked with LOCKTABLES"

[Mark Matthews]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

[EMAIL PROTECTED] wrote:

Hello,
We are experiencing severe problems when running MySql with 
INNODB in a  production environment.  Applications which work 
fine under light load fail when under production load.

Our MySql environment is as follows:
OS Platform: Windows 2000 Service Pack 2
Machine description:
Compiler   : VC++ 6.0
Architecture   : i686
Total Memory   : 2097151 KB RAM
Server Info3.23.54-max-nt-log
have_innodbYES
innodb_additional_mem_pool_size104857600
innodb_buffer_pool_size1048576000
innodb_data_file_path  ibdata1
innodb_data_home_dir   
innodb_file_io_threads 4
innodb_force_recovery  0
innodb_thread_concurrency  8
innodb_flush_log_at_trx_commit 1
innodb_fast_shutdown   ON
innodb_flush_method
innodb_lock_wait_timeout   50
innodb_log_arch_dir
innodb_log_archive OFF
innodb_log_buffer_size 1048576
innodb_log_file_size   5242880
innodb_log_files_in_group  2
innodb_log_group_home_dir  .\
innodb_mirrored_log_groups 1


We have a large block of SQL statements (about 50) which we 
would like to treat as  one transaction.  The software works fine 
when running in a limited environment,  but issues intermittent 
errors when running in production.

All the errors follow the pattern of "java.sql.SQLException: General
error: Table 'productsprovided'  was not locked with LOCK 
TABLES". 

The table name will be different at various times (even though the 
code being executed is always the same), but the error is always 
talking about a failure to lock the tables.

 We are using  two different connections within this one processing 
loop. The one connection shows up as number 7 below, while
the other is number 12. Our expectation is that these two 
connections will operate independently, even though the Java code 
is working with them both within our "transaction".  Our expectation 
is that a rollback on connection 7 will NOT effect the activities 
which had taken place on connection 12 across this same
timespan. Along the same lines we would expect the lock and 
unlock on connection 12 will not affect the transaction under way on 
connection 7. 

Our SQL in this application follows the following pattern. (the 
following is a section from the MysQL log with just one instance of 
the application running):

030125 20:24:29   7 Query   SET autocommit=0
  7 Query   BEGIN
  7 Query   Select * from accountsprovided Where
ExternalID='I06'AND FinServProvID = 'C33'
  7 Query   UPDATE accountsprovided SET
Enabled='Y' WHERE  AccountID = 'CKCBSBF2994309'
  7 Query   Select * from account Where AccountID
=  'CKCBSBF2994309'
  7 Query   UPDATE account SET 
PreLimit=1.00 WHERE  AccountID = 'CKCBSBF2994309'

 12 Query   Lock Table Control Write
 12 Query   Select * From Control
 12 Query   Update Control set NextID = 6999244
 12 Query   Unlock Tables

  7 Query   INSERT INTO productsowned (
VestedValue, Quantity ) VALUES ( 7293.90, 7293.9)

...  About another 40 lines of SQL following this same general 
pattern but using different tables.

  7 Query   Update cachestatus Set UpdatedOn =
null, UpdatedBy =  'XMLWarehouseLoader' Where PrimaryID = 
'CKCBSBF2994310' AND SecondaryID = 'CKCBSBF2994311'
  7 Query   COMMIT
  7 Query   SET autocommit=1

We are unable to determine what other activities taking place on 
the server/tables are causing the conflict. Other applications are 
running against this database which are doing allot of Selects and a 
few updates, all without any explicit transaction control 
(autocommit=1). 

 Will someone please provide us with some insights into what we 
are doing wrong?

It appears that you are trying to use two different locking models, and 
not noticing the problems with doing that until you get enough 
concurrency going in your system (i.e. production load levels)

Heikki can add to this, but it's not usually the best idea in the world 
to mix 'LOCK TABLES' and InnoDB (see 
http://www.innodb.com/ibman.html#InnoDB_restrictions) unless you really 
know how it's going to interact.

From your SQL, it appears that you are attempting to use 'LOCK TABLES' 
to implement a 'sequence' generator. There are two shortcomings with 
your approach. First it won't scale well, because all transactions are 
serializing on a single row...which is probably why you're getting these 
problems. Second, you're locking for _every_ transaction, when if you 
want to use this seque

Re: Transaction Support in mysql13.23.54

[Leila Lappin]

I had this problem with my new install on windows.  In my case in turned out
to be that the tables were not created as innoDB even though I had specified
type=innoDB.  I'm not sure why this happened but I fixed the problem by
recreating the tables after I made sure that I have innoDB server running.
I issued
NET STOP MySQL
to shut down the mysql server that was running and started
mysqld-max-nt --console

And then I recreated my tables with type=innoDB option and it worked.


- Original Message -
From: "Jayce^" <[EMAIL PROTECTED]>
To: "Nirmal Shah" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>;
<[EMAIL PROTECTED]>
Sent: Thursday, January 23, 2003 9:44 AM
Subject: Re: Transaction Support in mysql13.23.54


-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On Wednesday 22 January 2003 08:56 pm, Nirmal Shah wrote:
> hi,
> i have installed mysql3.23.54 on windows and have
> followed all instructions as required for using
> mysqld-max to have transaction support.
> i have created a table using TYPE=INNODB, but cannot
> use rollback on it.
> the error i get in my jsp is "transactions not
> supported".
> please advice me on how i can use commit - rollback on
> mysql database tables.

You might check your connection to, I received a similar on a machine
connecting through perl/DBI, and the problem was that the DBI package merely
needed upgrading, mysql had been set up correctly.  If your mysql really is
set up right, you could verify this by trying to perform a transaction via
command line.  If it works there, it's your connection most likely.

- --
- --Jayce^
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.0.7 (GNU/Linux)

iD8DBQE+MCoIA10/0O8cAHgRAgRWAKC1sZxyOoV+He8dZSe+vHbmTwlyMgCfVqrs
c6+peQEq9/gIBwOxASBfYDk=
=Y/8s
-END PGP SIGNATURE-


-
Before posting, please check:
   http://www.mysql.com/doc/ (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail
<[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: Transaction Support in mysql13.23.54

[Mark Matthews]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Robert Tam wrote:

Correct me if I am wrong, but I believe mysql 3.23.54 does not support
transaction support, version 3.23.54 has row locking only.  Transaction
feature starts with version 4.0.x.

Bob


You're corrected :)

3.23.54 supports InnoDB which has transactions.

The real question is, is what JDBC driver is giving the problems with 
'No Transaction Support' It either must be a very old version of 
MM.MySQL or the JDBC-ODBC bridge.

You should upgrade to MySQL Connector/J 3.0.x (Which is what MM.MySQL 
has become, now that I, the developer behind MM.MySQL am employed by 
MySQL AB). It supports transactions :)

See http://www.mysql.com/products/connector-j/

	-Mark


- -- 
MySQL 2003 Users Conference -> http://www.mysql.com/events/uc2003/

For technical support contracts, visit https://order.mysql.com/?ref=mmma

__  ___ ___   __
   /  |/  /_ __/ __/ __ \/ /  Mark Matthews <[EMAIL PROTECTED]>
  / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer - JDBC/Java
 /_/  /_/\_, /___/\___\_\___/ Flossmoor (Chicago), IL USA
<___/ www.mysql.com
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.1.90 (MingW32)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQE+MFZFtvXNTca6JD8RArklAKCnRX99M+OiVYm0gclzJj+b5xeAiQCgkU0D
Gz1lK5Yw1qSxK7/V6pK/Mpw=
=kUJ5
-END PGP SIGNATURE-


-
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: Transaction Support in mysql13.23.54

[Jayce^]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On Wednesday 22 January 2003 08:56 pm, Nirmal Shah wrote:
> hi,
> i have installed mysql3.23.54 on windows and have
> followed all instructions as required for using
> mysqld-max to have transaction support.
> i have created a table using TYPE=INNODB, but cannot
> use rollback on it.
> the error i get in my jsp is "transactions not
> supported".
> please advice me on how i can use commit - rollback on
> mysql database tables.

You might check your connection to, I received a similar on a machine 
connecting through perl/DBI, and the problem was that the DBI package merely 
needed upgrading, mysql had been set up correctly.  If your mysql really is 
set up right, you could verify this by trying to perform a transaction via 
command line.  If it works there, it's your connection most likely.

- -- 
- --Jayce^
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.0.7 (GNU/Linux)

iD8DBQE+MCoIA10/0O8cAHgRAgRWAKC1sZxyOoV+He8dZSe+vHbmTwlyMgCfVqrs
c6+peQEq9/gIBwOxASBfYDk=
=Y/8s
-END PGP SIGNATURE-


-
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: Transaction Support in mysql13.23.54

[Robert Tam]

Correct me if I am wrong, but I believe mysql 3.23.54 does not support
transaction support, version 3.23.54 has row locking only.  Transaction
feature starts with version 4.0.x.

Bob

- Original Message -
From: "Nirmal Shah" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>
Sent: Wednesday, January 22, 2003 7:56 PM
Subject: Transaction Support in mysql13.23.54


> hi,
> i have installed mysql3.23.54 on windows and have
> followed all instructions as required for using
> mysqld-max to have transaction support.
> i have created a table using TYPE=INNODB, but cannot
> use rollback on it.
> the error i get in my jsp is "transactions not
> supported".
> please advice me on how i can use commit - rollback on
> mysql database tables.
> I am connecting to the mysql database thru my java
> web-app.
> thank you in advance.
> regards,
> nirmal
> (23 jan 03)
>
> __
> Do you Yahoo!?
> Yahoo! Mail Plus - Powerful. Affordable. Sign up now.
> http://mailplus.yahoo.com
>
> -
> Before posting, please check:
>http://www.mysql.com/doc/ (the manual)
>http://lists.mysql.com/   (the list archive)
>
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail <[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: Transaction Support in mysql13.23.54

[Pål Arne Hoff]

hi,
i have installed mysql3.23.54 on windows and have
followed all instructions as required for using
mysqld-max to have transaction support.
i have created a table using TYPE=INNODB, but cannot
use rollback on it.
the error i get in my jsp is "transactions not
supported".
please advice me on how i can use commit - rollback on
mysql database tables.
I am connecting to the mysql database thru my java
web-app.
thank you in advance.
regards,
nirmal
(23 jan 03)



Go to this page: http://www.innodb.com/howtouse.html and have a look 
under: How to create tables in the InnoDB format.

Basically you will at least have to specify this line in 
C:\WINDOWS\my.ini and restart your mysql-max server:

innodb_data_file_path=ibdata1:30M

All the best,
Pål Arne Hoff



-
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: Transaction and Row Locking Feature

[Heikki Tuuri]

Robert,

- Original Message -
From: ""Robert Tam"" <[EMAIL PROTECTED]>
Newsgroups: mailing.database.mysql
Sent: Friday, January 10, 2003 7:43 AM
Subject: Transaction and Row Locking Feature


> Hello,
>
> I am a new user to MySQL.  I need innoDB's transaction and row locking
> feature in MySQL.  Could someone clarify whether the latest 3.23.54a of
> MySQL fully supports innoDB transaction and row locking or do I have to
use
> beta/gamma version of MySQL 4.0.x.  Thanks in advance.

transactions and row locking are fully supported in the stable 3.23.

If you want to emulate Oracle with different transaction isolation levels,
they are available starting from 4.0.5.

> R.T.

Best regards,

Heikki Tuuri
Innobase Oy
---
InnoDB - transactions, row level locking, and foreign key support for MySQL
See http://www.innodb.com, download MySQL-Max from http://www.mysql.com

sql 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: Transaction isolation level - potential bug?

[Mark Matthews]

Amine Korch wrote:
> Hello all.
> I have encountered an really annoying problem in MySQL 3.23.49 on Win32.
> I am using InnoDB because I need transactions. I use autocommit off. 
> I'll try best to describe the problem I have: 
> I have two sessions to my DB. 
> Session 1 inserts some data into a table, then commits. 
> Session 2 should normally be able to see data inserted by session 1,
> since session 1 has committed. But it is not the case. 
> The only way I manage to get session 2 to see data inserted at session 1
> is by doing a commit. Is this what should happen?
> 
> Please correct me if I am wrong, but using the READ_COMMITTED
> transaction isolation level, I should be achieving what I want, right? 
> I used the following syntax to ensure that the read_committed level
> (which is apparently default to Mysql) is enforced:
> set global transaction isolation level read_commited;
> 
> I tried using MySQL v4 beta, but same thing happens.
> Thanks for any clarification on what I am doing wrong. 
> 
> 
> -
> 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
> 

InnoDB does not support an isolation level of READ_COMMITTED, only 
REPEATABLE_READ. You will get REPEATABLE_READ when you ask for 
READ_COMMITTED. In most cases this is desirable, because it is easier to 
develop for an isolation level of REPEATABLE_READ, and InnoDB can 
provide this higher isolation level at a higher speed than most 
databases can provide READ_COMMITTED. (see 
http://www.innodb.com/ibman.html#InnoDB_transaction_model)

Heikki has said that support for READ_COMMITTED will come in MySQL-4.0.5.

-Mark
-- 
For technical support contracts, visit https://order.mysql.com/?ref=mmma

__  ___ ___   __
   /  |/  /_ __/ __/ __ \/ /  Mark Matthews <[EMAIL PROTECTED]>
  / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer - JDBC/Java
/_/  /_/\_, /___/\___\_\___/ Flossmoor (Chicago), IL USA
<___/ 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: transaction isolation level

[Jianliang Zhao]

Thanks Heikki,

My testcase is not valid. After I get the connection, I didn't set auto commit to 
false on the connection object, so I was unable to see the committed data. I saw the 
REPEATABLE READ behavior now, but it's unfortunate to know READ COMMITTED is not 
supported yet...

--Jianliang



-Original Message-
From: Heikki Tuuri [mailto:[EMAIL PROTECTED]]
Sent: 2002?10?8? 9:56
To: Jianliang Zhao; Mark Matthews
Cc: [EMAIL PROTECTED]
Subject: Re: transaction isolation level


Jianliang,

I tested that with two mysql clients, and it appeared to work ok. Check with

SHOW CREATE TABLE tablename

what is the type of your table. Is it MyISAM?

Also put the line

log

to the [mysqld] section of your my.cnf and restart mysqld. Then you will see
all received queries in the file 'hostname'.log in your datadir. Check from
it what SQL queries your clients actually send to the mysqld server.

Regards,

Heikki
Innobase Oy


Below my test:
CLIENT A:

heikki@hundin:~/mysql/client> mysql test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 3.23.53-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> set autocmmit = 0;
ERROR 1064: You have an error in your SQL syntax.  Check the manual that
corresp
onds to your MySQL server version for the right syntax to use near
'autocmmit =
0' at line 1
mysql> set autocommit = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> create table zhao (a int not null, b int, primary key (a)) type =
innodb;
Query OK, 0 rows affected (0.11 sec)

mysql> insert into zhao values (10, 20);
Query OK, 1 row affected (0.00 sec)

mysql> commit
-> ;
Query OK, 0 rows affected (0.00 sec)

mysql> update zhao set b = 100;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql>


CLIENT B:

heikki@hundin:~/mysql/client> mysql test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 3.23.53-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> select * from zhao;
++--+
| a  | b|
++--+
| 10 |   20 |
++--+
1 row in set (0.00 sec)

mysql> select * from zhao;
++--+
| a  | b|
++--+
| 10 |   20 |
++--+
1 row in set (0.00 sec)

mysql> select * from zhao;
++--+
| a  | b|
++--+
| 10 |   20 |
++--+
1 row in set (0.00 sec)

mysql> set autocommit = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from zhao;
++--+
| a  | b|
++--+
| 10 |   20 |
++--+
1 row in set (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from zhao;
++--+
| a  | b|
++--+
| 10 |   20 |
++--+
1 row in set (0.00 sec)

mysql>


- Original Message -
From: "Jianliang Zhao" <[EMAIL PROTECTED]>
To: "Heikki Tuuri" <[EMAIL PROTECTED]>; "Mark Matthews"
<[EMAIL PROTECTED]>
Cc: <[EMAIL PROTECTED]>
Sent: Tuesday, October 08, 2002 7:40 PM
Subject: RE: transaction isolation level


Thanks for all your help. Then it's more confusing to me. I opened two MySql
windows and set autocommit=0 and then update one column and then issue the
command COMMIT in one window, in the other MySql window, I queried the
column and saw the change immediately.

I also did the same thing with one MySql window and one JDBC client(my
testcase), and the testcase can see the change immediately.

In either case, instead of issuing COMMIT, if I issue ROLLBACK, the change
will be rolled back. (Just to make sure the auto commit is not on).

Here is my testcase:

import java.sql.*;
import java.util.Properties;
import com.mysql.jdbc.Driver;

public class TestMySql {

public static void main(String[] args) throws Exception {
Driver.class.getName();

Properties props = new Properties();
props.setProperty("user", "root");
props.setProperty("password", "geneva");
Connection conn =
DriverManager.getConnection("jdbc:mysql://localhost/test", props);
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery("select * from testtable");

while(rs.next()) {
   System.out.println(rs.getInt(1));
}

System.out.println("Please update the table TESTTABLE...");
System.in.read();

    rs = stmt.executeQuery("select * from testtable");

while(rs.next()) {
   System.out.println(rs.getInt(1));
}
}

}

-Original Message-
From: Heikki Tuuri [mailto:[EMAIL PROTECTED]]
Sent: 2002?10?8? 0:22
To: Mark Matthews
Cc: [EMAIL PROTECTED]
Subject: Re: transaction isolation level


Hi!

- Original Message -
From: "Mark Matthews" <[EMAIL PROTECTED]>
Newsgroups: mailing

Re: transaction isolation level

[Heikki Tuuri]

Jianliang,

I tested that with two mysql clients, and it appeared to work ok. Check with

SHOW CREATE TABLE tablename

what is the type of your table. Is it MyISAM?

Also put the line

log

to the [mysqld] section of your my.cnf and restart mysqld. Then you will see
all received queries in the file 'hostname'.log in your datadir. Check from
it what SQL queries your clients actually send to the mysqld server.

Regards,

Heikki
Innobase Oy


Below my test:
CLIENT A:

heikki@hundin:~/mysql/client> mysql test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 3.23.53-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> set autocmmit = 0;
ERROR 1064: You have an error in your SQL syntax.  Check the manual that
corresp
onds to your MySQL server version for the right syntax to use near
'autocmmit =
0' at line 1
mysql> set autocommit = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> create table zhao (a int not null, b int, primary key (a)) type =
innodb;
Query OK, 0 rows affected (0.11 sec)

mysql> insert into zhao values (10, 20);
Query OK, 1 row affected (0.00 sec)

mysql> commit
-> ;
Query OK, 0 rows affected (0.00 sec)

mysql> update zhao set b = 100;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql>


CLIENT B:

heikki@hundin:~/mysql/client> mysql test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 3.23.53-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> select * from zhao;
++--+
| a  | b|
++--+
| 10 |   20 |
++--+
1 row in set (0.00 sec)

mysql> select * from zhao;
++--+
| a  | b|
++--+
| 10 |   20 |
++--+
1 row in set (0.00 sec)

mysql> select * from zhao;
++--+
| a  | b|
++--+
| 10 |   20 |
++--+
1 row in set (0.00 sec)

mysql> set autocommit = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from zhao;
++--+
| a  | b|
++--+
| 10 |   20 |
++--+
1 row in set (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from zhao;
++--+
| a  | b|
++--+
| 10 |   20 |
++--+
1 row in set (0.00 sec)

mysql>


- Original Message -
From: "Jianliang Zhao" <[EMAIL PROTECTED]>
To: "Heikki Tuuri" <[EMAIL PROTECTED]>; "Mark Matthews"
<[EMAIL PROTECTED]>
Cc: <[EMAIL PROTECTED]>
Sent: Tuesday, October 08, 2002 7:40 PM
Subject: RE: transaction isolation level


Thanks for all your help. Then it's more confusing to me. I opened two MySql
windows and set autocommit=0 and then update one column and then issue the
command COMMIT in one window, in the other MySql window, I queried the
column and saw the change immediately.

I also did the same thing with one MySql window and one JDBC client(my
testcase), and the testcase can see the change immediately.

In either case, instead of issuing COMMIT, if I issue ROLLBACK, the change
will be rolled back. (Just to make sure the auto commit is not on).

Here is my testcase:

import java.sql.*;
import java.util.Properties;
import com.mysql.jdbc.Driver;

public class TestMySql {

public static void main(String[] args) throws Exception {
Driver.class.getName();

Properties props = new Properties();
props.setProperty("user", "root");
props.setProperty("password", "geneva");
Connection conn =
DriverManager.getConnection("jdbc:mysql://localhost/test", props);
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery("select * from testtable");

while(rs.next()) {
   System.out.println(rs.getInt(1));
}

System.out.println("Please update the table TESTTABLE...");
System.in.read();

rs = stmt.executeQuery("select * from testtable");

while(rs.next()) {
   System.out.println(rs.getInt(1));
}
}

}

-Original Message-
From: Heikki Tuuri [mailto:[EMAIL PROTECTED]]
Sent: 2002?10?8? 0:22
To: Mark Matthews
Cc: [EMAIL PROTECTED]
Subject: Re: transaction isolation level


Hi!

- Original Message -
From: "Mark Matthews" <[EMAIL PROTECTED]>
Newsgroups: mailing.database.mysql
Sent: Tuesday, October 08, 2002 5:07 AM
Subject: Re: transaction isolation level


> Jianliang Zhao wrote:
...
> >>I am connecting to MySql 3.23(innodb) with
> >>mysql-connector-java-2.0.14-bin.jar. I set the global transaction
> >>isolation level to READ COMMITTED. However, I still couldn't see the
> >>committed changes through JDBC client. Does anyone know about this
> >>issue?
...
> The isolation level of READ_COMMITTED has 

RE: transaction isolation level

[Jianliang Zhao]

Thanks for all your help. Then it's more confusing to me. I opened two MySql windows 
and set autocommit=0 and then update one column and then issue the command COMMIT in 
one window, in the other MySql window, I queried the column and saw the change 
immediately.

I also did the same thing with one MySql window and one JDBC client(my testcase), and 
the testcase can see the change immediately.

In either case, instead of issuing COMMIT, if I issue ROLLBACK, the change will be 
rolled back. (Just to make sure the auto commit is not on).

Here is my testcase:

import java.sql.*;
import java.util.Properties;
import com.mysql.jdbc.Driver;

public class TestMySql {

public static void main(String[] args) throws Exception {
Driver.class.getName();

Properties props = new Properties();
props.setProperty("user", "root");
props.setProperty("password", "geneva");
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost/test", 
props);
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery("select * from testtable");

while(rs.next()) {
   System.out.println(rs.getInt(1));
}

System.out.println("Please update the table TESTTABLE...");
System.in.read();

rs = stmt.executeQuery("select * from testtable");

while(rs.next()) {
   System.out.println(rs.getInt(1));
}
}

}

-Original Message-
From: Heikki Tuuri [mailto:[EMAIL PROTECTED]]
Sent: 2002?10?8? 0:22
To: Mark Matthews
Cc: [EMAIL PROTECTED]
Subject: Re: transaction isolation level


Hi!

- Original Message -
From: "Mark Matthews" <[EMAIL PROTECTED]>
Newsgroups: mailing.database.mysql
Sent: Tuesday, October 08, 2002 5:07 AM
Subject: Re: transaction isolation level


> Jianliang Zhao wrote:
...
> >>I am connecting to MySql 3.23(innodb) with
> >>mysql-connector-java-2.0.14-bin.jar. I set the global transaction
> >>isolation level to READ COMMITTED. However, I still couldn't see the
> >>committed changes through JDBC client. Does anyone know about this
> >>issue?
...
> The isolation level of READ_COMMITTED has no effect currently in MySQL,
> unless you're using BDB tables...InnoDB runs either as REPEATABLE_READ
> or SERIALIZABLE, and converts everthing else to REPEATABLE_READ, because
> it can do REPATABLE_READ as fast (or faster) than most other DB's
> READ_COMMITTED.

I have to correct that BDB always runs at the SERIALIZABLE isolation level.

The default for InnoDB is REPEATABLE READ, and you can enhance it to
SERIALIZABLE with the

SET [GLOBAL | SESSION] TRANSACTION ISOLATION LEVEL
{ READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE }

SQL command. Since MyISAM uses table locking, we can say that MyISAM always
runs at the SERIALIZABLE level.

The command SHOW VARIABLES currently shows the MySQL default isolation level
as READ-COMMITTED, but it has no effect on any of the table handlers.

Version 4.0.5 will probably feature a new InnoDB isolation level READ
COMMITTED. I will probably at the same time change the MySQL default
isolation level to REPEATABLE READ. You can then lower the InnoDB isolation
level by the command

SET [GLOBAL | SESSION] TRANSACTION ISOLATION LEVEL ...

Users porting from Oracle and other databases will find READ COMMITTED a
useful level.

> -Mark

Best regards,

Heikki Tuuri
Innobase Oy
---
InnoDB - transactions, row level locking, and foreign key support for MySQL
See http://www.innodb.com, download MySQL-Max from http://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


-
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: transaction isolation level

[Heikki Tuuri]

Hi!

- Original Message -
From: "Mark Matthews" <[EMAIL PROTECTED]>
Newsgroups: mailing.database.mysql
Sent: Tuesday, October 08, 2002 5:07 AM
Subject: Re: transaction isolation level


> Jianliang Zhao wrote:
...
> >>I am connecting to MySql 3.23(innodb) with
> >>mysql-connector-java-2.0.14-bin.jar. I set the global transaction
> >>isolation level to READ COMMITTED. However, I still couldn't see the
> >>committed changes through JDBC client. Does anyone know about this
> >>issue?
...
> The isolation level of READ_COMMITTED has no effect currently in MySQL,
> unless you're using BDB tables...InnoDB runs either as REPEATABLE_READ
> or SERIALIZABLE, and converts everthing else to REPEATABLE_READ, because
> it can do REPATABLE_READ as fast (or faster) than most other DB's
> READ_COMMITTED.

I have to correct that BDB always runs at the SERIALIZABLE isolation level.

The default for InnoDB is REPEATABLE READ, and you can enhance it to
SERIALIZABLE with the

SET [GLOBAL | SESSION] TRANSACTION ISOLATION LEVEL
{ READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE }

SQL command. Since MyISAM uses table locking, we can say that MyISAM always
runs at the SERIALIZABLE level.

The command SHOW VARIABLES currently shows the MySQL default isolation level
as READ-COMMITTED, but it has no effect on any of the table handlers.

Version 4.0.5 will probably feature a new InnoDB isolation level READ
COMMITTED. I will probably at the same time change the MySQL default
isolation level to REPEATABLE READ. You can then lower the InnoDB isolation
level by the command

SET [GLOBAL | SESSION] TRANSACTION ISOLATION LEVEL ...

Users porting from Oracle and other databases will find READ COMMITTED a
useful level.

> -Mark

Best regards,

Heikki Tuuri
Innobase Oy
---
InnoDB - transactions, row level locking, and foreign key support for MySQL
See http://www.innodb.com, download MySQL-Max from http://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: transaction isolation level

[Mark Matthews]

Jianliang Zhao wrote:

>Thanks Jeremy. It turns out the JDBC SQL query tool(ViennaSQL) I am trying is causing 
>the problem. I couldn't reproduce the problem by writing a test case.
>
>Thanks,
>
>Jianliang
>
>-Original Message-
>From: Jeremy Zawodny [mailto:[EMAIL PROTECTED]]
>Sent: 2002?10?7? 17:36
>To: Jianliang Zhao
>Cc: [EMAIL PROTECTED]
>Subject: Re: transaction isolation level
>
>
>On Mon, Oct 07, 2002 at 05:30:57PM -0700, Jianliang Zhao wrote:
>  
>
>>Hi,
>>
>>I am connecting to MySql 3.23(innodb) with
>>mysql-connector-java-2.0.14-bin.jar. I set the global transaction
>>isolation level to READ COMMITTED. However, I still couldn't see the
>>committed changes through JDBC client. Does anyone know about this
>>issue?
>>
>>
>
>Can you provide a sample test case, maybe?
>  
>
The isolation level of READ_COMMITTED has no effect currently in MySQL, 
unless you're using BDB tables...InnoDB runs either as REPEATABLE_READ 
or SERIALIZABLE, and converts everthing else to REPEATABLE_READ, because 
it can do REPATABLE_READ as fast (or faster) than most other DB's 
READ_COMMITTED.

-Mark

-- 
For technical support contracts, visit https://order.mysql.com/?ref=mmma

__  ___ ___   __
   /  |/  /_ __/ __/ __ \/ /  Mark Matthews <[EMAIL PROTECTED]>
  / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer - JDBC/Java
 /_/  /_/\_, /___/\___\_\___/ Flossmoor (Chicago), IL USA
<___/ 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: transaction isolation level

[Jianliang Zhao]

Thanks Jeremy. It turns out the JDBC SQL query tool(ViennaSQL) I am trying is causing 
the problem. I couldn't reproduce the problem by writing a test case.

Thanks,

Jianliang

-Original Message-
From: Jeremy Zawodny [mailto:[EMAIL PROTECTED]]
Sent: 2002?10?7? 17:36
To: Jianliang Zhao
Cc: [EMAIL PROTECTED]
Subject: Re: transaction isolation level


On Mon, Oct 07, 2002 at 05:30:57PM -0700, Jianliang Zhao wrote:
> Hi,
> 
> I am connecting to MySql 3.23(innodb) with
> mysql-connector-java-2.0.14-bin.jar. I set the global transaction
> isolation level to READ COMMITTED. However, I still couldn't see the
> committed changes through JDBC client. Does anyone know about this
> issue?

Can you provide a sample test case, maybe?
-- 
Jeremy D. Zawodny |  Perl, Web, MySQL, Linux Magazine, Yahoo!
<[EMAIL PROTECTED]>  |  http://jeremy.zawodny.com/

MySQL 3.23.51: up 62 days, processed 1,334,315,423 queries (246/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: transaction isolation level

[Jeremy Zawodny]

On Mon, Oct 07, 2002 at 05:30:57PM -0700, Jianliang Zhao wrote:
> Hi,
> 
> I am connecting to MySql 3.23(innodb) with
> mysql-connector-java-2.0.14-bin.jar. I set the global transaction
> isolation level to READ COMMITTED. However, I still couldn't see the
> committed changes through JDBC client. Does anyone know about this
> issue?

Can you provide a sample test case, maybe?
-- 
Jeremy D. Zawodny |  Perl, Web, MySQL, Linux Magazine, Yahoo!
<[EMAIL PROTECTED]>  |  http://jeremy.zawodny.com/

MySQL 3.23.51: up 62 days, processed 1,334,315,423 queries (246/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: Transaction Question CONFUSED

[Tod Harter]

On Tuesday 20 August 2002 01:26 pm, Randy Johnson wrote:
> I am confused. (innodb table type)

I'm really not so sure about the 'lock in share mode' thing, but to the best 
of my knowledge if you do a

SET TRANSACTION_ISOLATION_LEVEL=SERIALIZABLE

and then start a transaction where you read data from a row and then update 
it, there is an absolute guarantee (if the database properly honors the 
isolation level) that no two transactions can act in such a fashion that 
either one interferes with the other. In practical terms that means that the 
same code run from client 2 will block as soon as it attempts the read until 
transaction started in client 1 is 100% complete.

Now, there may be more efficient ways to get this result. InnoDB uses 
multi-versioning and that has some subtle effects on transactions and 
concurrency. 

A larger question however is this, why do you care about reading the old 
balance? If you aren't going to use it to calculate the new one, then its 
irrelevant...

In other words the scenario you outline reduces (as far as the db is 
concerned) to just

update table set balance=100

and since that is an atomic operation it requires no transaction. In fact in 
theory ACID never requires a transaction for any operation involving only one 
single row.  For instance if you were incrementing the balance by 100 it 
would STILL be an atomic operation

update table set balance=balance+100

It is in fact only when you get to multi-row or multi-table situations where 
transactions are required.

Consider again your example, since no matter what order the 2 operations are 
performed in the resut is the same (balance is 100) there is no point in 
caring what sequence occurs, esp since script 1 cannot care if script 2 ever 
runs or not, and vice versa (or else they'd be one script...).

You can satisfy yourself that the same is true for increment, decrement, or 
ANY other single-row scenario that can possibly be invented. This is in fact 
a theorem of transactions...

Why then were transactions invented? Suppose you had THREE rows you needed to 
update with a single update statement

update table set balance = balance=1 where id =1 or id = 2 or id = 3

NOW you might need a transaction, because it might be a really bad idea for 
script 2 to come along and do 

select balance from table where id =1 or id = 2 or id = 3

and end up with the incremented balance for row 1, and the unincremented 
balances for rows 2 and 3, which is quite possible.

In that case running the 1st query in a transaction would in fact be quite 
necessary. 

Now you know what keeps db design guys up late at night
>
> Client 1.
> starts transaction
> selects balance from table where id=1 for update
> update table set balance=100
> #At this point from what i have read the balance could be selected by
> anther user.
>
> committ
>
>
>
> I have read that a select balance from table where id=1 lock in share mode
> will wait for the committ statement,  but client 2 would be wanting to
> update the balance the same way client 1 does sO i do not see how client 2
> could use the lock in share mode because the script is the same for client
> 2 as it is in client one.
>
> so how would i ensure that client 2 waits for client 1 to committ before
> processing their select  and update?
>
>
>
> Randy
>
> sql,quary
>
>
>
> -
> 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: Re: Transaction Question

[Victoria Reznichenko]

Randy,
Monday, August 19, 2002, 7:29:43 PM, you wrote:

RJ> Thanks for your reply,  is it documented somewhere?

That COMMIT and ROLLBACK release locks? Of course, look at:
 http://www.mysql.com/doc/en/InnoDB_transaction_model.html




-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
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: Transaction Question

[Victoria Reznichenko]

Randy,
Tuesday, August 20, 2002, 8:51:36 AM, you wrote:

RJ> Here is an insert from the innodb reference manual

[skip]

RJ> The solution is to perform the SELECT in a locking mode, LOCK IN SHARE MODE.

RJ> SELECT * FROM PARENT WHERE NAME = 'Jones' LOCK IN SHARE MODE;

[skip]

RJ> This means that if we update a record and have not committed it and another
RJ> user uses the above select statement, it will wait until the first update
RJ> statement is committed before reading

Yes.




-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
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: Transaction Question

[Randy Johnson]

Thanks for your reply,  is it documented somewhere?

Randy


- Original Message -
From: "Victoria Reznichenko" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Monday, August 19, 2002 10:07 AM
Subject: Re: Transaction Question


> Randy,
> Sunday, August 18, 2002, 10:57:54 AM, you wrote:
>
> RJ>  I am using innodb tables via mysql max
>
> RJ>  if i do a select * for update
> RJ>  and then do an update statement
>
> RJ>  will this unlock the record for another update or will it wait for me
to do
> RJ>  a commit?
>
> It will wait for COMMIT/ROLLBACK.
>
>
>
>
> --
> For technical support contracts, goto https://order.mysql.com/?ref=ensita
> 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
>



-
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: Transaction Question

[Victoria Reznichenko]

Randy,
Sunday, August 18, 2002, 10:57:54 AM, you wrote:

RJ>  I am using innodb tables via mysql max

RJ>  if i do a select * for update
RJ>  and then do an update statement

RJ>  will this unlock the record for another update or will it wait for me to do
RJ>  a commit?

It will wait for COMMIT/ROLLBACK.




-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
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: Transaction isolation

[Ryan Hatch]

are you familiar with the different MySQL table types?

they are:
MyISAM
BDB
Gemini
InnoDB

but InnoDB is the most robust, supports row-level locking, ACID transactions (no dirty 
reads), and foreign key restraints... read up on it here:  http://www.innodb.com   
but InnoDB does multi-versioning, so be sure to read carefully

Ki Mien wrote:

> Hi all,
>
> We need to prevent Dirty Reading.
>
> How can we perform transaction isolation in mySQL? Are the following: 
>TRANSACTION_SERIALIZABLE, TRANSACTION_REPEATABLE_READ, TRANSACTION_READ_COMMITTED, 
>TRANSACTION_READ_UNCOMMITTED, TRANSACTION_NONE supported by MySQL? Or has anyone 
>tried any other alternatives?
>
> cheers,
> Ki Mien
>
> 
> Take part in the Penning on Squares Chinese Essay Writing Contest.
> Submit your masterpieces or vote for your favourite works, and win attractive prizes.
> Be there or be SQUARE!
> http://sea.litcontest.lycosasia.com
>
> cOntact @ Lycos 
> = 20MB for email and filestore + lots of other goodies...
>
> -
> 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: transaction with MySQL

[Roger Baklund]

* Andrew Chan
> Hi there, correct me if I am wrong.  Only verion 4.x of MySQL supports
> transaction.

You are wrong. :)

3.23.*-max binaries support BDB and InnoDB tables, both support
transactions.

http://www.mysql.com/doc/B/D/BDB.html >
http://www.mysql.com/doc/I/n/InnoDB.html >

--
Roger


-
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: transaction with MySQL

[Craig Vincent]

> Hi there, correct me if I am wrong.  Only verion 4.x of MySQL supports
> transaction.

If I remember correctly, BDB has transaction support and was released during
3.23 development.

Sincerely,

Craig Vincent



-
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: transaction with MySQL

[Heikki Tuuri]

Andrew,

MySQL-Max-3.23 also supports transactions, and that is a stable release.

Best regards,

Heikki Tuuri
Innobase Oy
---
InnoDB - transactions, row level locking, and foreign key support for MySQL
See http://www.innodb.com, download MySQL-Max from http://www.mysql.com

- Original Message -
From: ""Andrew Chan"" <[EMAIL PROTECTED]>
Newsgroups: mailing.database.mysql
Sent: Wednesday, April 17, 2002 8:57 PM
Subject: transaction with MySQL


> Hi there, correct me if I am wrong.  Only verion 4.x of MySQL supports
> transaction.
>
> Thanks.
>
> Andrew
>
>
> _
> Do You Yahoo!?
> Get your free @yahoo.com address at http://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: transaction

[BICHOT Guilhem 172613]

Yes MySQL supports transaction.
But you have to use certain table types : for example InnoDB or BDB (and not
MyISAM).
I use InnoDB ; read the following section of the manual :
http://www.mysql.com/documentation/mysql/bychapter/manual_Table_types.html#I
nnoDB
it will tell you which options you may have to set in your my.cnf or my.ini
file.
After that, you will have BEGIN/COMMIT/ROLLBACK, automatic crash recovery,
etc.

Guilhem

-Message d'origine-
De : Felik Harmanto [mailto:[EMAIL PROTECTED]]
Envoye : mardi 5 fevrier 2002 12:10
A : [EMAIL PROTECTED]
Objet : transaction


hi,

is mysql support transaction?
how to setup it?
please help

thx

felik

__
Do You Yahoo!?
Send FREE Valentine eCards with Yahoo! Greetings!
http://greetings.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: Transaction Support in MySQL

[Rajarajeswaraprabhu]

Hi Arawind,

> Could any one help how to cope up without transactons
> in mysql. The Help manual provided with the software
> doesn't help a great deal.

  Oneway is to incorporte transaction processing functionalities in the
middle layer application. eg. before commiting persistent objects used for
backend database from mysql. Or if you are using php scripts, then modify
the scripts for transaction functionalities before commiting the changes
into mysql.

-- Prabhu

> Regards
> Aravind
> 
> 
> For Stock Quotes, Finance News, Insurance, Tax Planners, Mutual Funds...
> Visit http://in.finance.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
> 

---
 Beauty is bought by judgement of the eye. 
-- Shakespeare
---
Prabhu SR.
Sankhya Technologies Private Limited, Chennai-34.
---


-
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: Transaction help

[Heikki Tuuri]

Luca,

>Hi, I need transaction in my db. I installed Mysql max and I'm usind
>InnoDb tables.
>When an error occurred during the transaction the mysql continue with
>the execution. Do I need to "manually" check every query result to
>know if query get ok and then in case of error make a rollback?
>I thought simply transaction sends an error and rollback automatically
>on query error.
>Thanks

ANSI specifies that at an SQL error only the current
SQL statement is rolled back, not the whole transaction.

An exception in MySQL is that a duplicate key error
currently only rolls back the insert of the current row.
This has relevance only in complex statements of type:
INSERT INTO ... SELECT ...

Another exception in InnoDB is that a transaction deadlock
or a lock wait timeout causes the whole transaction to be
rolled back, not just the SQL statement.

So, your application has to check the return status of
every SQL statement, and decide what to do in case of
an error: rollback, commit, or something else.

Regards,

Heikki
http://www.innodb.com/ibman.html



-
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: Transaction Support

[Jeremy Zawodny]

On Mon, Oct 22, 2001 at 11:14:58AM -0400, Victor wrote:
> Hello
> 
> As I understand it, MySQL with InnoDB or BDB support can support
> transactions. Is there a doc on the shortcomings of these tables and
> which one is better? MySQL documentation has a shortcomings page for
> InnoDB (but couldn't find one for BDB)
> 
> http://www.mysql.com/doc/I/n/InnoDB_restrictions.html
> 
> Any suggestions with which type to go with? What have been the
> experience with either/both? How does the future look for both?

The future looks very bright for InnoDB.  It is faster and scales
better than BDB, and it uses more granular locks.

> Both maintain log files to do rollbacks. Is this how PGSQL does this
> too?

InnoDB uses a mult-versioning scheme, which is what PostreSQL does.
So in that respect they're quite similar.

> How do these tables compare to dbs with "native" transaction support
> (like Oracle, MSSQL, and PostgreSQL) ?

InnoDB was largely modeled after Oracle.

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.41-max: up 46 days, processed 1,018,046,439 queries (253/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: Transaction Counter flawed

[Paul DuBois]

At 11:37 AM -0400 9/20/01, Kevin Slean wrote:
>I have come across a flawed transaction counter in a mysql application that
>I now support.  I believe it is flawed because it does not use record
>locking to deal with concurrency and was hoping someone could give me ideas
>on how to correct.
>
>The transaction counter works like this:
>
>1) Web transaction starts.
>2) Transaction queries counter value stored in mysql table.
>3) Transaction increments counter in its own address space.
>4) Transaction applies update to original counter with newly incremented
>value.
>5) Web transaction ends.
>
>Since many of these transactions run concurrently (over 100 at the same
>time), and steps 2, 3, and 4 are not atomic, they are probably stepping on
>each other's results.
>
>Any ideas are appreciated.

UPDATE tbl_name SET counter = LAST_INSERT_ID(counter+1)

SELECT LAST_INSERT_ID()

This does steps 2-4 in such a way that clients won't interfere with
each other.  The second query returns the value that you updated
the counter to, regardless of whether other clients have changed it
in the meantime.

>
>Kevin


-- 
Paul DuBois, [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: transaction tables

[Carl Troein]

Matthew Harting writes:

>  Which table type would you
> recommend using for transaction support?

InnoDB, unless you have a small database and plenty of RAM.
At least that's what others who have actually tried it have
said. Since what I'm doing isn't really in need of transactions
I'm sticking with MyISAM for now.

//C

-- 
 Carl Troein - Círdan / Istari-PixelMagic - UIN 16353280
 [EMAIL PROTECTED] | http://pixelmagic.dyndns.org/~cirdan/
 Amiga user since '89, and damned proud of it too.


-
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: Transaction safe tables for Mac OS X

[Grigory Bakunov]

Date |Mon, 6 Aug 2001 13:44:28 +0200
>From |Willy Faes <[EMAIL PROTECTED]>

Hello!

WF> Hi,

WF> Are there any transaction safe tables for Mac OS X you can use?

WF> Thanks,

WF> Willy Faes

You must use innodb or bdb tables for transaction safe.
Both may normaly compilled on Mac OS

___
For technical support contracts, visit https://order.mysql.com/
This email is sponsored by SWSoft, http://www.asplinux.ru/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Grigory Bakunov <[EMAIL PROTECTED]>
 / /|_/ / // /\ \/ /_/ / /__   MySQL AB / SWSoft
/_/  /_/\_, /___/\___\_\___/
   <___/   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: Transaction safe tables for Mac OS X

[Heikki Tuuri]

Willy,

people have written quite a lot of compiling MySQL on
OS X. It should be possible. Below is an ad of a CD
which contains the OS X version. Obviously the
producers of the CD have been able to compile.

Regards,

Heikki
http://www.innodb.com


Copied message:

Hi,

Are there any transaction safe tables for Mac OS X you can use?

Thanks,

Willy Faes

> "OpenBase 1.0 includes full installation and configuration of MySQL
3.23.39,
> unixODBC 2.0.7, BerkeleyDB and InnoDB for Mac OS X. For MySQL
> administration, the CD will optionally install and configure Apache Web
> Server, PHP 4.0.6 and phpMyAdmin 2.2.0rc1. "
>
> And the CD is only $30.  Details at
> http://www.openosx.com/openbase/index.html.


-
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: Transaction for MySQL

[Heikki Tuuri]

Hi!

You can change the MySQL server program from
mysqld-nt.exe or any other mysqld*.exe to
mysqld-max.exe or mysqld-max-nt.exe (on NT and 2000)
without changing your MyISAM tables in any way.

The MyISAM part in these versions is identical.

To use InnoDB tables you have to add startup
parameters to your my.cnf or my.ini, to the
[mysqld] section. Look at the InnoDB manual
http://www.mysql.com/doc/I/n/InnoDB_start.html
for advice on how to specify them.

Regards,

Heikki Tuuri
Innobase Oy

Copied message:

On Sat, Jul 21, 2001 at 03:46:58AM -0700, Michael Tam wrote:
> Hi Jeremy,
>
> In question 2),  what I meant is not uninstall MySql from Windows 2000
> but the service of it by calling "mysqld-nt --remove".  That will take off
> the service of the mysql-nt from being the default mysql daemon and then
I'd
> call "mysqld-max --install" to establish the max version of sqld which
> supports the BDB and InnoDB
>
> This, then, leads to question 3), if I switch the daemon as mentioned
in
> 2) then would the tables created under "mysqld-nt" will remain intact as
> "mysql-max" takes over??

I believe so, yes.  But it'd be safest to first back them up.  It
can't hurt to be safe.

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 35 days, processed 270,286,701 queries (89/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: Transaction for MySQL

[Jeremy Zawodny]

On Sat, Jul 21, 2001 at 03:46:58AM -0700, Michael Tam wrote:
> Hi Jeremy,
> 
> In question 2),  what I meant is not uninstall MySql from Windows 2000
> but the service of it by calling "mysqld-nt --remove".  That will take off
> the service of the mysql-nt from being the default mysql daemon and then I'd
> call "mysqld-max --install" to establish the max version of sqld which
> supports the BDB and InnoDB.
> 
> This, then, leads to question 3), if I switch the daemon as mentioned in
> 2) then would the tables created under "mysqld-nt" will remain intact as
> "mysql-max" takes over??

I believe so, yes.  But it'd be safest to first back them up.  It
can't hurt to be safe.

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 35 days, processed 270,286,701 queries (89/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: Transaction for MySQL

[Michael Tam]

Hi Jeremy,

Thank you for your valuable info.

However, may be you can fill me with some more detail here

In question 2),  what I meant is not uninstall MySql from Windows 2000
but the service of it by calling "mysqld-nt --remove".  That will take off
the service of the mysql-nt from being the default mysql daemon and then I'd
call "mysqld-max --install" to establish the max version of sqld which
supports the BDB and InnoDB.

This, then, leads to question 3), if I switch the daemon as mentioned in
2) then would the tables created under "mysqld-nt" will remain intact as
"mysql-max" takes over??

Thank you very much.

Regards,
Michael



- Original Message -
From: "Jeremy Zawodny" <[EMAIL PROTECTED]>
To: "Michael Tam" <[EMAIL PROTECTED]>
Cc: "mysql" <[EMAIL PROTECTED]>
Sent: Saturday, July 21, 2001 2:57 AM
Subject: Re: Transaction for MySQL


> On Wed, Jul 18, 2001 at 02:06:03PM -0700, Michael Tam wrote:
> > Hi all,
> >
> >  I am new using MySQL.  I found out that the default table type used
> >  in the binary isn't support transaction and would like to make
> >  MySQL uses another table type which supports transaction.  The
> >  following are my questions regarding the issue:
> >
> > 1) which one should I use InnoDB or BDB?  what kind of advantages
> >given by each type?
>
> BDB tables have been around in MySQL longer.  They provide page-level
> locking.
>
> InnoDB tables are newer but have row-level locking and are faster than
> BDB tables.
>
> > 2) currently, I am using the default type - MyISAM, where I am
> > running MySqld-nt.  If I would like to switch to another table type.
> > Do I just uninstall the service of MySqld-nt from win2000 and
> > install MySqld-Max service??
>
> Hard to know.  I've never installed or uninstalled MySQL on Windows.
> Does the uninstall remove the data files?
>
> > 3 what would happen to my existing MyISAM type tables which already
> > exists in MySQL if I do the switch over? and would I able to
> > use/create MyISAM type table uder MySqld-Max??
>
> You can convert any MyISAM tables to InnoDB that you'd like.  It's
> just a matter of:
>
>   ALTER TABLE  TYPE = InnoDB;
>
> for each one of them.  Or you can use the mysql_convert_table_format
> script that comes with MySQL.
>
> 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 35 days, processed 269,914,242 queries (89/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
>
>

-
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: Transaction for MySQL

[Jeremy Zawodny]

On Wed, Jul 18, 2001 at 02:06:03PM -0700, Michael Tam wrote:
> Hi all,
> 
>  I am new using MySQL.  I found out that the default table type used
>  in the binary isn't support transaction and would like to make
>  MySQL uses another table type which supports transaction.  The
>  following are my questions regarding the issue:
> 
> 1) which one should I use InnoDB or BDB?  what kind of advantages
>given by each type?

BDB tables have been around in MySQL longer.  They provide page-level
locking.

InnoDB tables are newer but have row-level locking and are faster than
BDB tables.

> 2) currently, I am using the default type - MyISAM, where I am
> running MySqld-nt.  If I would like to switch to another table type.
> Do I just uninstall the service of MySqld-nt from win2000 and
> install MySqld-Max service??

Hard to know.  I've never installed or uninstalled MySQL on Windows.
Does the uninstall remove the data files?

> 3 what would happen to my existing MyISAM type tables which already
> exists in MySQL if I do the switch over? and would I able to
> use/create MyISAM type table uder MySqld-Max??

You can convert any MyISAM tables to InnoDB that you'd like.  It's
just a matter of:

  ALTER TABLE  TYPE = InnoDB;

for each one of them.  Or you can use the mysql_convert_table_format
script that comes with MySQL.

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 35 days, processed 269,914,242 queries (89/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: Transaction for MySQL

[Ilya Martynov]

MT> 1) which one should I use InnoDB or BDB?  what kind of advantages
MT> given by each type?

I'm not expert but AFAIK BDB databases doesn't work well (i.e. fast)
if they don't fit fully in RAM. Another important thing: InnoDB
provides row-level locking while BDB only page-level locking. Thus it
can be a limitation for some applications.

-- 
 -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
| Ilya Martynov (http://martynov.org/)|
| GnuPG 1024D/323BDEE6 D7F7 561E 4C1D 8A15 8E80  E4AE BE1A 53EB 323B DEE6 |
| AGAVA Software Company (http://www.agava.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: Transaction for MySQL

[alec . cawley]

>Would someone help me out with these questions?
>
>I know these may be something obvious/ in the manual .. etc but I
would
> like to have someone showing me if I am on the right track and maybe
share
> some of these in greater detail.  Greatly appreciated.


Since the experts are too busy, I'll put in my $0.02 - though I am NOT an
expert

>
> I am new using MySQL.  I found out that the default table type used
in
> the binary isn't support transaction and would like to make MySQL uses
> another table type which supports transaction.  The following are my
> questions regarding the issue:
>
> 1) which one should I use InnoDB or BDB?  what kind of advantages given
by
> each type?
>
> 2) currently, I am using the default type - MyISAM, where I am running
> MySqld-nt.  If I would like to switch to another
> table type.  Do I just uninstall the service of MySqld-nt from
win2000
> and install MySqld-Max service??

Yes - the Max server support sevral table types at the same time.

> In addition to the daemond, I need to comment out the MyISAM config
>
> 3) what would happen to my existing MyISAM type tables which already
exists
> in MySQL if I do the switch over? and
>   would I able to use/create MyISAM type table uder MySqld-Max??

The existing tables remain as they are, and will be handled in the same
way. The default remains MyISAM.
However, you can create new tables using different table types, and alter
your existing tables using the
ALTER TABLE command.

 Alec Cawley





-
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: Transaction for MySQL

[Michael Tam]

Hi everyone,

Would someone help me out with these questions?

I know these may be something obvious/ in the manual .. etc but I would
like to have someone showing me if I am on the right track and maybe share
some of these in greater detail.  Greatly appreciated.


Regards,
Michael

- Original Message -
From: "Michael Tam" <[EMAIL PROTECTED]>
To: "mysql" <[EMAIL PROTECTED]>
Sent: Wednesday, July 18, 2001 2:06 PM
Subject: Transaction for MySQL


Hi all,

I am new using MySQL.  I found out that the default table type used in
the binary isn't support transaction and would like to make MySQL uses
another table type which supports transaction.  The following are my
questions regarding the issue:

1) which one should I use InnoDB or BDB?  what kind of advantages given by
each type?

2) currently, I am using the default type - MyISAM, where I am running
MySqld-nt.  If I would like to switch to another
table type.  Do I just uninstall the service of MySqld-nt from win2000
and install MySqld-Max service??
In addition to the daemond, I need to comment out the MyISAM config

3) what would happen to my existing MyISAM type tables which already exists
in MySQL if I do the switch over? and
   would I able to use/create MyISAM type table uder MySqld-Max??

Thank you and greatly appreciated for any suggestion/help.

Regards,
Michael


-
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