MySQL Forums - Transactions

How to get % status (no replies)

Ajeet Sri — Wed, 22 May 2013 09:25:09 +0000

I have a table that contain a column like as-

Course_Name Status

Math Complete
Math Complete
Math Complete
Science Incomplete
Science Complete
Science Complete

I need the sql query who give % complete of each course which output like as follows-

Course_Name % status_Complete
Math 100%
Science 33.3%

Max no of select queries to a table in mysql? (1 reply)

Velmurugan Panneerselvam — Thu, 11 Apr 2013 16:30:23 +0000

Hi,
Can anyone please let me know how many different select queries we can execute to a particular table in mysql at a time?

Thanks in advance,
Velmurugan.P

Locking multiple rows in repeatable read mode (1 reply)

Marko Vukovic — Tue, 02 Apr 2013 05:02:46 +0000

If I understand correctly how repeatable read works, on the first select in a trasaction the database creates a snapshot of its current state and with few exceptions like "insert into ... select ..." the transaction does not see any changes hapenning outside of it.

In my situation I have to lock two or more arbitrary rows identified discretly by their primary key values. If I lock them independently like this:

select * from t where pk=3 for update;
select * from t where pk=8 for update;

another transaction may squezee between these two statements and do something the first transaction would not be aware of. I cannot process the records independently with a commit between them because this has to be dealt with in "all or nothing" manner. Is the only solution to lock both records at the same time like this:

select * from t where pk in (3,8) for update;

I am not particularly fond of that idea because I do not like building the statements of arbitrary lentght like this one dynamically. Also, would it honour the order in which it locks them so I can avoid a deadlock by sorting them?

Thank you for your response in advance.
Marko

How do I know which transaction lock a table? (6 replies)

john walker — Sun, 24 Mar 2013 00:52:37 +0000

if I execute any alter statement(alter table online_payment add column mac varchar(200); or alter table online_payment engine="MyISAM"; or alter online_payment AUTO_INCREMENT=15492;) to online_payment, this table will be stuck, but I can execute any insert statement, and there isn't any other process operate this table(i make sure this by show processlist statment), only one "Waiting for table metadata lock alter table". The engine of this table is myiasm, so i believe that there is a transactions lock this table. In this case, is there any way to solve this problem without restart mysql? I have waited ten minutes before i stop the alter statement, so that transaction should be running at least ten minutes, and i execute the alter several times, so the transaction should be often executed. And now as long as I execute this alter statement, then I would not be able to execute select statement, the results of select will immediately come out if I interrupt the alter statement.

solved: How can a user read his own privileges? (2 replies)

Wolfgang Jacques — Thu, 14 Mar 2013 01:06:22 +0000

Hello everyone,

I am writing a database application in OpenOffice and each person logging in has his own mySQL user.

In order to customize forms I would like to read out, if user x has update privileges for table y or not, for example. Of course, user x has no access to the information_schema database. Can he get to know his rights without trying and throwing errors?

Thank you!

Update column takes time!! (1 reply)

MOnalee Bhandge — Sun, 10 Mar 2013 18:08:21 +0000

Dear all!!
I have 450 number of tables. Every table has a column 'update_count'.
At some time I have to update value of this column to zero. But due large volume of data(row) in every table and also indexing on many other column it takes too...too much time.
Can be a different trick to update a column value, of every table??

changing isolation level (1 reply)

Christian Soutou — Sun, 24 Feb 2013 21:23:23 +0000

Hello

Testing the last GA version, and SET TRANSACTION ISOLATION doesn't seem to change internal variable.

Server version: 5.6.10 MySQL Community Server (GPL)

(soutou@localhost) [bdsoutou] mysql> SELECT @@tx_isolation;
+-----------------+
| @@tx_isolation |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set (0.00 sec)

(soutou@localhost) [bdsoutou] mysql> SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
Query OK, 0 rows affected (0.00 sec)

(soutou@localhost) [bdsoutou] mysql> SELECT @@tx_isolation;
+-----------------+
| @@tx_isolation |
+-----------------+
| REPEATABLE-READ |
+-----------------+

It works with SET SESSION tx_isolation='READ-UNCOMMITTED' but why ?

SELECT ... FOR UPDATE does not honor next-key locks (no replies)

Brian Bernstein — Tue, 12 Feb 2013 18:10:33 +0000

I was wondering if this is expected behavior or a bug I am seeing in the InnoDB table engine.

