MYSQL :: Transactions And Race Conditions

Database Access Race Condition
I am working on a website with two applications on the server - both accessing the MySql database. PHP applications are reading and writing to the database and a Java servlet (running under Tomcat) is also accessing the database.

Currently the software in both applications is under development so there is very little in the way of actual database accesses, however, someone has suggested that we will have problems in the operational system because both Java and PHP applications will be attempting to access the same database.

Where Conditions
I am using MySQL phpmyadmin on my server. I would like some examples of different conditional codes to retrieve info from my tables.

LIKE Conditions
Basically I have a table, with a column which holds a list of related tags separated by spaces. The user can search for a row using a key word that will search this column (and the column named header too) and return if the keyword appears in the tags (or heading)... I tried to achieve this by using the following SQL but it actually returns every entry in the database... I have never used "LIKE" before so if someone could give me a slap and say I am doing something stupid that would be awesome .

SELECT * FROM VIDEOS WHERE VIDEO_HEADER LIKE '%$seachText%' OR VIDEO_TAGS LIKE '%$searchText%'

WHERE Conditions
I'm trying to optimize a search and I'm wondering if anybody knew whether the order of the conditions in the WHERE clause actually matters. Is it parsed from left to right? If so, then should I put the conditions likely to eliminate the most records first, followed by those that will eliminate the least?
Also - I need to join several tables for this search (1 master table that contains user info and about 7 secondary tables that include additional information on users) and I am trying to decide whether I should do the joins one at a time and eliminate users in a series of steps (to prevent getting an overly large table), or if I should evaluate and combine everything in a single statement. The problem is I don't really know what the parsing order of the SELECT statement is.

2 Of 5 Conditions Match
I need my query to match at least 2 of 5 given conditions.

Example:
I have conditions
content REGEXP 'A'
content REGEXP 'B'
content REGEXP 'C'
content REGEXP 'D'
content REGEXP 'E'

I need database to return this entry only if at least 2 conditons are true.

Need Help With Multiple WHERE Conditions...
i'm wondering if anyone knows how to have multiple WHERE conditions...i'm using php/mysql as my programing languages...

example --> SELECT * FROM table WHERE rid=$ride && uid=$userid && accepted=Ɔ'

I need help selecting rows where all three of the conditions above are met...anyone knows how to do this?

MySQL 5.0 And OR Conditions
I have heard that version 5.0 of MySQL has performance gains for "OR" conditions. Can anyone confirm this? If so, how significant are the performance gains over 4.1?

For instance, I am referring to something like: SELECT * FROM some_table WHERE this_thing = 'something' OR that_thing = 'whatever';

Search On Several Conditions
I want to retreive data from a table on several conditions in order; for example;

All i am trying to do is select all records on the following condition;

User_id = variable AND (Profile = "variable" OR Profile = "Both") ORDER by hits in DESC

I came up with this statement but no matter what i do i cannot get it to produce the result above

"SELECT * FROM links WHERE user_id='$user_id' AND (profile='$mycurrentprofile' OR profile='Both') GROUP BY total_hits DESC"

Can anyone offer me any help please, would really appreciate this as its doing my head in.

BTW, the following statement seems to work so not sure whats going on ? Only thing i can think is that its screwing up if total_hits above is equal 0 ?

"SELECT * FROM links WHERE folder_id='$activefolder' AND (profile='$mycurrentprofile' OR profile='Both') GROUP BY title ASC"

Sums For Two Different Conditions
I want to combine the following 2 queries into 1 query where it would give me 2 results (proflad and proftot) but don't know how

select sum(return-inv)as proflad
from allbets where com="lad"

select sum(return-inv)as proftot
from allbets where com="tot"

Two Alike Conditions In Query?
Example. I have a table with information about payments (id, amount, pay_date, customer_id). Can I select two sums for two different date ranges, grouped by 'customer_id' (for each customer) in one query?

Left Join Conditions
Two tables.

table1, table2

Table 1 contains all unique items.
Table 2 contains x references to table 1, unique by customerId

I want to do a join onto table2 where table1.id = table2.table1Id AND table2.customerId = the user's ID

I can't quite figure out how to work in the idea of an AND into my left join. Unless both those conditions are two, I don't want any data from table2.

Only solution I can think of so far is to just left join on the id's, but select the customerId from table2, and as I loop through, check to see if it's supplied and the right id, and keep track of duplicate items from multiple customers myself.

Suggestions?

Conditions On Left Join
I'm having trouble constructing this left join query. I want to retrieve all rows from tProducts, and relevant matches from pictures, however what I have got just jams up the server and I have to restart. Am I doing it wrong?

SELECT prodCode, prodName
FROM tProducts LEFT JOIN pictures
ON productID = relProdId
AND prodCode = 'SF2'

Select With Multiple Conditions
I've a table called tag_log.

In this table are two columns called tag_id and sub_id. This table is used to link tag names to stories.

I'm trying to write the query for a search. The search is looking for all the sub_id that matches the tag_id given.

Example of what I have currently:
select distinct(sub_id) from tag_log where tag_id in(101, 102, 103);

In the above example, it was performing a union instead of what I really want. It was returning results that have either one or all of the tags.

What's required is for the story to have ALL the three tags. It's should be like a typical search, where the more search terms you enter, the more focused the results will be.

Conditions In UPDATE Statement
i have some data to be updated in a specific row - WHERE id=1

data:
$min_time, $max_time, $time, $attempts

so, i would like to calculate new avg and replace min_time with new value, if new min_time is smaller then that one stored in the database.

"UPDATE mytable SET
avg=(avg*attempts+$time) DEV (attempts+1),
min_time=**here i could use help** ??IF(min_time>$min_time)$min_time ELSE min_time??
**and similar with max_time .

