Re: Are transactions atomic?
Hi Douglas,
Douglas Pearson wrote:
Sorry if this is a dumb question, but are SQL transactions in MySQL atomic?
I'm using InnoDB tables in MySQL5.
Clearly, transactions have the property that either all updates occur or
none do. By atomic, I mean are other queries guaranteed to either see all
changes from the transaction or none of them?
You're actually asking about isolation -- the I in ACID. yes, MySQL
transactions are ACID. And MySQL and InnoDB implement all four levels
of transaction isolation described by the SQL standard. Most RDBMSs
don't, so MySQL is actually a good bit more complicated to use correctly
with respect to transactions and isolation levels.
An obvious example of the potential problem if they are not atomic (with
regards to visibility):
Transaction {
Query1 - update credits_table with +$20
Query2 - update debits_table with -$20
}
Separate process running periodically:
Query3 - compute total credits and debits
Could query3 ever see the total of credits and debits as being out of
balance?
Yes, if you choose the wrong isolation level.
If the transaction doesn't guarantee that, is there any other way to force
atomic visibility?
Choosing the right isolation level will do what you need. It will not
guarantee no one can ever see inconsistent data, so if you're trying to
prevent people from being able to, you can't. If you're trying to
design applications so they WON'T, you can do that.
You should read the section on InnoDB Transaction Model and Locking,
especially this section (but read the whole thing, it is very complex):
http://dev.mysql.com/doc/refman/5.0/en/innodb-transaction-isolation.html
Baron
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Are transactions atomic?
Sorry if this is a dumb question, but are SQL transactions in MySQL atomic?
I'm using InnoDB tables in MySQL5.
Clearly, transactions have the property that either all updates occur or
none do. By atomic, I mean are other queries guaranteed to either see all
changes from the transaction or none of them?
An obvious example of the potential problem if they are not atomic (with
regards to visibility):
Transaction {
Query1 - update credits_table with +$20
Query2 - update debits_table with -$20
}
Separate process running periodically:
Query3 - compute total credits and debits
Could query3 ever see the total of credits and debits as being out of
balance?
If the transaction doesn't guarantee that, is there any other way to force
atomic visibility?
Doug
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SV: Help with query...
> Hello > where is the FROM for f1 table? > Martin-- Why would there be any "FROM" for the f1 table? It's not needed in the result. Anyway, I've come up with a query that actually seems to do what I'm looking for; SELECT a1.username FROM accountuser AS a1 LEFT JOIN payments AS p1 ON (a1.username = p1.username) LEFT JOIN freeaccounts AS f1 ON (a1.username = f1.username) WHERE a1.username LIKE 'cit%' AND a1.imp + a1.pp + a1.se + a1.auth != 0 AND (p1.validdate < UNIX_TIMESTAMP(NOW()) OR p1.validdate IS NULL) AND (f1.free = false OR f1.free IS NULL) Thanks for the hints, you who gave them.. ;) Anders. > - Original Message - > From: "Anders Norrbring" <[EMAIL PROTECTED]> > To: > Sent: Sunday, October 14, 2007 7:13 AM > Subject: Help with query... > > > > I'm trying to set up a query, but I don't really get the result I > > expected, so can someone please help me out here? > > > > The query I've built is this: > > > > SELECT a1.username FROM accountuser AS a1 > > LEFT JOIN (freeaccounts AS f1, payments AS p1) > > ON (a1.username = p1.username > > AND p1.username = f1.username) > > WHERE a1.username LIKE 'cit%' > > AND a1.imp + a1.pp + a1.se + a1.auth != 0 > > AND (f1.free IS NULL OR f1.free = false) > > AND (p1.validdate < UNIX_TIMESTAMP(NOW()) OR p1.validdate IS NULL) > > > > The accountuser table is always fully populated. > > The freeaccounts and payments tables are only occupied with the > > 'username' field if it's been used previously, so they may not > contain > > any data. > > > > What I expect to get from the query is 'username' from the > accountuser > > table when: > > > > The a1.username starts with 'cit', > > AND > > Any of a1.imp, a1.pp, a1.se or a1.auth is not 0, > > AND > > f1.free is either not populated or false. > > AND > > p1.validdate is either not populated, or the timestamp is before NOW. > > > > Everything seems to work except for the passed time check. If I set > > p1.validdate to a timestamp for something next week, the username is > > still returned in the result. > > > > Grateful for any hints... > > Anders. > > > > > > -- > > 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: DOCUMENTATION ABOUT SHOW PROFILE
Wagner, SHOW PROFILE[S] is documented at http://dev.mysql.com/doc/refman/5.0/en/show-profiles.html. 5.0 Community edition only. Perhaps it will appear in 5.1 Community? They do not say so. PB Wagner Bianchi wrote: Hi friends, Somebody here in this list have or knows where i get or read about the PROFILE of MySQL (SHOW PROFILE) ? I see this article of Schummi, but is most superficial. (http://dev.mysql.com/tech-resources/articles/using-new-query-profiler.html) Can u help me? Thk's 4 all. Wagner Bianchi Diretor de Tecnologia - INFODBA Technologies & Consulting [EMAIL PROTECTED] - (31) 3272 - 0226 / 8427 - 8803 Abra sua conta no Yahoo! Mail, o único sem limite de espaço para armazenamento! http://br.mail.yahoo.com/ No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.5.488 / Virus Database: 269.14.9/1068 - Release Date: 10/13/2007 10:15 AM
DOCUMENTATION ABOUT SHOW PROFILE
Hi friends, Somebody here in this list have or knows where i get or read about the PROFILE of MySQL (SHOW PROFILE) ? I see this article of Schummi, but is most superficial. (http://dev.mysql.com/tech-resources/articles/using-new-query-profiler.html) Can u help me? Thk's 4 all. Wagner Bianchi Diretor de Tecnologia - INFODBA Technologies & Consulting [EMAIL PROTECTED] - (31) 3272 - 0226 / 8427 - 8803 Abra sua conta no Yahoo! Mail, o único sem limite de espaço para armazenamento! http://br.mail.yahoo.com/
Re: Help with query...
Anders Norrbring wrote: I'm trying to set up a query, but I don't really get the result I expected, so can someone please help me out here? The query I've built is this: SELECT a1.username FROM accountuser AS a1 LEFT JOIN (freeaccounts AS f1, payments AS p1) ON (a1.username = p1.username AND p1.username = f1.username) WHERE a1.username LIKE 'cit%' AND a1.imp + a1.pp + a1.se + a1.auth != 0 AND (f1.free IS NULL OR f1.free = false) AND (p1.validdate < UNIX_TIMESTAMP(NOW()) OR p1.validdate IS NULL) The accountuser table is always fully populated. The freeaccounts and payments tables are only occupied with the 'username' field if it's been used previously, so they may not contain any data. What I expect to get from the query is 'username' from the accountuser table when: The a1.username starts with 'cit', AND Any of a1.imp, a1.pp, a1.se or a1.auth is not 0, AND f1.free is either not populated or false. AND p1.validdate is either not populated, or the timestamp is before NOW. Everything seems to work except for the passed time check. If I set p1.validdate to a timestamp for something next week, the username is still returned in the result. Put the last line (the tests on p1.validdate) in the JOIN condition instead. brian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Help with query...
I'm trying to set up a query, but I don't really get the result I expected, so can someone please help me out here? The query I've built is this: SELECT a1.username FROM accountuser AS a1 LEFT JOIN (freeaccounts AS f1, payments AS p1) ON (a1.username = p1.username AND p1.username = f1.username) WHERE a1.username LIKE 'cit%' AND a1.imp + a1.pp + a1.se + a1.auth != 0 AND (f1.free IS NULL OR f1.free = false) AND (p1.validdate < UNIX_TIMESTAMP(NOW()) OR p1.validdate IS NULL) The accountuser table is always fully populated. The freeaccounts and payments tables are only occupied with the 'username' field if it's been used previously, so they may not contain any data. What I expect to get from the query is 'username' from the accountuser table when: The a1.username starts with 'cit', AND Any of a1.imp, a1.pp, a1.se or a1.auth is not 0, AND f1.free is either not populated or false. AND p1.validdate is either not populated, or the timestamp is before NOW. Everything seems to work except for the passed time check. If I set p1.validdate to a timestamp for something next week, the username is still returned in the result. Grateful for any hints... Anders. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