If a SELECT . . . FOR UPDATE statement is called, other selects will block on any rows locked by this statement. However, if the SELECT . . . FOR UPDATE statement does not find any rows, but applies a lock on an index, a similar SELECT statement will not block on that lock.

A simple use case:

Create a table named `test` with a column named col1, with an index on col1:

CREATE TABLE `test` (
`col1` int(11) NOT NULL,
KEY `col1` (`col1`)
) ENGINE=InnoDB;

On one session, start a transaction, and select for update where col1=1:

BEGIN;
SELECT * FROM `test` WHERE col1=1 FOR UPDATE;

This will select an empty set, but should set an appropriate lock on an index.

Before committing the first transaction, on a second session, start another transaction, and attempt to insert a row with col1 equalling 1.

BEGIN;
INSERT INTO `test` values (1);

This will block as expected. However, if while the first transaction is still open and a new transaction is repeated with the same select statement as the first:

BEGIN;
SELECT * FROM `test` WHERE col1=1 FOR UPDATE;

It does not block and returns immediately. It blocks only if there were rows found (with row blocking).

According to the documentation on innodb-next-key-locking (http://dev.mysql.com/doc/refman/5.0/en/innodb-next-key-locking.html), this should block:

"You can use next-key locking to implement a uniqueness check in your application: If you read your data in share mode and do not see a duplicate for a row you are going to insert, then you can safely insert your row and know that the next-key lock set on the successor of your row during the read prevents anyone meanwhile inserting a duplicate for your row. Thus, the next-key locking enables you to “lock” the nonexistence of something in your table."

Is this an error in the documentation or a bug in MySQL? The use case in the documentation is exactly the issue I am seeing-- I select a row and insert only if it doesn't exist. However, when this operation occurs simultaneously, both operations see no row existing, and both then insert the same row, resulting in duplicate rows.

I've tried the test stated above with both "LOCK IN SHARE MODE" as well as "FOR UPDATE", and found the same issue occurs for both.

Importing a database dump created with --single-transaction does not result in single-transaction import of the file (4 replies)

Ben Johnson — Thu, 14 Feb 2013 04:23:16 +0000

Hello,

I'm not sure that this qualifies as a "Newbie" question, but this forum seems to lack a more appropriate category.

I have a handful of tables, totaling 150 MB in size, that I need to dump and then import into another server on a regular basis.

The destination server is a production server on which zero downtime while replacing the tables is a must.

I thought that enclosing the dump in a single transaction would serve the intended purpose. However, in practice, each table appears to be emptied before the data is imported, which causes the table to contain incomplete data for as long as is required to import the data in its entirety. This occurs one table at a time. To summarize:

1.) Table is dropped and recreated.
2.) Data is inserted; this process requires several minutes, during which time the the number of rows in the table increases "in real-time", as the data is imported.
3.) The process is repeated for the next table.

In essence, it seems that START TRANSACTION is being ignored.

I see nothing on the relevant manual page ( http://dev.mysql.com/doc/refman/5.1/en/commit.html ) that explains this behavior when using AUTOCOMMIT=0, START TRANSACTION, and COMMIT.

The structure of the dump file is as follows:

-----------------------------------------------
SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
SET AUTOCOMMIT=0;
START TRANSACTION;
SET time_zone = "+00:00";

DROP TABLE IF EXISTS `table`;
CREATE TABLE IF NOT EXISTS `table` ([...]);
INSERT INTO `table` ([...]) VALUES ([...]);

COMMIT;
-----------------------------------------------

The dumps are created with the following command:

mysqldump --add-drop-table --default-character-set=utf8 --extended-insert --host=localhost --quick --quote-names --routines --set-charset --single-transaction --triggers --tz-utc --verbose --user=root --password=password mydbname

All tables are InnoDB.

MySQL version is 5.1.66.

Thanks for any help!

deadlock for mixed row and table lock? (4 replies)

Andrew Nuss — Sun, 24 Feb 2013 21:39:23 +0000

Hi,

Using InnoDB. 2 Tables, call them A and B, which are used in conjunction to define my "tree" relationships. I always make sure in all transactions to update A and THEN update B.

However, in many:

I do row updates on rows of A (sorted by id) (row lock) and then inserts on B (assume table lock) while in others I do inserts on A (assume table lock) and then updates on rows of B (sorted by id).

Am I safe against deadlock?

Andy

Transaction Not Fully Commited (1 reply)

Teddy Cooper — Mon, 31 Dec 2012 20:17:34 +0000

I am having a problem with a transaction.

Environment:
- C# ASP.NET
- MySQL 5.1.51

Tables:
- tbl_book
- tbl_pages

My code flow basically works like this:
1) Start Transaction
2) Call a stored procedure (book_new) to insert a new book record into tbl_book and return the inserted book id using LAST_INSERT_ID
3) In my code, I can read the returned Book ID
4) In my code, in a loop, generate a page and call another stored procedure (page_new) to insert the new page record into tbl_pages with the Book ID as foreign key
5) After generating all the pages (around 15 ~ 100 records, usually takes about 45 ~ 90 seconds), the transaction is committed.