Query Performance On Column Conditions
I have a question regarding MySQL performance on column conditions. How MySQL reacts where you add a condition to the same column?

For example:

SELECT * FROM users WHERE status<>4
and the same query with extended column check
SELECT * FROM users WHERE status<>4 AND status<>5 ...

I am specially interested in situation where the conditions checks a column value and whether the data is specified:

SELECT * FROM users WHERE status<>4 OR status IS NULL

What's with the MySQL's performance in such cases? Is the same column (status in the above example) checked twice or the optimizer takes care of it in some special way?

OUTER JOIN With Extra Conditions?
I need to get a list of products in a certain category, along with the quantity of each item already added to the shopping cart for a given session number. I'm having a heck of a time satisfying the latter condition.

shoppingCart table:
+--------+-----------+-----------+-----+
| cartID | sessionID | productID | qty |
+--------+-----------+-----------+-----+
product table:
+-----------+--------------+------+--------+
| ProductID | ProductCatID | name | Active |
+-----------+--------------+------+--------+
Here's my base query, which just gets all the products in a given category.

SELECT product.*
FROM product
WHERE `ProductCatID`='{$id}'
AND `Active`=Ƈ'
Here's the query I've got so far:

SELECT product.*, shoppingCart.qty
FROM product
LEFT OUTER JOIN shoppingCart ON shoppingCart.productID = product.ProductID
WHERE `ProductCatID` = Ɗ'
AND shoppingCart.sessionID = '{$sessionID}'
AND `Active` = Ƈ'
Obviously, this does NOT work, because it limits the query to ONLY products that have the specified sessionID. I need it to return ALL the products in the category, but give me the quanity for items in the shoppingCart table, ONLY IF the sessionID matches (otherwise it should return NULL)!

Combining UPDATE Statements With Different Fields And Conditions
I'm slightly paranoid that I haven't unearthed an existing answer to this question, but the ones I have been able to find didn't have two different variables requiring two different conditions. Basically, I want to know if it's possible to combine the following UPDATE statements into a single query:

UPDATE table SET field1 = field1 - n WHERE field 1 > x
UPDATE table SET field2 = field2 - n WHERE field 2 > x

.......

How Do I Write Multiple Conditions In An MySQL IF Statement?
I am using version 3.23 and want to write something like this:

Granting User Access On Field With Restrictions Or Conditions
I am planning to develop a database that will have many users. Assume I have a table called Customer having these fields (id, name, user). Assume we have these Data:

ID Name User
1 Cust1 user1
2 Cust2 user1
3 Cust3 user1
4 Cust4 user2
5 Cust5 user2

I want the users to have access on those records that are created by them only or those that have their name on in the (User Field).

For example, if (user1) accessed the database and called: "SELECT * FROM customer;" it should return these data only automatically (or throw an exception at least):

Result:
1 Cust1 user1
2 Cust2 user1
3 Cust3 user1

(or throw an exception at least) that he cant call this statements without a where cluse like (USER=user1).

also, the user should have only the right to insert records having their name on in the (User) Field. For example: user1 can only call : "INSERT in CUSTOMER Values (6 , Cust6, user1), if he inserts user2 instead, the system should through an exception.

I hope I made clear examples of what I would like to build. How can I acheive this?

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

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

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

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

begin transaction

run a few sql statemenets

end transaction

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

Transactions
this is apart of a php script

first i BEGIN the transaction

then i have a mysql_query()

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

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

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

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

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

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

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

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

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

Using Transactions With InnoDB
Right now I am experiencing with transactions in MySQL using the InnoDB engine.
I found a really great article which includes all about transactions:
http://dev.mysql.com/books/mysqlpres...rial/ch10.html

However, I still got a question and I couldn't find the answer exactly in this article.
A simple example:

UPDATE users SET money = money - 1000 WHERE id = 1 ;
UPDATE users SET money = money + 1000 WHERE id = 2 ;
I just want both queries executed OR both not executed, so I should use a transaction.
I thought the following would give me the right result, but it is not exactly what I want:

START TRANSACTION ;
UPDATE users SET money = money - 1000 WHERE id = 1 ;
UPDATE users SET money = money + 1000 WHERE id = 2 ;
COMMIT ;
If user 1 doesn't have 1000 dollar anymore, both queries shouldn't be executed.
The same for when the field name in the first query is wrong spelled or doesn't exist, both queries shouldn't be executed.

I read something about ROLLBACK in the mysql article on the mysql.com website and I think I can solve my problem with this one, but I don't know exactly how to use this.

The example in the article says:

START TRANSACTION ;
UPDATE users SET money = money - 1000 WHERE id = 1 ;
UPDATE users SET money = money + 1000 WHERE id = 2 ;
SELECT money FROM users WHERE id = 1 ;
ROLLBACK ;
But this one doesn't work for me. The ROLLBACK will always be executed, even when the user got enough money.

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

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

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

$result = MYSQL_QUERY($sql);
?>

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

$result = MYSQL_QUERY($sql);
?>

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

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

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

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

I'm doing this :

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

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

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

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

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

I'm doing this :

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

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

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

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

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

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

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

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

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

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

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

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

Auditing Transactions
With regard to auditing transactions :

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

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

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

Transactions Tutorial
I'm looking for any online resources/tutorials and such about transactions.
Preferable for MySQL, but others will do.
Other than that, is there any good book about transactions. Again preferable
for MySQL.

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

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

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

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

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

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

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

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

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

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

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

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

CREATE PROCEDURE testproc

BEGIN
START TRANSACTION;
CALL another_proc();
COMMIT;

END;

Or is there anything I need to take care of?

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