The problem:
tbl_pages has all the records with the Book ID, however, tbl_book has no record with Book ID.

In another word, the transaction only commits half of the statements.

Any idea why?

MySQL Stored Procedure coding question (1 reply)

Ed English — Sat, 29 Dec 2012 22:56:19 +0000

I am using MySql with Workbench

I have not been able to use the MAX(C.LastContact) agregate in the WHERE clause which is needed to get the correct results. I am trying to filter based off a incoming parameter 'MonitorStatus'. I have tried using HAVING and CASE WHEN. Neither has worked. If I could bring in one more column into the select statement that could be used for filtering , it would work but if I do that it messes up the COUNT returning a SINGLE ROW / SINGLE NUMBER, needed for a Digital gadget. I have not been able to get it to work. I am using Dynamic SQL. Below is a portion of the code:
DOES ANYONE KNOW HOW THIS CAN BE DONE?

DELIMITER $$

DROP PROCEDURE IF EXISTS usp_cbi_TEST_LocationMonitorCounts$$
CREATE PROCEDURE usp_cbi_TEST_LocationMonitorCounts(

QueryType varchar(30),
MonitorStatus varchar(50),
StatusTimeToFail int #In Minutes
)
BEGIN
IF QueryType LIKE ('%Count%')
THEN
SET SelectStmt =

"SELECT COUNT(distinct(C.LocationID))AS 'Total Locations'

FROM Computers AS C
#******************************************************************************
IF ((StatusTimeToFail IS NULL) OR (StatusTimeToFail ='') OR (StatusTimeToFail =0))
THEN
SET @TimeToFail = 15; #DEFAULT minutes
ELSE
SET @TimeToFail = StatusTimeToFail;
END IF;
#******************************************************************************
IF (QueryType LIKE ('%Count%'))
THEN
IF (MonitorStatus != 'all')
THEN
IF (MonitorStatus LIKE('%Fail%'))
THEN
SET WhereClause = CONCAT(' WHERE ');
SET WhereClause = CONCAT(WhereClause,
" IFNULL(TIMESTAMPDIFF(minute, C.LastContact, NOW()),0) ", '>=', @TimeToFail);

ELSEIF (MonitorStatus LIKE('%OK%'))
then
SET WhereClause = CONCAT(' WHERE ');
SET WhereClause = CONCAT(WhereClause,
" IFNULL(TIMESTAMPDIFF(minute, C.LastContact, NOW()),0) ", '<', @TimeToFail);

Alternative for IN+Subselect in this case (1 reply)

Christoph Noltensmeier — Sat, 17 Nov 2012 02:33:42 +0000

Good morning everyone,

iam having some problems with the following statement. The Subselect is moderately quick, but the whole statment takes about 70-75 seconds, which is unacceptable. Here are some facts you might need to know:
The statement is partly hardcoded and cant be changed (until the first WHERE). So i cant use a join rather than the IN+Subselect.
"EXISTS" only saves me a few seconds.
table consists of 1 million lines
idxmail consists of 2 million lines
matching consists of 10 000 lines
@var1 is set by the program

SELECT col1, col2
FROM table b
WHERE b.id IN (
SELECT id
FROM idxmail i
JOIN matching m ON i.proxyaddress = m.proxyaddress
WHERE m.mailnickname = '@var1')

The profile of this query has about 2 million lines aswell. One for each line in idxmail.

Explain (There is an index on id in table b):

"id" "select_type" "table" "type" "possible_keys" "key" "key_len" "ref" "rows" "Extra"
"1" "PRIMARY" "b" "ALL" NULL NULL NULL NULL "813796" "Using where"
"2" "DEPENDENT SUBQUERY" "i" "ref" "ID,Proxyadresse" "ID" "5" "func" "1" "Using where"
"2" "DEPENDENT SUBQUERY" "m" "ref" "Mailnickname,Proxyadresse" "Proxyadresse" "768" "dwdata.i.Proxyadresse" "1" "Using where"

Thanks in advance.

Optimisation problem with left join (1 reply)

Gary Broughton — Sat, 17 Nov 2012 02:43:07 +0000

Hi

I wonder if anyone could offer any advice on the following query. It runs okay, and very quickly, but due to the left join it is scanning all rows on the "teams" table despite having an index set on 'url_teamname', and I'm hoping this can be reduced somehow.

A URL friendly team name can be passed in a URL, and I need to grab the ID and name for that team. We have 3,161 teams in the "teams" table, but there may or may not be an entry in table "prevteams" depending on if the team was formerly known by another name for a range of seasons.

The query is as follows:

SELECT t.teamid AS useid, IF(pt.prevname IS NULL,t.teamname,pt.prevname) AS usename
FROM teams t
LEFT JOIN prevteams pt ON t.teamno = pt.teamno AND pt.fromssn <= '142' AND pt.tossn >= '142'
WHERE pt.url_prevname = 'arsenal' OR t.url_teamname = 'arsenal';

The explain results are as follows:
1, 'SIMPLE', 't', 'ALL', 'IX_URLT', '', '', '', 3161, ''
1, 'SIMPLE', 'pt', 'ref', 'PRIMARY,IX_TmFromTo,IX_TmFrom,IX_TmTo,IX_From_To', 'IX_TmFromTo', '4', 'football.t.TeamNo', 1, 'Using where'

Many thanks in advance
Gary

Query Is to Slow (4 replies)

Scott Hanebutt — Wed, 31 Oct 2012 14:13:24 +0000

The following two queres when ran separatly give me results inless then a second.

set @start = '2012-01-01';
set @stop = '2012-12-31';

select
distinct concat(users.first_name, " ", users.last_name) as Name
from
users
left join tasks on users.id = tasks.created_by
where
(tasks.date_start between @start and @stop)
order by concat(users.first_name, " ", users.last_name)
;

set @start = '2012-01-01';
set @stop = '2012-12-31';

select
distinct concat(users.first_name, " ", users.last_name) as Name
from
users
left join meetings on users.id = meetings.created_by
where
(meetings.date_start between @start and @stop)
order by concat(users.first_name, " ", users.last_name)
;

However when I combine the queries in to the following it takes munites (I have never waited long enough for it to finish).

set @start = '2012-01-01';
set @stop = '2012-12-31';

select
distinct concat(users.first_name, " ", users.last_name) as Name
from
users
left join tasks on users.id = tasks.created_by
left join meetings on users.id = meetings.created_by
where
(tasks.date_start between @start and @stop)
or (meetings.date_start between @start and @stop)
order by concat(users.first_name, " ", users.last_name)
;

What am I doing wrong, or is this just a performance issue?

Thank you,
Scott

SQL SELECT statement taking too long (1 reply)

Joshua Walusz — Sat, 27 Oct 2012 21:49:17 +0000

Hello,

I am currently running the following SQL statement:

SELECT company
FROM plotrows
WHERE pid
IN (

SELECT pid
FROM plotrows
WHERE hybrid = '1098'
)
GROUP BY company
ORDER BY company

The query is taking almost 5 minutes. This is causing a problem and I believe it is time out in my PHP script. When I try to run the PHP script in my browser I receive the following error:

Query execution was interrupted

The 'plotrows' database table has about 37,000 records...

How can I go about getting this query to run faster? Any suggestions?

PLEASE HELP, SELECT FOR UPDATE NOT WORKING (no replies)

Alem Bišćan — Sun, 14 Oct 2012 18:22:21 +0000

SELECT .... FOR UPDATE not working when fired from remote accounts. It works fine when i run it from the command line client directly on the server using root@localhost account. I have given all privileges to my remote accounts and it doesnt work. I am using DBFORGE studio as client. When i execute the following statement

SELECT * FROM SOMETABLE WHERE ID = 8 FOR UPDATE;

it runs and finishes and everything looks fine. Then i log to my server, run command line and connect to my MySql db as root@localhost to test. Aggain i run

SELECT * FROM SOMETABLE WHERE ID = 8 FOR UPDATE;

And it finishes. So row wasnt locked!?!?!?. I'm like WTF? So i go back to dbForge studio running on my computer and run

SELECT * FROM SOMETABLE WHERE ID = 8 FOR UPDATE;

AND ROW IS LOCKED, TRANSACTION IS WAITING!!!

I cannot figure out why SELECT .... FOR UPDATE doesnt lock the row when run by remote accounts from remote computer, and workse when run from the server.

PLEASE HELP!!!!

BTW. I HAVE TRIED EVERYTHING. GIVEN THE USERS GRANT ALL PRIVILEGES and everything!!!

Select statement display all ip-addresses of a subnet (1 reply)

Florian Schilling — Wed, 10 Oct 2012 05:37:41 +0000

Hi,

I'm not new in Mysql, but my knowledge is just some basics of SQL. I hope somebody could help, because first I'm not sure if it is possible what I'm looking for and further, what do I need to look for :S

Here my question:
I have a table called network_addr which consist of the fields:
- id_network, network_addr, mask

As network_addr is stored in decimal format and mask in number of bits, I can calulate how ip-addresses this network could contain.

Now the question is, is it possible by an sql statement to print a table with all possible ip-addresse?

Hope somebody could help me.

Thanks!

include line number depending on field (no replies)

pascual bayo — Tue, 25 Sep 2012 13:48:29 +0000

Hello,

I've got a table in my database where I store the order lines. I am using a sh script to export that table to a csv.
What I need is to show the line number in its order, so if it outputs the first order, lines would be:
1,product1
2,product2
3,product3

for the second order, lines would begin with 1, so I can see for every order, the numbered lines.

Is there any way to achieve this, something like incrementing a variable and reseting it to 0 when a new order_id is found?

I've achieved creating a variable and incrementing it on every iteration, but I don't know how to know the field.

Thanks you!

How Count Distinct With IF (1 reply)

Daniel Estrada — Fri, 28 Sep 2012 04:47:55 +0000

I need to do a query with count distinct and IF, but the results always are 0.
What I need to do, is count the different users from a table in different months, using IF, and finally grouping by some value. My individual query, for one month is this:

SELECT
COUNT(DISTINCT(idUsers)) AS num_usuarios
FROM table01
WHERE date1='201207'
GROUPED BY cve_school

But I need to get the results by different months in the same query. What I'm trying to do is this:

SELECT
IF(date1=(201207), count(distinct(idUsers)), 0) as user30,
IF(fecha1=(201206), count(distinct(idUsers)), 0) as user60,
IF(fecha1=(201205), count(distinct(idUsers)), 0) as user90,
IF(fecha1=(201204), count(distinct(idUsers)), 0) as user120,
IF(fecha1=(201203), count(distinct(idUsers)), 0) as user150
FROM table01
GROUPED BY cve_school

But the all the results are always 0.

work with transaction in Query Browser! (no replies)

huke joli — Thu, 13 Sep 2012 09:55:50 +0000

I have just upgraded Mysql for HP from 4.0.8 (Connector/J 3.0.11) to 4.1.11 (Copnnector/J 3.1.7) and realized that my old code did not work anymore. Be specific, the ResultSet that was obtained from executing a PreparedStatement was empty. In the process of figuring what went wrong, I replaced the PreparedStatement with non-prepared statement and it works:
Watch The Dark Knight Rises Online
String query = "SELECT xxxxxxxxx";
Statement stmt = conn.createStatement();
rs = stmt.executeQuery(query);
Watch The Expendables 2 Online

Just wondering if anyone has experienced the same issue? The query string is pretty long and have couple of inner joins and outer joins and plus order by clause. I don't know if that also contribute to the problem.
Watch Ted Online

FiFo in mysql (1 reply)

Paresh Madani — Mon, 20 Aug 2012 03:21:08 +0000

Hi,
I have a tricky transaction that I haven't been able to create. I have looked for an answer on the web, but I haven't found anything...

Imagine I have created a database where I keep track of when and how many company shares (Stocks) I sell/buy and the shares prices.

Let's say that I have bought the following shares:
01/01/2007 100 Yahoo Shares at 10$ each.
02/01/2007 100 Yahoo Shares at 20$ each.

The average price in this case would be (we can use AVG()):
200 Yahoo Shares at 15 $ each.

The problem is that if I sell:
03/01/2007 100 Yahoo shares at 30$ each.
The average price would be:
(100*10$ + 100*20$ - 100*30$)/100 = 0 $.

That average price includes the profit within the average price. I want to consider a FIFO system, where the first shares that I bought are the ones that are sold first. That way I would get an average price of:
100 Yahoo Shares at 20 $ each.

Is it possible to do this FIFO thing using Mysql?. Since I am using PHP, would it be better to do all the FIFO calculations within PHP?

I would really appreciate any help with this.

Thanks in advance.

Transaction in functions/stored procedure (1 reply)

Sam Jonhson — Thu, 02 Aug 2012 14:34:23 +0000

If i have in a mysql function/stored procedure just 2 queries (2 deletes from different tables, or 1 insert and 1 delete for different tables), how shoul i use the transaction? I think i must use it because if the second query fails, my DB is wrong. What controls should i use to see if i must commit or rollback? I searched, but i found just some examples with query+commit or query+rollback.

how to get value of auto increased number (3 replies)

Global Solution — Mon, 16 Jul 2012 05:30:43 +0000

right now, this is the best way I can think of to preserve the record identity using auto incresed number (pseudo-code):

//begin of pseudo code

trans.begin

statement.execute("INSERT INTO TBL1 (name) VALUES ('one')"); // inserting
rs = statement.execute("SELECT MAX(recID) number FROM TBL1"); //get recID

long number = rs.get("number");

trans.commit

//end of pseudo code

this is the only way I can think of to make the "number" available in the application code. Is there any better way? I need alternative that will work in concurrent multi-user environment, where multiple user may access the same row at the same time. thanks

Table Rename (1 reply)

Rob UK — Wed, 13 Jun 2012 16:36:27 +0000

Is there someway to do a table rename within a transaction, or make it atomic so if an update fails it doesnt rename the table.

Thanks

Best isolation level for the transaction (4 replies)

Luiz Cláudio Pinheiro — Fri, 27 Apr 2012 03:01:23 +0000

Hello guys, are you ok?

Well, i'm having a little doubt about a isolation level of a transaction.
I have a group of query that will be executed and perform the follow actions:

- 1º step: check if the user account have founds (SELECT);
- 2º step: (here, some updates in another table and bla bla bla);
- 3º step: disccount the ammount (if his account have founds) of the user account;

The problem is: if two transaction were executed in the same time (on the truth, with miliseconds of diference, anyway, SIMULTANEOUS!), the 1º step and 3º step can be a big problem for the database. Let's call the transactions by T1 and T2. If T1 execute the 1º step, and T2 execute the 1º step BEFORE T1 execute the 3º step, T2 can receive the response that have founds in the account, even if the T1 in the 3º step, erase all this founds.

So i need a isolation level for the transaction that make the T2 (or T3, T4, Tn) wait for a complete execution of T1 for continue.

So well, what is the best choice for my problem friends?
Best regards,
Luiz Cláudio Pinheiro

Multi-User Updates (1 reply)

Lee Bornstein — Mon, 23 Apr 2012 21:36:32 +0000

I'm new to working with MySql and could use some help.

I have a relationship between two tables - Budget_Master_File and Budget_Transactions.

Each record in Budget_Master_File can have MANY Budget_Transaction Records.

Tables are related by Budget_Master_File.Bud_ID and Budget_Transactions.BudID

I would like to Edit the amount of an existing Budget_Transaction (t.Amount) and update the Current Account Balance (b.balance) by backing out old transaction amount and adding in new transaction amount.

To get my data that needs to be edited (Transaction ID 27) I use the following SQL.

SELECT *
FROM Budget_Transactions t
LEFT JOIN Budget_Master_File b
ON b.Bud_ID = t.BudID
WHERE t.Trans_ID = 27;

Now I display data and wait for operator to click a UPDATE Button

It could be several minutes before UPDATE button is clicked.

Once UPDATE is Clicked I need to:
Lock both Tables
Apply Updates to both Tables

b.Balance = b.Balance - t.Amount + NewAmount
t.Amount = NewAmount

Finally - Unlock Tables

I am assuming that I would need to do something like the following:

-- Lock Tables ---
LOCK TABLES
Budget_Master_File WRITE,
Budget_Transactions WRITE;

-- ReFetch Data ---
SELECT *
FROM Budget_Transactions t
LEFT JOIN Budget_Master_File b
ON b.Bud_ID = t.BudID
WHERE t.Trans_ID = 27;

-- Update Tables Here ---

UNLOCK TABLES;

Can someone show me:
1. How do do Lock for Writing if my code is wrong.
2. Best way to get data immediately before updating
3. How to do the Update
4. Unlock Procedure if my code is wrong.

Any assistance is appreciated.

Thanks in advance...

Uncommitted rows are updated by a second transaction (2 replies)

Moshe Elisha — Thu, 29 Mar 2012 21:52:33 +0000

Hello,

Please help me understand the following scenario:
Connection 1 and connection 2 starts a transaction with REPEATABLE-READ transaction isolation.

A. Connection 1:
1. Inserts a row.
2. commit.

B. Connection 2:
1. Selects all rows - gets no rows.
2. Updates all rows - the row of connection 1 is updated.
3. Selects all rows - the up-to-date row of connection 1 is returned.

C. Connection 1:
1. Selects all rows - gets the inserted row - not up-to-date.

I would expect that the UPDATE in B.2 will not update any rows.
Is this the correct behaviour? Can you please explain?

MySQL Server 5.1.42-community.

Thanks.

This is an output of a Java test:

connection1 = Connection 73934, AutoCommit = false, JDBC TransactionIsolation = 4, DB TransactionIsolation REPEATABLE-READ
connection2 = Connection 73935, AutoCommit = false, JDBC TransactionIsolation = 4, DB TransactionIsolation REPEATABLE-READ
executeUpdate Connection 73934, AutoCommit = false, JDBC TransactionIsolation = 4, DB TransactionIsolation REPEATABLE-READ: 'INSERT INTO TEST_TABLE (PK_COL) VALUES (NULL)', updatedRowCount = 1
AFTER ONE INSERT BEFORE COMMIT: Connection 73934, rows {1=null}
AFTER ONE INSERT BEFORE COMMIT: Connection 73935, rows {}
AFTER ONE INSERT AFTER COMMIT: Connection 73934, rows {1=null}
AFTER ONE INSERT AFTER COMMIT: Connection 73935, rows {}
executeUpdate Connection 73935, AutoCommit = false, JDBC TransactionIsolation = 4, DB TransactionIsolation REPEATABLE-READ: 'UPDATE TEST_TABLE SET COL2 = CURRENT_TIMESTAMP', updatedRowCount = 1
AFTER UPDATE ALL: Connection 73934, rows {1=null}
AFTER UPDATE ALL: Connection 73935, rows {1=2012-03-29 11:57:44.0}
executeUpdate Connection 73935, AutoCommit = false, JDBC TransactionIsolation = 4, DB TransactionIsolation REPEATABLE-READ: 'INSERT INTO TEST_TABLE (PK_COL) VALUES (NULL)', updatedRowCount = 1
executeUpdate Connection 73935, AutoCommit = false, JDBC TransactionIsolation = 4, DB TransactionIsolation REPEATABLE-READ: 'INSERT INTO TEST_TABLE (PK_COL) VALUES (NULL)', updatedRowCount = 1
executeUpdate Connection 73935, AutoCommit = false, JDBC TransactionIsolation = 4, DB TransactionIsolation REPEATABLE-READ: 'INSERT INTO TEST_TABLE (PK_COL) VALUES (NULL)', updatedRowCount = 1
AFTER THREE INSERT: Connection 73934, rows {1=null}
AFTER THREE INSERT: Connection 73935, rows {1=2012-03-29 11:57:44.0, 2=null, 3=null, 4=null}
AFTER COMMIT: Connection 73934, rows {1=2012-03-29 11:57:44.0, 2=null, 3=null, 4=null}
AFTER COMMIT: Connection 73935, rows {1=2012-03-29 11:57:44.0, 2=null, 3=null, 4=null}

This is the Java class:

import java.sql.*;
import java.util.HashMap;
import java.util.Map;

public class TestTransaction {
public static void main(String[] args) throws SQLException {

Connection connection1 = null;
Connection connection2 = null;
try {

prepareEnv();

connection1 = createConnection();
connection1.setAutoCommit(false);
System.out.println("connection1 = " + toString(connection1));

connection2 = createConnection();
connection2.setAutoCommit(false);
System.out.println("connection2 = " + toString(connection2));

printRows("START", connection1);
printRows("START", connection2);

executeUpdate(connection1, "INSERT INTO TEST_TABLE (PK_COL) VALUES (NULL)");

printRows("AFTER ONE INSERT BEFORE COMMIT", connection1);
printRows("AFTER ONE INSERT BEFORE COMMIT", connection2);
connection1.commit();
printRows("AFTER ONE INSERT AFTER COMMIT", connection1);
printRows("AFTER ONE INSERT AFTER COMMIT", connection2);

executeUpdate(connection2, "UPDATE TEST_TABLE SET COL2 = CURRENT_TIMESTAMP");
printRows("AFTER UPDATE ALL", connection1);
printRows("AFTER UPDATE ALL", connection2);

executeUpdate(connection2, "INSERT INTO TEST_TABLE (PK_COL) VALUES (NULL)");
executeUpdate(connection2, "INSERT INTO TEST_TABLE (PK_COL) VALUES (NULL)");
executeUpdate(connection2, "INSERT INTO TEST_TABLE (PK_COL) VALUES (NULL)");
printRows("AFTER THREE INSERT", connection1);
printRows("AFTER THREE INSERT", connection2);

connection1.commit();
connection2.commit();

printRows("AFTER COMMIT", connection1);
printRows("AFTER COMMIT", connection2);

} finally {
closeConnection(connection1);
closeConnection(connection2);
}
}

private static void prepareEnv() throws SQLException {
Connection connection = null;
try {
connection = createConnection();
Statement statement = connection.createStatement();
statement.execute("DROP TABLE `TEST_TABLE`");
statement.execute(
"CREATE TABLE `TEST_TABLE` (" +
" `PK_COL` int(11) NOT NULL AUTO_INCREMENT,\n" +
" `COL2` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,\n" +
" PRIMARY KEY (`PK_COL`)\n" +
") ENGINE=InnoDB DEFAULT CHARSET=utf8");
} finally {
closeConnection(connection);
}
}

private static void executeUpdate(Connection connection, String sql) throws SQLException {
Statement statement = connection.createStatement();
int rowCount = statement.executeUpdate(sql);
System.out.println("executeUpdate " + toString(connection) + ": '" + sql + "', updatedRowCount = " + rowCount);
}

private static Connection createConnection() throws SQLException {
return DriverManager.getConnection("jdbc:mysql://localhost/test", "test", "test");
}

private static String toString(Connection connection) throws SQLException {
int connectionId = fetchConnectionId(connection);
String transactionIsolation;
Statement statement = null;
ResultSet resultSet = null;
try {
statement = connection.createStatement();
resultSet = statement.executeQuery("SELECT @@tx_isolation");
resultSet.next();
transactionIsolation = resultSet.getString(1);
} finally {
closeResources(statement, resultSet);
}
return "Connection " + connectionId + ", AutoCommit = " + connection.getAutoCommit() + ", JDBC TransactionIsolation = " + connection.getTransactionIsolation() + ", DB TransactionIsolation " + transactionIsolation;
}

private static int fetchConnectionId(Connection connection) throws SQLException {
int connectionId;
Statement statement = null;
ResultSet resultSet = null;
try {
statement = connection.createStatement();
resultSet = statement.executeQuery("SELECT CONNECTION_ID()");
resultSet.next();
connectionId = resultSet.getInt(1);
} finally {
closeResources(statement, resultSet);
}
return connectionId;
}

private static void printRows(String prefix, Connection connection) throws SQLException {
Map rows = new HashMap();
Statement statement = null;
ResultSet resultSet = null;
try {
statement = connection.createStatement();
resultSet = statement.executeQuery("SELECT PK_COL, COL2 FROM TEST_TABLE");
while (resultSet.next()) {
rows.put(resultSet.getInt("PK_COL"), resultSet.getTimestamp("COL2"));
}
System.out.println(prefix + ": Connection " + fetchConnectionId(connection) + ", rows " + rows);
} finally {
closeResources(statement, resultSet);
}
}

private static void closeResources(Statement statement, ResultSet resultSet) {
if (resultSet != null) {
try {
resultSet.close();
} catch (SQLException e) {
System.out.println("WARN: Closing resultSet " + e);
}
}
if (statement != null) {
try {
statement.close();
} catch (SQLException e) {
System.out.println("WARN: Closing statement " + e);
}
}
}

public static void closeConnection(Connection connection) {
try {
if (connection != null) {
connection.close();
}
} catch (SQLException e) {
System.out.println("WARN: Closing connection " + e);
}
}
}
:

Convert string to date (1 reply)

Jolinda Bartlett — Thu, 29 Mar 2012 19:12:40 +0000

I have a table with dates as strings in the format 11-Jun-2011. I want to change this to a proper date in the format 2011/06/11.

In some of the tables, the dates are in the format: 11-Jun-11. I also want to change this to the format 2011/06/11.

Kind regards

Jolinda

q about concurrency for money transfer (1 reply)

sagi mann — Sat, 10 Mar 2012 19:30:02 +0000

Hi all,
I would like to make a money transfer procedure in a single transaction, but whether to perform or not perform the transaction depends on some value in some view (not table). something like this:

select units from view1 where product = 1;

if units > 0 then

update account set money = money - 10 where account_id = 1;

update account set money = money + 10 where account_id = 2;

endif

how can i make this transactional? I was thinking about "select for udpate" but I don't think it works on views, or at least, I'm not sure which records I should lock... I am concerned about the fact that 'units' may change between the "if" statement and the actual update statement...

I'd appreciate any help...
thanks