Re: Examples of savepoints and transactions
Hello Lars, On 1/24/2018 8:50 AM, Johan De Meersman wrote: What you're looking for is simple backup and restore :-) Savepoints are, simply put, markers within a transaction; allowing you to rollback only part of a transaction instead of the whole thing. A commit will inevitably commit the ENTIRE transactions, and thus remove the savepoints. A typical workflow for the kind of thing you're trying to do is to have your (automated) testing framework restore last night's backup after the test run. You could also make a backup before the test run and restore that afterwards; have an automated nightly db copy from prod to dev; or in very specific cases you could simply have your test system revert the data by issuing the "reverse" queries - although that one is rarely an option in real life. Another alternative would be to take a filesystem (or virtual machine) snapshot, and revert to that after the tests. Filesystem snapshots will require your database to be stopped and started, though. /Johan - Original Message - From: "Lars Nielsen" <l...@lfweb.dk> To: "MySql" <mysql@lists.mysql.com> Sent: Tuesday, 23 January, 2018 23:19:29 Subject: Re: Examples of savepoints and transactions Den 22-01-2018 kl. 22:01 skrev shawn l.green: Hello Lars, On 1/21/2018 3:37 PM, Lars Nielsen wrote: Hi, I have a system that uses begin and commit transactions. It works like a dream! ;) Now I want to test it by creating test data. This how ever cannot be rolled back. I think the solution for rolling back test data is to use savepoints and rollback. I think it is hard to find examples of this scenario. Are there some good guides or tutorials out there somewhere? Any suggestions are welcome. Best regards Lars Nielsen Can you mock up an example (a simple text walkthrough) of how you think a savepoint should work with what you are calling "test data" ? I think that the term "test data" is too general to make much sense to most of us in the context you described. Yours, Hello Shawn, Thanks for your interest. Here is an example of my idea. I have a php site working through PDO connections. I insert some data through php like this : |START TRANSACTION; SELECT @A:=SUM(salary) FROM table1 WHERE type=1; UPDATE table2 SET summary=@A WHERE type=1; INSERT INTO table3 values (x, y, z); COMMIT; ||| ||Now I want to do automated tests that create "dummy" data that i want to remove after the test has finished: like this : |SAVEPOINT autotest1; START TRANSACTION; SELECT @A:=SUM(salary) FROM table1 WHERE type=1; UPDATE table2 SET summary=@A WHERE type=1; INSERT INTO table3 values (x, y, z); COMMIT; -- DO OTHER TRANSACTIONAL OPERATIONS ROLLBACK TO autotest1; ||| ||All done. I have tested the application and have cleaned up the dummy test-data. The issue is that when I call the first commit then the savepoint is deleted. Is this possible at all? Regards Lars || Is table2 what you want to return to its earlier state? Other techniques to do what Johan suggested include: * Make a copy of your "base" data for each test run. That way you don't change your starting point. When that test run is over, drop the copy. This way your "data to be tested" exists (or ceases to exist) outside the boundaries of the transactions you are creating to test/change that data but the original state of that data persists somewhere else. * Use a non-transactional storage engine for table3 (being non-transactional means that the changes you store there will not be affected by a ROLLBACK or COMMIT. They become "permanent" the moment you do them). Yours, -- Shawn Green MySQL Senior Principal Technical Support Engineer Oracle USA, Inc. - Integrated Cloud Applications & Platform Services Office: Blountville, TN Become certified in MySQL! Visit https://www.mysql.com/certification/ for details. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Examples of savepoints and transactions
Kind Regards / Med venlig hilsen Lars Nielsen > - Original Message - >> From: "Lars Nielsen" <l...@lfweb.dk> >> To: "MySql" <mysql@lists.mysql.com> >> Sent: Tuesday, 23 January, 2018 23:19:29 >> Subject: Re: Examples of savepoints and transactions > >>> Den 22-01-2018 kl. 22:01 skrev shawn l.green: >>> Hello Lars, >>> >>>> On 1/21/2018 3:37 PM, Lars Nielsen wrote: >>>> Hi, >>>> I have a system that uses begin and commit transactions. It works >>>> like a dream! ;) >>>> Now I want to test it by creating test data. This how ever cannot be >>>> rolled back. I think the solution for rolling back test data is to >>>> use savepoints and rollback. I think it is hard to find examples of >>>> this scenario. Are there some good guides or tutorials out there >>>> somewhere? Any suggestions are welcome. >>>> >>>> Best regards >>>> Lars Nielsen >>>> >>> >>> Can you mock up an example (a simple text walkthrough) of how you >>> think a savepoint should work with what you are calling "test data" ? >>> I think that the term "test data" is too general to make much sense to >>> most of us in the context you described. >>> >>> >>> Yours, >> Hello Shawn, >> Thanks for your interest. Here is an example of my idea. >> >> I have a php site working through PDO connections. I insert some data >> through php like this : >> >> |START TRANSACTION; SELECT @A:=SUM(salary) FROM table1 WHERE type=1; >> UPDATE table2 SET summary=@A WHERE type=1; INSERT INTO table3 values (x, >> y, z); COMMIT; ||| >> >> ||Now I want to do automated tests that create "dummy" data that i want >> to remove after the test has finished: >> like this : >> >> |SAVEPOINT autotest1; START TRANSACTION; SELECT @A:=SUM(salary) FROM >> table1 WHERE type=1; UPDATE table2 SET summary=@A WHERE type=1; INSERT >> INTO table3 values (x, y, z); COMMIT; -- DO OTHER TRANSACTIONAL >> OPERATIONS ROLLBACK TO autotest1; ||| >> >> ||All done. I have tested the application and have cleaned up the dummy >> test-data. >> >> The issue is that when I call the first commit then the savepoint is >> deleted. >> >> Is this possible at all? >> >> Regards Lars >> >> || >> > > -- > The bay-trees in our country are all wither'd > And meteors fright the fixed stars of heaven; > The pale-faced moon looks bloody on the earth > And lean-look'd prophets whisper fearful change. > These signs forerun the death or fall of kings. > -- Wm. Shakespeare, "Richard II" > Den 24. jan. 2018 kl. 14.50 skrev Johan De Meersman <vegiv...@tuxera.be>: > > What you're looking for is simple backup and restore :-) > > Savepoints are, simply put, markers within a transaction; allowing you to > rollback only part of a transaction instead of the whole thing. A commit will > inevitably commit the ENTIRE transactions, and thus remove the savepoints. > > A typical workflow for the kind of thing you're trying to do is to have your > (automated) testing framework restore last night's backup after the test run. > You could also make a backup before the test run and restore that afterwards; > have an automated nightly db copy from prod to dev; or in very specific cases > you could simply have your test system revert the data by issuing the > "reverse" queries - although that one is rarely an option in real life. > > Another alternative would be to take a filesystem (or virtual machine) > snapshot, and revert to that after the tests. Filesystem snapshots will > require your database to be stopped and started, though. > > /Johan > Thanks Johan, I understood the savepoints could be around transactions and not within! I know how to restore from a backup. I just wanted to avoid loading 500+GB after each test-run. :) Thanks for your help everyone. /Lars
Re: Examples of savepoints and transactions
What you're looking for is simple backup and restore :-) Savepoints are, simply put, markers within a transaction; allowing you to rollback only part of a transaction instead of the whole thing. A commit will inevitably commit the ENTIRE transactions, and thus remove the savepoints. A typical workflow for the kind of thing you're trying to do is to have your (automated) testing framework restore last night's backup after the test run. You could also make a backup before the test run and restore that afterwards; have an automated nightly db copy from prod to dev; or in very specific cases you could simply have your test system revert the data by issuing the "reverse" queries - although that one is rarely an option in real life. Another alternative would be to take a filesystem (or virtual machine) snapshot, and revert to that after the tests. Filesystem snapshots will require your database to be stopped and started, though. /Johan - Original Message - > From: "Lars Nielsen" <l...@lfweb.dk> > To: "MySql" <mysql@lists.mysql.com> > Sent: Tuesday, 23 January, 2018 23:19:29 > Subject: Re: Examples of savepoints and transactions > Den 22-01-2018 kl. 22:01 skrev shawn l.green: >> Hello Lars, >> >> On 1/21/2018 3:37 PM, Lars Nielsen wrote: >>> Hi, >>> I have a system that uses begin and commit transactions. It works >>> like a dream! ;) >>> Now I want to test it by creating test data. This how ever cannot be >>> rolled back. I think the solution for rolling back test data is to >>> use savepoints and rollback. I think it is hard to find examples of >>> this scenario. Are there some good guides or tutorials out there >>> somewhere? Any suggestions are welcome. >>> >>> Best regards >>> Lars Nielsen >>> >> >> Can you mock up an example (a simple text walkthrough) of how you >> think a savepoint should work with what you are calling "test data" ? >> I think that the term "test data" is too general to make much sense to >> most of us in the context you described. >> >> >> Yours, > Hello Shawn, > Thanks for your interest. Here is an example of my idea. > > I have a php site working through PDO connections. I insert some data > through php like this : > >|START TRANSACTION; SELECT @A:=SUM(salary) FROM table1 WHERE type=1; > UPDATE table2 SET summary=@A WHERE type=1; INSERT INTO table3 values (x, > y, z); COMMIT; ||| > >||Now I want to do automated tests that create "dummy" data that i want > to remove after the test has finished: > like this : > >|SAVEPOINT autotest1; START TRANSACTION; SELECT @A:=SUM(salary) FROM > table1 WHERE type=1; UPDATE table2 SET summary=@A WHERE type=1; INSERT > INTO table3 values (x, y, z); COMMIT; -- DO OTHER TRANSACTIONAL > OPERATIONS ROLLBACK TO autotest1; ||| > >||All done. I have tested the application and have cleaned up the dummy > test-data. > > The issue is that when I call the first commit then the savepoint is > deleted. > > Is this possible at all? > > Regards Lars > >|| > -- The bay-trees in our country are all wither'd And meteors fright the fixed stars of heaven; The pale-faced moon looks bloody on the earth And lean-look'd prophets whisper fearful change. These signs forerun the death or fall of kings. -- Wm. Shakespeare, "Richard II" -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Examples of savepoints and transactions
Den 22-01-2018 kl. 22:01 skrev shawn l.green: Hello Lars, On 1/21/2018 3:37 PM, Lars Nielsen wrote: Hi, I have a system that uses begin and commit transactions. It works like a dream! ;) Now I want to test it by creating test data. This how ever cannot be rolled back. I think the solution for rolling back test data is to use savepoints and rollback. I think it is hard to find examples of this scenario. Are there some good guides or tutorials out there somewhere? Any suggestions are welcome. Best regards Lars Nielsen Can you mock up an example (a simple text walkthrough) of how you think a savepoint should work with what you are calling "test data" ? I think that the term "test data" is too general to make much sense to most of us in the context you described. Yours, Hello Shawn, Thanks for your interest. Here is an example of my idea. I have a php site working through PDO connections. I insert some data through php like this : |START TRANSACTION; SELECT @A:=SUM(salary) FROM table1 WHERE type=1; UPDATE table2 SET summary=@A WHERE type=1; INSERT INTO table3 values (x, y, z); COMMIT; ||| ||Now I want to do automated tests that create "dummy" data that i want to remove after the test has finished: like this : |SAVEPOINT autotest1; START TRANSACTION; SELECT @A:=SUM(salary) FROM table1 WHERE type=1; UPDATE table2 SET summary=@A WHERE type=1; INSERT INTO table3 values (x, y, z); COMMIT; -- DO OTHER TRANSACTIONAL OPERATIONS ROLLBACK TO autotest1; ||| ||All done. I have tested the application and have cleaned up the dummy test-data. The issue is that when I call the first commit then the savepoint is deleted. Is this possible at all? Regards Lars ||
Re: Examples of savepoints and transactions
Hello Lars, On 1/21/2018 3:37 PM, Lars Nielsen wrote: Hi, I have a system that uses begin and commit transactions. It works like a dream! ;) Now I want to test it by creating test data. This how ever cannot be rolled back. I think the solution for rolling back test data is to use savepoints and rollback. I think it is hard to find examples of this scenario. Are there some good guides or tutorials out there somewhere? Any suggestions are welcome. Best regards Lars Nielsen Can you mock up an example (a simple text walkthrough) of how you think a savepoint should work with what you are calling "test data" ? I think that the term "test data" is too general to make much sense to most of us in the context you described. Yours, -- Shawn Green MySQL Senior Principal Technical Support Engineer Oracle USA, Inc. - Integrated Cloud Applications & Platform Services Office: Blountville, TN Become certified in MySQL! Visit https://www.mysql.com/certification/ for details. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Examples of savepoints and transactions
Hi, I have a system that uses begin and commit transactions. It works like a dream! ;) Now I want to test it by creating test data. This how ever cannot be rolled back. I think the solution for rolling back test data is to use savepoints and rollback. I think it is hard to find examples of this scenario. Are there some good guides or tutorials out there somewhere? Any suggestions are welcome. Best regards Lars Nielsen -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
identifying the cause of old active transactions with locks
Hi,
In the output of
SHOW ENGINE INNODB STATUS \G
I have several old active transactions, each having locked some rows.
All or from the same process id, but with different thread ids. This
is the oldest one, which is 2.5 days old:
---TRANSACTION 0 1532609805, ACTIVE 227995 sec, process no 29517, OS
thread id 140076393318144
5 lock struct(s), heap size 1216, 2 row lock(s), undo log entries 1
MySQL thread id 2573, query id 7268517 10.49.11.54 destockjeans
Trx read view will not see trx with id = 0 1532609807, sees 0 1532609631
An strace on the process shows some activity, so it's not completely
locked (see below).
How can I debug this further? In a show full process list I don't see
any more related information.
Can I see which query the transaction is executing? How can a lock be
hold so long?
Any help is welcome, thanks in advance!
Raph
strace on process:
--
select(16, [13 15], NULL, NULL, NULL) = 1 (in [13])
fcntl(13, F_SETFL, O_RDWR|O_NONBLOCK) = 0
accept(13, {sa_family=AF_INET, sin_port=htons(49952),
sin_addr=inet_addr(10.49.11.54)}, [16]) = 43
fcntl(13, F_SETFL, O_RDWR) = 0
rt_sigaction(SIGCHLD, {SIG_DFL, [CHLD], SA_RESTORER|SA_RESTART,
0x7f6614c20af0}, {SIG_DFL, [CHLD], SA_RESTORER|SA_RESTART,
0x7f6614c20af0}, 8) = 0
getpeername(43, {sa_family=AF_INET, sin_port=htons(49952),
sin_addr=inet_addr(10.49.11.54)}, [16]) = 0
getsockname(43, {sa_family=AF_INET, sin_port=htons(3306),
sin_addr=inet_addr(10.49.11.54)}, [16]) = 0
open(/etc/hosts.allow, O_RDONLY) = 102
fstat(102, {st_mode=S_IFREG|0644, st_size=580, ...}) = 0
mmap(NULL, 4096, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1,
0) = 0x7f66165d
read(102, # /etc/hosts.allow: list of host..., 4096) = 580
read(102, , 4096) = 0
close(102) = 0
munmap(0x7f66165d, 4096)= 0
open(/etc/hosts.deny, O_RDONLY) = 102
fstat(102, {st_mode=S_IFREG|0644, st_size=1339, ...}) = 0
mmap(NULL, 4096, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1,
0) = 0x7f66165d
read(102, # /etc/hosts.deny: list of hosts..., 4096) = 1339
read(102, , 4096) = 0
close(102) = 0
munmap(0x7f66165d, 4096)= 0
getsockname(43, {sa_family=AF_INET, sin_port=htons(3306),
sin_addr=inet_addr(10.49.11.54)}, [16]) = 0
fcntl(43, F_SETFL, O_RDONLY)= 0
fcntl(43, F_GETFL) = 0x2 (flags O_RDWR)
fcntl(43, F_SETFL, O_RDWR|O_NONBLOCK) = 0
setsockopt(43, SOL_IP, IP_TOS, [8], 4) = 0
setsockopt(43, SOL_TCP, TCP_NODELAY, [1], 4) = 0
futex(0x7f661717e244, FUTEX_WAKE_OP_PRIVATE, 1, 1, 0x7f661717e240,
{FUTEX_OP_SET, 0, FUTEX_OP_CMP_GT, 1}) = 1
select(16, [13 15], NULL, NULL, NULL
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql
Re: identifying the cause of old active transactions with locks
On Mon, Sep 3, 2012 at 11:32 AM, Raphael Bauduin rbli...@gmail.com wrote:
Hi,
In the output of
SHOW ENGINE INNODB STATUS \G
I have several old active transactions, each having locked some rows.
All or from the same process id, but with different thread ids. This
is the oldest one, which is 2.5 days old:
---TRANSACTION 0 1532609805, ACTIVE 227995 sec, process no 29517, OS
thread id 140076393318144
5 lock struct(s), heap size 1216, 2 row lock(s), undo log entries 1
MySQL thread id 2573, query id 7268517 10.49.11.54 destockjeans
Trx read view will not see trx with id = 0 1532609807, sees 0 1532609631
An strace on the process shows some activity, so it's not completely
locked (see below).
duh, of course it's the same process, it's the mysqld process
Still looking for more info
Raphaël
How can I debug this further? In a show full process list I don't see
any more related information.
Can I see which query the transaction is executing? How can a lock be
hold so long?
Any help is welcome, thanks in advance!
Raph
strace on process:
--
select(16, [13 15], NULL, NULL, NULL) = 1 (in [13])
fcntl(13, F_SETFL, O_RDWR|O_NONBLOCK) = 0
accept(13, {sa_family=AF_INET, sin_port=htons(49952),
sin_addr=inet_addr(10.49.11.54)}, [16]) = 43
fcntl(13, F_SETFL, O_RDWR) = 0
rt_sigaction(SIGCHLD, {SIG_DFL, [CHLD], SA_RESTORER|SA_RESTART,
0x7f6614c20af0}, {SIG_DFL, [CHLD], SA_RESTORER|SA_RESTART,
0x7f6614c20af0}, 8) = 0
getpeername(43, {sa_family=AF_INET, sin_port=htons(49952),
sin_addr=inet_addr(10.49.11.54)}, [16]) = 0
getsockname(43, {sa_family=AF_INET, sin_port=htons(3306),
sin_addr=inet_addr(10.49.11.54)}, [16]) = 0
open(/etc/hosts.allow, O_RDONLY) = 102
fstat(102, {st_mode=S_IFREG|0644, st_size=580, ...}) = 0
mmap(NULL, 4096, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1,
0) = 0x7f66165d
read(102, # /etc/hosts.allow: list of host..., 4096) = 580
read(102, , 4096) = 0
close(102) = 0
munmap(0x7f66165d, 4096)= 0
open(/etc/hosts.deny, O_RDONLY) = 102
fstat(102, {st_mode=S_IFREG|0644, st_size=1339, ...}) = 0
mmap(NULL, 4096, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1,
0) = 0x7f66165d
read(102, # /etc/hosts.deny: list of hosts..., 4096) = 1339
read(102, , 4096) = 0
close(102) = 0
munmap(0x7f66165d, 4096)= 0
getsockname(43, {sa_family=AF_INET, sin_port=htons(3306),
sin_addr=inet_addr(10.49.11.54)}, [16]) = 0
fcntl(43, F_SETFL, O_RDONLY)= 0
fcntl(43, F_GETFL) = 0x2 (flags O_RDWR)
fcntl(43, F_SETFL, O_RDWR|O_NONBLOCK) = 0
setsockopt(43, SOL_IP, IP_TOS, [8], 4) = 0
setsockopt(43, SOL_TCP, TCP_NODELAY, [1], 4) = 0
futex(0x7f661717e244, FUTEX_WAKE_OP_PRIVATE, 1, 1, 0x7f661717e240,
{FUTEX_OP_SET, 0, FUTEX_OP_CMP_GT, 1}) = 1
select(16, [13 15], NULL, NULL, NULL
--
Web database: http://www.myowndb.com
Free Software Developers Meeting: http://www.fosdem.org
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql
Read_only and InnoDB transactions
Hi all. From the Mysql Documentation: If you attempt to enable read_only while other clients hold explicit table locks or have pending transactions, the attempt blocks until the locks are released and the transactions end. While the attempt to enable read_only is pending, requests by other clients for table locks or to begin transactions also block until read_only has been set. But when I try to set SET GLOBAL read_only = ON; It brake running transaction. -- Viacheslav Biriukov BR
RE: Read_only and InnoDB transactions
What version do you use? David. -Original Message- From: Viacheslav Biriukov [mailto:v.v.biriu...@gmail.com] Sent: Monday, November 28, 2011 7:09 AM To: mysql@lists.mysql.com Subject: Read_only and InnoDB transactions Hi all. From the Mysql Documentation: If you attempt to enable read_only while other clients hold explicit table locks or have pending transactions, the attempt blocks until the locks are released and the transactions end. While the attempt to enable read_only is pending, requests by other clients for table locks or to begin transactions also block until read_only has been set. But when I try to set SET GLOBAL read_only = ON; It brake running transaction. -- Viacheslav Biriukov BR The information contained in this e-mail and any attached documents may be privileged, confidential and protected from disclosure. If you are not the intended recipient you may not read, copy, distribute or use this information. If you have received this communication in error, please notify the sender immediately by replying to this message and then delete it from your system. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Read_only and InnoDB transactions
MySQL Community Server 5.1.59 on the Centos 5.7 2011/11/28 David Lerer dle...@us.univision.com What version do you use? David. -Original Message- From: Viacheslav Biriukov [mailto:v.v.biriu...@gmail.com] Sent: Monday, November 28, 2011 7:09 AM To: mysql@lists.mysql.com Subject: Read_only and InnoDB transactions Hi all. From the Mysql Documentation: If you attempt to enable read_only while other clients hold explicit table locks or have pending transactions, the attempt blocks until the locks are released and the transactions end. While the attempt to enable read_only is pending, requests by other clients for table locks or to begin transactions also block until read_only has been set. But when I try to set SET GLOBAL read_only = ON; It brake running transaction. -- Viacheslav Biriukov BR The information contained in this e-mail and any attached documents may be privileged, confidential and protected from disclosure. If you are not the intended recipient you may not read, copy, distribute or use this information. If you have received this communication in error, please notify the sender immediately by replying to this message and then delete it from your system. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- Viacheslav Biriukov BR http://biriukov.com
RE: Read_only and InnoDB transactions
It may not have an impact on you, but be aware of this severe (imho) bug that caused read_pnly to be ignored regardless of running transactions in version 5.5.8. See bug#58669 and others. We upgraded to 5.5.17 where the bug was fixed. David. From: Viacheslav Biriukov [mailto:v.v.biriu...@gmail.com] Sent: Monday, November 28, 2011 9:20 AM To: David Lerer Cc: mysql@lists.mysql.com Subject: Re: Read_only and InnoDB transactions MySQL Community Server 5.1.59 on the Centos 5.7 2011/11/28 David Lerer dle...@us.univision.commailto:dle...@us.univision.com What version do you use? David. -Original Message- From: Viacheslav Biriukov [mailto:v.v.biriu...@gmail.commailto:v.v.biriu...@gmail.com] Sent: Monday, November 28, 2011 7:09 AM To: mysql@lists.mysql.commailto:mysql@lists.mysql.com Subject: Read_only and InnoDB transactions Hi all. From the Mysql Documentation: If you attempt to enable read_only while other clients hold explicit table locks or have pending transactions, the attempt blocks until the locks are released and the transactions end. While the attempt to enable read_only is pending, requests by other clients for table locks or to begin transactions also block until read_only has been set. But when I try to set SET GLOBAL read_only = ON; It brake running transaction. -- Viacheslav Biriukov BR The information contained in this e-mail and any attached documents may be privileged, confidential and protected from disclosure. If you are not the intended recipient you may not read, copy, distribute or use this information. If you have received this communication in error, please notify the sender immediately by replying to this message and then delete it from your system. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- Viacheslav Biriukov BR http://biriukov.com The information contained in this e-mail and any attached documents may be privileged, confidential and protected from disclosure. If you are not the intended recipient you may not read, copy, distribute or use this information. If you have received this communication in error, please notify the sender immediately by replying to this message and then delete it from your system.
Fwd: Question relating to transactions on innodb tables.
Hi All Just a quick question relating to the use of transactions on innodb tables. We are doing some archiving on some innodb tables, however there seems to be some issues somewhere in the process with data not being updated accordingly. We would like to make use of transactions for this , in order to allow us to roll back on changes if the after checks does not correspond. What I am trying to find out is whether a transaction will cause the locks to be handled as table locks or will it be row level locks? Regards Machiel
Re: Question relating to transactions on innodb tables.
Am 25.11.2011 14:20, schrieb Machiel Richards - Gmail: Just a quick question relating to the use of transactions on innodb tables. We are doing some archiving on some innodb tables, however there seems to be some issues somewhere in the process with data not being updated accordingly. We would like to make use of transactions for this , in order to allow us to roll back on changes if the after checks does not correspond. What I am trying to find out is whether a transaction will cause the locks to be handled as table locks or will it be row level locks? which locks about you are speaking? a transaction is a transaction a lock is a lock signature.asc Description: OpenPGP digital signature
Too many active concurrent transactions
I keep getting this error when we get spikes in traffic. Even though max connections is set to 4096 and file limits are raised, mysql still gives this error. Is there a setting I should be looking for that will allow me to raise whatever causes this error? Thanks Angelo Vargas ang...@at.com
Re: Too many active concurrent transactions
Quoting Angelo Vargas ang...@at.com: I keep getting this error when we get spikes in traffic. Even though max connections is set to 4096 and file limits are raised, mysql still gives this error. Is there a setting I should be looking for that will allow me to raise whatever causes this error? Could be this bug: http://bugs.mysql.com/bug.php?id=26590 Are you running 5.1.47 or 5.5.4 or newer? Andy. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Too many active concurrent transactions
It's actually running 5.1.56 (percona) On Thu, Jun 2, 2011 at 11:27 AM, a.sm...@ukgrid.net wrote: Quoting Angelo Vargas ang...@at.com: I keep getting this error when we get spikes in traffic. Even though max connections is set to 4096 and file limits are raised, mysql still gives this error. Is there a setting I should be looking for that will allow me to raise whatever causes this error? Could be this bug: http://bugs.mysql.com/bug.php?id=26590 Are you running 5.1.47 or 5.5.4 or newer? Andy. -- Angelo Vargas ang...@at.com
Re: CR: add support of interactive transactions for webclients
I can't speak for the MySQL people, but in my view your workaround is the correct way of implementing this. It is not the database's job to keep track of which user wants to keep what session open, and HTTP is stateless by design. Keeping transactions open for relatively long periods of time would be a major load on the database, as it needs to keep track of all the different changesets and consistent views. I'd rather have my database be good at handling data :-) I can see your need, but that's what middleware is for. -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel
CR: add support of interactive transactions for webclients
CR: add support of interactive transactions for webclients Hello, I dont know how to place an idea (CR) for mySQL. I try it that way. At the moment I am implementing an easy-to-use multiuser webclient for database usage. (phpMyAdmin in contrast is a very powerful tool for people with technical background knowledge and I like to use it.) In an multiuser environment the usage of transactions to avoid data loss by access conflicts between different users is mandatory. But: Webserver (e.g. apache) doesnt keep open the connection to mySQL after the script (e.g. PHP) has been executed. mySQL thread is terminated and any open transaction is rolled back. Idea (CR): provide new session-variable KEEP_PROCESS and new command CHANGE_PROCESS. --- scenario (simplified): user1 navigates through database SELECT but NOT for update user1 likes to checkout a row to do some changes: ?PHP mySQL authentification START TRANSACTION SELECT FOR UPDATE SET @@KEEP_PROCESS=1 // process shall NOT be terminated after connection has been closed SELECT CONNECTION_ID() data is displayed in webclient FORM for editing ? row (InnoDB) is locked for user1 and cannot be SELECT FOR UPDATE by any other user user1 likes to write the changes back: ?PHP mySQL authentification CHANGE_PROCESS $connectionId // process shall be overtaken if a lot of conditions are satisfied (see bellow) UPDATE . COMMIT SET @@KEEP_PROCESS=NULL // process shall be terminated after connection has been closed ? --- conditions for CHANGE_PROCESS $connectionId : - user (and host) have to match between currently active (new) process and (old, kept) process with ID=$connectionId - currently active (new) process and (old, kept) process have to have successfully finnished user authentification - (original, last) connection of (old, kept) process has to be terminated before active (new) process can overtake if (original, last) connection has not been terminated then CHANGE_PROCESS is queued until (original, last) connection terminates I have been implementing a workaround (around 500 lines of code) that implements the above described behaviour. But this is implemented in C++ as PHP to MySQL bridge acting towards PHP as simulated mySQL server on port 3307 and acting towards mySQL as simulated PHP client on port 3306. This workaround does satisfy my needs but of course it would be much better to have this functionality implemented directly in mySQL if other users need interactive transactions for webclients too. Please give me feedback. Thank you very much! Herbert Don't get soaked. Take a quick peek at the forecast with the Yahoo! Search weather shortcut. http://tools.search.yahoo.com/shortcuts/#loc_weather
InnoDB / Transactions question
Hello, I'm currently writing a python program that scans some web directories and then stores some information in a local database. I'm playing with using InnoDB for this application as a test bed for using InnoDB in further applications, but I'm running into a couple issues. When I try to write my code and send it to the server, it appears as if the commands don't actually get processed. But if I print out the MySQL queries and copy and paste them into the SQL input via phpmyadmin, the commands work as expected. However, I can wrap my statements in START TRANSACTION and COMMIT statements which then actually processes everything. I'm getting some 2014 Commands out of sync errors when I use transactions and try to perform a second or third set of queries. Is it required to use transaction statements when using InnoDB, and if not, are there any ideas on why my installation doesn't seem to autocommit the queries without it? If I do use transaction statements/InnoDB, is there some common mistake that I'm using or do I need to use a separate connection to the MySQL database for every query I send? I can try to send my code if it is helpful, but I hope that my questions are general enough to not need it. The basic structure of my program is to do the following. Scan a webpage and parse it for information to send to the table named folders. Then scan the folders table for every record that has 0 in the updated field. Then for every record that the last query matched, scan another page relevant to that record and update a second table named observations with the information parsed from that page. This could be thousands of records needing inserting. Then update the folder table to have the updated field equal to 1 for the records I just scanned. I do have a foreign key set in the observations table so that if I delete a record in the folders table, then everything in the observations table that contains that key will be deleted as well. This hopefully will make it so that I don't have to queue up deletion requests for all the observation request records. Thanks in advance! Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: InnoDB / Transactions question
MyISAM does not support transactions so it is inherently in 'autocommit mode' all the time. You will run into this with any transactional database, be it InnoDB, Falcon, or Oracle and DB2 installations for that matter. For many classes of application, avoiding autocommit and explicitly creating and commiting transactions is the only way to keep the data coherent. For lightweight purposes, this can be overkill On Mon, May 17, 2010 at 2:21 PM, Michael Stroh st...@astroh.org wrote: Thanks Michael, it seems that using that works. I have other python scripts that write to other tables on the same installation. The only difference that I can think of is that they are MyISAM type whereas these two are InnoDB. Does the different type require this flag to be set? Or is there be something else going on here? Michael On May 17, 2010, at 2:12 PM, Michael Dykman wrote: The autocommit option can be set globally in your config (there probably is an example in the my.conf file that came with your distro). Alternatively, you may explicitly turn on auto commit on your connection by issuing this command first: set autocommit = 1; As this is a session variable, this only affect the current connection. - michael On Mon, May 17, 2010 at 12:34 PM, Michael Stroh st...@astroh.org wrote: Hello, I'm currently writing a python program that scans some web directories and then stores some information in a local database. I'm playing with using InnoDB for this application as a test bed for using InnoDB in further applications, but I'm running into a couple issues. When I try to write my code and send it to the server, it appears as if the commands don't actually get processed. But if I print out the MySQL queries and copy and paste them into the SQL input via phpmyadmin, the commands work as expected. However, I can wrap my statements in START TRANSACTION and COMMIT statements which then actually processes everything. I'm getting some 2014 Commands out of sync errors when I use transactions and try to perform a second or third set of queries. Is it required to use transaction statements when using InnoDB, and if not, are there any ideas on why my installation doesn't seem to autocommit the queries without it? If I do use transaction statements/InnoDB, is there some common mistake that I'm using or do I need to use a separate connection to the MySQL database for every query I send? I can try to send my code if it is helpful, but I hope that my questions are general enough to not need it. The basic structure of my program is to do the following. Scan a webpage and parse it for information to send to the table named folders. Then scan the folders table for every record that has 0 in the updated field. Then for every record that the last query matched, scan another page relevant to that record and update a second table named observations with the information parsed from that page. This could be thousands of records needing inserting. Then update the folder table to have the updated field equal to 1 for the records I just scanned. I do have a foreign key set in the observations table so that if I delete a record in the folders table, then everything in the observations table that contains that key will be deleted as well. This hopefully will make it so that I don't have to queue up deletion requests for all the observation request records. Thanks in advance! Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=mdyk...@gmail.com -- - michael dykman - mdyk...@gmail.com May the Source be with you. -- - michael dykman - mdyk...@gmail.com May the Source be with you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: InnoDB / Transactions question
Thanks for the clarification. Michael On May 17, 2010, at 2:28 PM, Michael Dykman wrote: MyISAM does not support transactions so it is inherently in 'autocommit mode' all the time. You will run into this with any transactional database, be it InnoDB, Falcon, or Oracle and DB2 installations for that matter. For many classes of application, avoiding autocommit and explicitly creating and commiting transactions is the only way to keep the data coherent. For lightweight purposes, this can be overkill On Mon, May 17, 2010 at 2:21 PM, Michael Stroh st...@astroh.org wrote: Thanks Michael, it seems that using that works. I have other python scripts that write to other tables on the same installation. The only difference that I can think of is that they are MyISAM type whereas these two are InnoDB. Does the different type require this flag to be set? Or is there be something else going on here? Michael On May 17, 2010, at 2:12 PM, Michael Dykman wrote: The autocommit option can be set globally in your config (there probably is an example in the my.conf file that came with your distro). Alternatively, you may explicitly turn on auto commit on your connection by issuing this command first: set autocommit = 1; As this is a session variable, this only affect the current connection. - michael On Mon, May 17, 2010 at 12:34 PM, Michael Stroh st...@astroh.org wrote: Hello, I'm currently writing a python program that scans some web directories and then stores some information in a local database. I'm playing with using InnoDB for this application as a test bed for using InnoDB in further applications, but I'm running into a couple issues. When I try to write my code and send it to the server, it appears as if the commands don't actually get processed. But if I print out the MySQL queries and copy and paste them into the SQL input via phpmyadmin, the commands work as expected. However, I can wrap my statements in START TRANSACTION and COMMIT statements which then actually processes everything. I'm getting some 2014 Commands out of sync errors when I use transactions and try to perform a second or third set of queries. Is it required to use transaction statements when using InnoDB, and if not, are there any ideas on why my installation doesn't seem to autocommit the queries without it? If I do use transaction statements/InnoDB, is there some common mistake that I'm using or do I need to use a separate connection to the MySQL database for every query I send? I can try to send my code if it is helpful, but I hope that my questions are general enough to not need it. The basic structure of my program is to do the following. Scan a webpage and parse it for information to send to the table named folders. Then scan the folders table for every record that has 0 in the updated field. Then for every record that the last query matched, scan another page relevant to that record and update a second table named observations with the information parsed from that page. This could be thousands of records needing inserting. Then update the folder table to have the updated field equal to 1 for the records I just scanned. I do have a foreign key set in the observations table so that if I delete a record in the folders table, then everything in the observations table that contains that key will be deleted as well. This hopefully will make it so that I don't have to queue up deletion requests for all the observation request records. Thanks in advance! Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=mdyk...@gmail.com -- - michael dykman - mdyk...@gmail.com May the Source be with you. -- - michael dykman - mdyk...@gmail.com May the Source be with you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Restarting MySQLD when all transactions are complete
Hi, Probably a simple question for someone who knows :) Is there a way to force MySQLD to restart after it has finished processing all current transactions? I seem to remember from the bit of Oracle work I did in the past we could do a Transactional Restart in Oracle 10g which caused the server to stop accepting new requests and restart when it has processed all current transactions. I now need to do a similar thing with MySQL 5.0, is this possible? It would also be handy if I could get it to do this 'transactional retstart' and when it comes back up force the slave to do the same, but we'll get one working first! Its needed so we can apply updates etc to the box without disrupting database access. Thanks in advance for any help. Regards John -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Restarting MySQLD when all transactions are complete
Read the mysqld man pages about what it does with kill -X signals. One of them may mean graceful stop Or not. If there is one, you'd still have to figure out how to tie that into a re-boot or whatever for updates. Sounds like a perfectly reasonable feature request if you find nothing at all... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Restarting MySQLD when all transactions are complete
Hi, On Wed, Jan 14, 2009 at 3:00 PM, John Daisley john.dais...@mypostoffice.co.uk wrote: Hi, Probably a simple question for someone who knows :) Is there a way to force MySQLD to restart after it has finished processing all current transactions? I seem to remember from the bit of Oracle work I did in the past we could do a Transactional Restart in Oracle 10g which caused the server to stop accepting new requests and restart when it has processed all current transactions. I now need to do a similar thing with MySQL 5.0, is this possible? Right, under Oracle you can do SHUTDOWN TRANSACTIONAL There is no such command available with MySQL but you can do the basically the same thing. Reduce the max_connections variable to 1, this will prevent any new non-super connections. Optionally set the server to read_only to prevent any existing non-super connections from initiating new updates. View the processlist, once all the transactions have completed you can kill the connections and issue a shutdown. It would also be handy if I could get it to do this 'transactional retstart' and when it comes back up force the slave to do the same, but we'll get one working first! Its needed so we can apply updates etc to the box without disrupting database access. Its not exactly what oracle is doing, but at least you can control access. Cheers, Ewen Thanks in advance for any help. Regards John -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=ewen.fort...@gmail.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Transactions with ODBC
Thank you for your response. I am using InnoDB (picked that out of the docs).
Does that mean what I did should have worked? I should not have had 2 rows in
that table after running the commands?
Thanks again...
I would like to wrap my updates top MySQL in transactions.
Use InnoDB tables.
PB
[EMAIL PROTECTED] wrote:
I apologize if you saw this on the MySQL Forums but I have not gotten a
response... Thanks for your help...
I know this is probably a stupid question but I could use a nudge in the
right direction.
I would like to wrap my updates top MySQL in transactions. I am using ODBC as
my means of communication (Visual FoxPro is the front end) and passing SQL
statements off to the server one Execute at a time.
I understand how I could do this with a stored procedure, or possibly even
ADO, but I would prefer to send it one command at a time for various reasons.
When I send the following series of commands through, though, it doesn't work
although each statement tells me that it executed properly:
*-- Set the connection string
ConnectionString = DRIVER={MySQL ODBC 3.51 Driver};SERVER=localhost;
+ ; DATABASE=test; + ; USER=test;+; PASSWORD=test;+; OPTION=3;
lnHandle = SQLSTRINGCONNECT(ConnectionString) Get the connection ?
lnHandle Check the connection to make sure it worked.
lcSQL = START TRANSACTION
? SQLEXEC(lnHandle, lcSQL)
lcSQL = BEGIN
? SQLEXEC(lnHandle, lcSQL)
lcSQL = INSERT INTO n1 (n1) VALUES (1)
? SQLEXEC(lnHandle, lcSQL)
lcSQL = INSERT INTO n1 (n1) VALUES (2)
? SQLEXEC(lnHandle, lcSQL)
lcSQL = ROLLBACK
? SQLEXEC(lnHandle, lcSQL)
Yet, when I query the table after this runs, there are still two records
despite the fact that I asked it to rollback the transaction and that all
commands reported as executing OK.
I am sure I am missing something stupid but I cannot find a decent doc on
this (if you know of one, a reference would be appreciated).
Thanks in advance for your help.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Transactions with ODBC
I apologize if you saw this on the MySQL Forums but I have not gotten a
response... Thanks for your help...
I know this is probably a stupid question but I could use a nudge in the right
direction.
I would like to wrap my updates top MySQL in transactions. I am using ODBC as
my means of communication (Visual FoxPro is the front end) and passing SQL
statements off to the server one Execute at a time.
I understand how I could do this with a stored procedure, or possibly even ADO,
but I would prefer to send it one command at a time for various reasons.
When I send the following series of commands through, though, it doesn't work
although each statement tells me that it executed properly:
*-- Set the connection string
ConnectionString = DRIVER={MySQL ODBC 3.51 Driver};SERVER=localhost; + ;
DATABASE=test; + ;
USER=test;+;
PASSWORD=test;+;
OPTION=3;
lnHandle = SQLSTRINGCONNECT(ConnectionString) Get the connection
? lnHandle Check the connection to make sure it worked.
lcSQL = START TRANSACTION
? SQLEXEC(lnHandle, lcSQL)
lcSQL = BEGIN
? SQLEXEC(lnHandle, lcSQL)
lcSQL = INSERT INTO n1 (n1) VALUES (1)
? SQLEXEC(lnHandle, lcSQL)
lcSQL = INSERT INTO n1 (n1) VALUES (2)
? SQLEXEC(lnHandle, lcSQL)
lcSQL = ROLLBACK
? SQLEXEC(lnHandle, lcSQL)
Yet, when I query the table after this runs, there are still two records
despite the fact that I asked it to rollback the transaction and that all
commands reported as executing OK.
I am sure I am missing something stupid but I cannot find a decent doc on this
(if you know of one, a reference would be appreciated).
Thanks in advance for your help.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Transactions with ODBC
Does that mean what I did should have worked?
I'd want to know (i) the result of executing those cmds in a mysql
client, and (ii) what sqlresult acountinfo contain after each cmd.
PB
[EMAIL PROTECTED] wrote:
Thank you for your response. I am using InnoDB (picked that out of the docs).
Does that mean what I did should have worked? I should not have had 2 rows in
that table after running the commands?
Thanks again...
I would like to wrap my updates top MySQL in transactions.
Use InnoDB tables.
PB
[EMAIL PROTECTED] wrote:
I apologize if you saw this on the MySQL Forums but I have not gotten a
response... Thanks for your help...
I know this is probably a stupid question but I could use a nudge in the right
direction.
I would like to wrap my updates top MySQL in transactions. I am using ODBC as
my means of communication (Visual FoxPro is the front end) and passing SQL
statements off to the server one Execute at a time.
I understand how I could do this with a stored procedure, or possibly even ADO,
but I would prefer to send it one command at a time for various reasons.
When I send the following series of commands through, though, it doesn't work
although each statement tells me that it executed properly:
*-- Set the connection string
ConnectionString = DRIVER={MySQL ODBC 3.51 Driver};SERVER=localhost;
+ ; DATABASE=test; + ; USER=test;+; PASSWORD=test;+; OPTION=3;
lnHandle = SQLSTRINGCONNECT(ConnectionString) Get the connection ?
lnHandle Check the connection to make sure it worked.
lcSQL = START TRANSACTION
? SQLEXEC(lnHandle, lcSQL)
lcSQL = BEGIN
? SQLEXEC(lnHandle, lcSQL)
lcSQL = INSERT INTO n1 (n1) VALUES (1)
? SQLEXEC(lnHandle, lcSQL)
lcSQL = INSERT INTO n1 (n1) VALUES (2)
? SQLEXEC(lnHandle, lcSQL)
lcSQL = ROLLBACK
? SQLEXEC(lnHandle, lcSQL)
Yet, when I query the table after this runs, there are still two records
despite the fact that I asked it to rollback the transaction and that all
commands reported as executing OK.
I am sure I am missing something stupid but I cannot find a decent doc on this
(if you know of one, a reference would be appreciated).
Thanks in advance for your help.
Re: Transactions with ODBC
I would like to wrap my updates top MySQL in transactions.
Use InnoDB tables.
PB
[EMAIL PROTECTED] wrote:
I apologize if you saw this on the MySQL Forums but I have not gotten a
response... Thanks for your help...
I know this is probably a stupid question but I could use a nudge in the right
direction.
I would like to wrap my updates top MySQL in transactions. I am using ODBC as
my means of communication (Visual FoxPro is the front end) and passing SQL
statements off to the server one Execute at a time.
I understand how I could do this with a stored procedure, or possibly even ADO,
but I would prefer to send it one command at a time for various reasons.
When I send the following series of commands through, though, it doesn't work
although each statement tells me that it executed properly:
*-- Set the connection string
ConnectionString = DRIVER={MySQL ODBC 3.51 Driver};SERVER=localhost; + ;
DATABASE=test; + ;
USER=test;+;
PASSWORD=test;+;
OPTION=3;
lnHandle = SQLSTRINGCONNECT(ConnectionString) Get the connection
? lnHandle Check the connection to make sure it worked.
lcSQL = START TRANSACTION
? SQLEXEC(lnHandle, lcSQL)
lcSQL = BEGIN
? SQLEXEC(lnHandle, lcSQL)
lcSQL = INSERT INTO n1 (n1) VALUES (1)
? SQLEXEC(lnHandle, lcSQL)
lcSQL = INSERT INTO n1 (n1) VALUES (2)
? SQLEXEC(lnHandle, lcSQL)
lcSQL = ROLLBACK
? SQLEXEC(lnHandle, lcSQL)
Yet, when I query the table after this runs, there are still two records
despite the fact that I asked it to rollback the transaction and that all
commands reported as executing OK.
I am sure I am missing something stupid but I cannot find a decent doc on this
(if you know of one, a reference would be appreciated).
Thanks in advance for your help.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Transactions and locking
Sequences, if I got that right, need the new value to be stored immediately, i.e. outside of an active transaction. This requires a second connection to the database which probably causes more implementation work for my web application. You mean real sequences? As with Oracle? Then no, you can use the values to whatever purpose you like. An Oracle/InterBase/Firebird/DB2/any decent DBMS Sequence is a separate metadata object with the only purpose to generate sequential numbers. Martijn Tonies Database Workbench - tool for InterBase, Firebird, MySQL, NexusDB, Oracle MS SQL Server Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com I don't need LOCK TABLES anymore now. And at last, I can say that this is indeed not a simple topic as I've thought and maybe I've read most of the related documentation now anyway... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Transactions and locking
Yves,
Did you read this reply I send earlier? I think it does what you
want without needing to lock anything, thus making it portable.
Damn, I found out that I need table locking *and* transactions.
What makes you say that?
BEGIN TRANSACTION
SELECT MAX(id) FROM table
INSERT INTO table (id) VALUES (?)
INSERT INTO othertable (id) VALUES (?)
COMMIT
First I find a new id value, then I do several INSERTs that need to be
atomic, and especially roll back completely if a later one fails.
That Perl module uses the exact technique I described to you with
updates and LAST_INSERT_ID().
AUTO_INCREMENT isn't portable. Now I only support MySQL and SQLite.
But I also did PostgreSQL (until it failed one of the more complex
queries, maybe it comes back one day) and maybe Oracle or whatever will
be compatible, too, so that I then stand there with my AUTO_INCREMENT
and can't use it.
I would suggest the following --
create a table called SEQUENCES:
create table SEQUENCES
( table_name varchar(128/maxlength of tablename) not null primary key,
sequence_value largeint not null) ;
Create a row for each table, eg:
insert into sequences values('CUSTOMERS', 0);
Next, whenever you want to get a new value, do:
select sequence_value as current_value
from sequences
where table_name = 'CUSTOMERS';
Next, do this:
update sequences
set sequence_value = sequence_value + 1
where sequence_value = your current value you just got
and table_name = 'CUSTOMERS'
Now, repeate the above sequence until the UPDATE statement
above says that it's updated 1 row. If it updated 0 rows, it means
someone else did it just before you.
Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, NexusDB, Oracle
MS SQL Server
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]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Transactions and locking
On 14.11.2007 12:50 CE(S)T, Martijn Tonies wrote: Yves, Did you read this reply I send earlier? I think it does what you want without needing to lock anything, thus making it portable. I would suggest the following -- create a table called SEQUENCES: Yes, I've read it and actually put a flag on that message, but then I decided to go for the other flagged message that explained SELECT ... FOR UPDATE. I did some tests with multiple client windows and found that the locking is good enough. I use it for finding new ID values and for telling whether a new value is unique where UNIQUE constraints won't help me (because I want the values to be caseless unique e.g.). SELECT ... FOR UPDATE works fine when I always use the same function to access that table. It is supported by MySQL, PostgreSQL and Oracle. I only need a small workaround for SQLite (which gets the FOR UPDATE stripped off and instead requires the programmer to have started an EXCLUSIVE transaction before; else - Exception). Sequences, if I got that right, need the new value to be stored immediately, i.e. outside of an active transaction. This requires a second connection to the database which probably causes more implementation work for my web application. I don't need LOCK TABLES anymore now. And at last, I can say that this is indeed not a simple topic as I've thought and maybe I've read most of the related documentation now anyway... -- Yves Goergen LonelyPixel [EMAIL PROTECTED] Visit my web laboratory at http://beta.unclassified.de -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Transactions and locking
On 13.11.2007 01:04 CE(S)T, Perrin Harkins wrote: On Nov 12, 2007 6:47 PM, Yves Goergen [EMAIL PROTECTED] wrote: From what I've read about MySQL's table locks and InnoDB, you cannot use LOCK TABLES with transactions. Either of them deactivates the other one. Beginning a transaction unlockes all tables, locking tables ends a transaction. I don't think that's correct. At least that's not how I read this: http://dev.mysql.com/doc/refman/5.0/en/innodb-restrictions.html It sounds like you issue a LOCK TABLES at the beginning of your transaction, and doing a COMMIT unlocks the tables at the end. From that page: Sometimes it would be useful to lock further tables in the course of a transaction. Unfortunately, LOCK TABLES in MySQL performs an implicit COMMIT and UNLOCK TABLES. An InnoDB variant of LOCK TABLES has been planned that can be executed in the middle of a transaction. In any case, you only need to do a table lock long enough to insert a row into your first table. After that, you can release the lock. And when I insert the row in the first table but cannot do so in the second because of some invalid data, I need to also remove the first row again because it doesn't make sense alone. This is what transactions are for. I think I'll go for transactions and check the error code in most cases. Only where a custom check is needed, I'll lock the tables without using a transaction. I'll see how far I get with it. Oh, I see from that page above: All InnoDB locks held by a transaction are released when the transaction is committed or aborted. Thus, it does not make much sense to invoke LOCK TABLES on InnoDB tables in AUTOCOMMIT=1 mode, because the acquired InnoDB table locks would be released immediately. So, it seems that locking tables is *impossible* with InnoDB. Bad. The only thing I can do then is write the data and afterwards count if there are two of them. But this still isn't safe, in concurrency means. Any solution? May be a bug report? -- Yves Goergen LonelyPixel [EMAIL PROTECTED] Visit my web laboratory at http://beta.unclassified.de -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Transactions and locking
(For the record... I missed the mailing list recipient - again!!) On 13.11.2007 00:30 CE(S)T, Perrin Harkins wrote: On Nov 12, 2007 5:58 PM, Yves Goergen [EMAIL PROTECTED] wrote: First I find a new id value, then I do several INSERTs that need to be atomic, and especially roll back completely if a later one fails. If you use a table lock on the first table where you get the ID, you know that ID is safe to use. Using a table lock when you get the ID and then trusting transactions to roll back all the inserts in the event of a later failure should work fine. From what I've read about MySQL's table locks and InnoDB, you cannot use LOCK TABLES with transactions. Either of them deactivates the other one. Beginning a transaction unlockes all tables, locking tables ends a transaction. That Perl module uses the exact technique I described to you with updates and LAST_INSERT_ID(). AUTO_INCREMENT isn't portable. You're misunderstanding. The LAST_INSERT_ID() function doesn't use AUTO_INCREMENT. That's why the perl module uses it. It just copies the value you pass to it and makes that available without another select. I don't understand what you mean. It's not portable to SQLite, but you can use a sequence there instead. To my knowledge, SQLite doesn't support sequences either, only auto_increment. I've began to convert my code to evaluate error codes now, but I see the next problem already: At one place, I insert a row where two columns could potentially violate a uniqueness constraint. With just reading the error code, I can't figure out which of them caused the problem. The error message I can present to the user will be somewhat generic then. (Either this or that of your input already exists. Find out which one. Haha!) Maybe I'll use error codes or table locks depending on the situation. It's all a big hack, but so is databases (and portability) it seems. I'm not sure yet. It's late. -- Yves Goergen LonelyPixel [EMAIL PROTECTED] Visit my web laboratory at http://beta.unclassified.de -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Transactions and locking
Yves Goergen wrote: (For the record... I missed the mailing list recipient - again!!) On 13.11.2007 00:30 CE(S)T, Perrin Harkins wrote: On Nov 12, 2007 5:58 PM, Yves Goergen [EMAIL PROTECTED] wrote: First I find a new id value, then I do several INSERTs that need to be atomic, and especially roll back completely if a later one fails. If you use a table lock on the first table where you get the ID, you know that ID is safe to use. Using a table lock when you get the ID and then trusting transactions to roll back all the inserts in the event of a later failure should work fine. From what I've read about MySQL's table locks and InnoDB, you cannot use LOCK TABLES with transactions. Either of them deactivates the other one. Beginning a transaction unlockes all tables, locking tables ends a transaction. It's more complicated than that. You can use them together, you just have to do it like this: set autocommit = 0; begin; lock tables; -- you are now in a transaction automatically begun by LOCK TABLES . commit; -- your tables are now unlocked. In fact, you *must* use a transaction for LOCK TABLES to be safe, at least in MySQL 5. Even if you're using non-transactional tables. Otherwise, you can get nasty behavior. See http://bugs.mysql.com/bug.php?id=31479 The manual isn't very clear on the interaction between LOCK TABLES and transactions, it's true. But this is what I've found. Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Transactions and locking
On Nov 13, 2007 4:53 AM, Yves Goergen [EMAIL PROTECTED] wrote: From that page: Sometimes it would be useful to lock further tables in the course of a transaction. Unfortunately, LOCK TABLES in MySQL performs an implicit COMMIT and UNLOCK TABLES. An InnoDB variant of LOCK TABLES has been planned that can be executed in the middle of a transaction. I read that as saying that you can't issue a LOCK TABLES and then another LOCK TABLES in the same transaction, because it causes a COMMIT before locking the tables. You can use one LOCK TABLES at the beginning of your transaction with no problems. In any case, you only need to do a table lock long enough to insert a row into your first table. After that, you can release the lock. And when I insert the row in the first table but cannot do so in the second because of some invalid data, I need to also remove the first row again because it doesn't make sense alone. This is what transactions are for. Yes, and you will be in a transaction, and the insert will be rolled back. But maybe UNLOCK TABLES would commit your transaction, in which case, you do need to keep the lock until the transaction is over. Oh, I see from that page above: All InnoDB locks held by a transaction are released when the transaction is committed or aborted. Thus, it does not make much sense to invoke LOCK TABLES on InnoDB tables in AUTOCOMMIT=1 mode, because the acquired InnoDB table locks would be released immediately. So, it seems that locking tables is *impossible* with InnoDB. No, the text you're quoting there says that LOCK TABLES is impossible without a transaction in InnoDB. You plan to use a transaction. - Perrin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Transactions and locking
Yves,
Damn, I found out that I need table locking *and* transactions.
What makes you say that?
BEGIN TRANSACTION
SELECT MAX(id) FROM table
INSERT INTO table (id) VALUES (?)
INSERT INTO othertable (id) VALUES (?)
COMMIT
First I find a new id value, then I do several INSERTs that need to be
atomic, and especially roll back completely if a later one fails.
That Perl module uses the exact technique I described to you with
updates and LAST_INSERT_ID().
AUTO_INCREMENT isn't portable. Now I only support MySQL and SQLite.
But I also did PostgreSQL (until it failed one of the more complex
queries, maybe it comes back one day) and maybe Oracle or whatever will
be compatible, too, so that I then stand there with my AUTO_INCREMENT
and can't use it.
I would suggest the following --
create a table called SEQUENCES:
create table SEQUENCES
( table_name varchar(128/maxlength of tablename) not null primary key,
sequence_value largeint not null) ;
Create a row for each table, eg:
insert into sequences values('CUSTOMERS', 0);
Next, whenever you want to get a new value, do:
select sequence_value as current_value
from sequences
where table_name = 'CUSTOMERS';
Next, do this:
update sequences
set sequence_value = sequence_value + 1
where sequence_value = your current value you just got
and table_name = 'CUSTOMERS'
Now, repeate the above sequence until the UPDATE statement
above says that it's updated 1 row. If it updated 0 rows, it means
someone else did it just before you.
Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, NexusDB, Oracle
MS SQL Server
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: Transactions and locking
Baron Schwartz wrote: Yves Goergen wrote: (For the record... I missed the mailing list recipient - again!!) On 13.11.2007 00:30 CE(S)T, Perrin Harkins wrote: On Nov 12, 2007 5:58 PM, Yves Goergen [EMAIL PROTECTED] wrote: First I find a new id value, then I do several INSERTs that need to be atomic, and especially roll back completely if a later one fails. If you use a table lock on the first table where you get the ID, you know that ID is safe to use. Using a table lock when you get the ID and then trusting transactions to roll back all the inserts in the event of a later failure should work fine. From what I've read about MySQL's table locks and InnoDB, you cannot use LOCK TABLES with transactions. Either of them deactivates the other one. Beginning a transaction unlockes all tables, locking tables ends a transaction. It's more complicated than that. You can use them together, you just have to do it like this: set autocommit = 0; begin; lock tables; -- you are now in a transaction automatically begun by LOCK TABLES . commit; -- your tables are now unlocked. In fact, you *must* use a transaction for LOCK TABLES to be safe, at least in MySQL 5. Even if you're using non-transactional tables. Otherwise, you can get nasty behavior. See http://bugs.mysql.com/bug.php?id=31479 The manual isn't very clear on the interaction between LOCK TABLES and transactions, it's true. But this is what I've found. As your using InnoDB, which has row level locking a SELECT ... FOR UPDATE should work. http://dev.mysql.com/doc/refman/4.1/en/innodb-locking-reads.html e.g. BEGIN TRANSACTION new_id := (SELECT MAX(id) FROM table FOR UPDATE) + 1 -- some more work here INSERT INTO table (id, ...) VALUES (new_id, ...) COMMIT mark -- MARK ADDISON WEB DEVELOPER 200 GRAY'S INN ROAD LONDON WC1X 8XZ UNITED KINGDOM T +44 (0)20 7430 4678 F E [EMAIL PROTECTED] WWW.ITN.CO.UK P Please consider the environment. Do you really need to print this email? Please Note: Any views or opinions are solely those of the author and do not necessarily represent those of Independent Television News Limited unless specifically stated. This email and any files attached are confidential and intended solely for the use of the individual or entity to which they are addressed. If you have received this email in error, please notify [EMAIL PROTECTED] Please note that to ensure regulatory compliance and for the protection of our clients and business, we may monitor and read messages sent to and from our systems. Thank You.
Re: Transactions and locking
On 13.11.2007 16:37 CE(S)T, mark addison wrote: As your using InnoDB, which has row level locking a SELECT ... FOR UPDATE should work. http://dev.mysql.com/doc/refman/4.1/en/innodb-locking-reads.html e.g. BEGIN TRANSACTION new_id := (SELECT MAX(id) FROM table FOR UPDATE) + 1 -- some more work here INSERT INTO table (id, ...) VALUES (new_id, ...) COMMIT Row level locking can only lock rows that exist. Creating new rows (that would have an influence on my MAX value) are still possible and thus row level locking is not what I need. I really need locking an entire table for every other read or write access. -- Yves Goergen LonelyPixel [EMAIL PROTECTED] Visit my web laboratory at http://beta.unclassified.de -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Transactions and locking
Yves Goergen wrote: On 13.11.2007 16:37 CE(S)T, mark addison wrote: As your using InnoDB, which has row level locking a SELECT ... FOR UPDATE should work. http://dev.mysql.com/doc/refman/4.1/en/innodb-locking-reads.html e.g. BEGIN TRANSACTION new_id := (SELECT MAX(id) FROM table FOR UPDATE) + 1 -- some more work here INSERT INTO table (id, ...) VALUES (new_id, ...) COMMIT Row level locking can only lock rows that exist. Creating new rows (that would have an influence on my MAX value) are still possible and thus row level locking is not what I need. I really need locking an entire table for every other read or write access. InnoDB can also lock the gap, which will prevent new rows that would have been returned by the SELECT. The manual has more info on this in the section on consistent reads in InnoDB. FOR UPDATE will do what you need. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Transactions and locking
(Damn I hate those lists that don't come with a Reply-To to the list! Resending...) On 13.11.2007 17:39 CE(S)T, Baron Schwartz wrote: Yves Goergen wrote: Row level locking can only lock rows that exist. Creating new rows (that would have an influence on my MAX value) are still possible and thus row level locking is not what I need. I really need locking an entire table for every other read or write access. InnoDB can also lock the gap, which will prevent new rows that would have been returned by the SELECT. The manual has more info on this in the section on consistent reads in InnoDB. FOR UPDATE will do what you need. I've read about that gap but it sounded like the place [somewhere] before a record where one could insert a new record into. Not sure what that should be. I'm not aware of the InnoDB internals. I know that usually (?) when a new record is stored, it is written to where is enough space for it, linked from a free pointer index. If one is locked, another one might be used. Order doesn't matter in relational databases. -- Yves Goergen LonelyPixel [EMAIL PROTECTED] Visit my web laboratory at http://beta.unclassified.de -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Transactions and locking
Yves Goergen wrote: (Damn I hate those lists that don't come with a Reply-To to the list! Resending...) On 13.11.2007 17:39 CE(S)T, Baron Schwartz wrote: Yves Goergen wrote: Row level locking can only lock rows that exist. Creating new rows (that would have an influence on my MAX value) are still possible and thus row level locking is not what I need. I really need locking an entire table for every other read or write access. InnoDB can also lock the gap, which will prevent new rows that would have been returned by the SELECT. The manual has more info on this in the section on consistent reads in InnoDB. FOR UPDATE will do what you need. I've read about that gap but it sounded like the place [somewhere] before a record where one could insert a new record into. Not sure what that should be. I'm not aware of the InnoDB internals. I know that usually (?) when a new record is stored, it is written to where is enough space for it, linked from a free pointer index. If one is locked, another one might be used. Order doesn't matter in relational databases. Are you thinking that your theoretical knowledge of relational databases must hold the answer to your questions about MySQL? :-) I suggest you read the entire manual section on InnoDB and experiment. Set aside a day for it; there's a lot to learn there. Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Transactions and locking
On Nov 13, 2007 11:39 AM, Baron Schwartz [EMAIL PROTECTED] wrote: InnoDB can also lock the gap, which will prevent new rows that would have been returned by the SELECT. The manual has more info on this in the section on consistent reads in InnoDB. FOR UPDATE will do what you need. Interesting, I didn't think that would work, but the manual does say it will: 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 allows you to lock the non-existence of something in your table. http://dev.mysql.com/doc/refman/5.0/en/innodb-next-key-locking.html There's another suggestion in the comments on that page: INSERT IGNORE and then check the number of rows affected. But, not portable to SQLite. - Perrin P.S. I enjoy your blog, Baron. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Transactions and locking
On 13.11.2007 14:01 CE(S)T, Baron Schwartz wrote: It's more complicated than that. You can use them together, you just have to do it like this: set autocommit = 0; begin; lock tables; -- you are now in a transaction automatically begun by LOCK TABLES . I assume that at this point, any SELECT on the table I have locked should block. But guess what, it doesn't. So it doesn't really lock. commit; -- your tables are now unlocked. In fact, you *must* use a transaction for LOCK TABLES to be safe, at least in MySQL 5. Even if you're using non-transactional tables. Otherwise, you can get nasty behavior. See http://bugs.mysql.com/bug.php?id=31479 -- Yves Goergen LonelyPixel [EMAIL PROTECTED] Visit my web laboratory at http://beta.unclassified.de -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Transactions and locking
Yves Goergen wrote: On 13.11.2007 14:01 CE(S)T, Baron Schwartz wrote: It's more complicated than that. You can use them together, you just have to do it like this: set autocommit = 0; begin; lock tables; -- you are now in a transaction automatically begun by LOCK TABLES . I assume that at this point, any SELECT on the table I have locked should block. But guess what, it doesn't. So it doesn't really lock. What kind of lock are you using? -- cxn 1 set autocommit=0; begin; lock tables t1 write; Query OK, 0 rows affected (6.29 sec) -- cxn 2 set autocommit=0; begin; select * from t1; -- hangs -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Transactions and locking
On 13.11.2007 19:19 CE(S)T, Perrin Harkins wrote: You can use next-key locking to implement a uniqueness check in your application: (...) http://dev.mysql.com/doc/refman/5.0/en/innodb-next-key-locking.html This doesn't help my problem either. It may lock new INSERTs to the table, but it won't lock SELECTs so any other concurrent user can still find its own (same) MAX(id) value and then do an insert. Or any other process can still check for uniqueness and then fail with its insert. The insert of the first process may succeed guaranteed, but the second will fail at a point where it should not. (Actually, it should never fail when I found a new id value / found that my new value is unique.) I have tested the SELECT ... FOR UPDATE and the LOCK TABLES with autocommit = 0 thing. Both don't lock anything (at least not for reading by others which is what I need). May I now conclude that exclusive full table locking is not possible with InnoDB? Or is there another way that I don't know yet? -- Yves Goergen LonelyPixel [EMAIL PROTECTED] Visit my web laboratory at http://beta.unclassified.de -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Transactions and locking
Yves Goergen wrote: On 13.11.2007 19:19 CE(S)T, Perrin Harkins wrote: You can use next-key locking to implement a uniqueness check in your application: (...) http://dev.mysql.com/doc/refman/5.0/en/innodb-next-key-locking.html This doesn't help my problem either. It may lock new INSERTs to the table, but it won't lock SELECTs so any other concurrent user can still It will absolutely lock SELECTs. Are you sure autocommit is set to 0 and you have an open transaction? Are you sure your table is InnoDB? I'm doing this right now: -- cxn 1 mysql set autocommit=0; mysql begin; mysql select * from t1 for update; +--+ | a| +--+ |1 | +--+ 1 row in set (0.00 sec) -- cxn 2 mysql set autocommit=0; mysql begin; mysql select * from t1 for update; ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Transactions and locking
On 13.11.2007 20:43 CE(S)T, Baron Schwartz wrote: Yves Goergen wrote: I assume that at this point, any SELECT on the table I have locked should block. But guess what, it doesn't. So it doesn't really lock. What kind of lock are you using? -- cxn 1 set autocommit=0; begin; lock tables t1 write; Query OK, 0 rows affected (6.29 sec) -- cxn 2 set autocommit=0; begin; select * from t1; -- hangs Not for me. This is what I was doing here. (FYI: MySQL 5.0.45-community-nt, Windows XP, mysql command line client, InnoDB tables) -- Yves Goergen LonelyPixel [EMAIL PROTECTED] Visit my web laboratory at http://beta.unclassified.de -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Transactions and locking
On 13.11.2007 20:57 CE(S)T, Baron Schwartz wrote: It will absolutely lock SELECTs. Are you sure autocommit is set to 0 and you have an open transaction? Are you sure your table is InnoDB? I'm doing this right now: -- cxn 1 mysql set autocommit=0; mysql begin; mysql select * from t1 for update; +--+ | a| +--+ |1 | +--+ 1 row in set (0.00 sec) -- cxn 2 mysql set autocommit=0; mysql begin; mysql select * from t1 for update; Okay, my fault, I didn't use the FOR UPDATE in the second connection. If I do (which is likely to be the case in an application because there, the same code is run concurrently), the second SELECT locks. (The same is true when I select MAX(id) instead of *.) If I don't, it still works. Okay, so we have some table locking, tested, working. Very nice. Thank you for this one. :) ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction -- Yves Goergen LonelyPixel [EMAIL PROTECTED] Visit my web laboratory at http://beta.unclassified.de -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Transactions and locking
On 13.11.2007 20:43 CE(S)T, Baron Schwartz wrote: -- cxn 2 set autocommit=0; begin; select * from t1; -- hangs Delete my last message. I just did it again and now it works, too. I have no idea what I did a couple of minutes ago, but it must have been wrong. Okay. Works, too. I was doubting that it was possible at all. Meanwhile, I found the Oracle reference and it says that locks can never lock queries, so reading a table is possible in any case. Thank you for all your patience you had with me. I think my problems are now solved... I'll see it when I test my application the next time. ;) -- Yves Goergen LonelyPixel [EMAIL PROTECTED] Visit my web laboratory at http://beta.unclassified.de -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Transactions and locking
On Nov 13, 2007 3:32 PM, Yves Goergen [EMAIL PROTECTED] wrote: I found the Oracle reference and it says that locks can never lock queries, so reading a table is possible in any case. No, you just have to use FOR UPDATE and it will block. - Perrin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Transactions and locking
Hi, there's very much information about how transactions and locking works in InnoDB, but maybe there's also a simple and understandable answer to my simple question: When I start a transaction, then find the maximum value of a column and use that + 1 to write a new row into the table, how do transactions protect me from somebody else doing the same thing so that we'd both end up writing a new row with the same value? Here's a description: BEGIN TRANSACTION new_id := (SELECT MAX(id) FROM table) + 1 -- some more work here INSERT INTO table (id, ...) VALUES (new_id, ...) COMMIT What happens if another user does the same in that more work region? (Of course, this example is pseudocode, I really have a PHP application that does this.) -- Yves Goergen LonelyPixel [EMAIL PROTECTED] Visit my web laboratory at http://beta.unclassified.de -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Transactions and locking
Hello Yves, there's very much information about how transactions and locking works in InnoDB, but maybe there's also a simple and understandable answer to my simple question: When I start a transaction, then find the maximum value of a column and use that + 1 to write a new row into the table, how do transactions protect me from somebody else doing the same thing so that we'd both end up writing a new row with the same value? They won't, a constraint protects you from inserting a new row with the same value. Here's a description: BEGIN TRANSACTION new_id := (SELECT MAX(id) FROM table) + 1 -- some more work here INSERT INTO table (id, ...) VALUES (new_id, ...) COMMIT What happens if another user does the same in that more work region? You will end up with the same new_id value, but the primary key constraint - if you have one - will reject the insert. Transactions come in multiple flavors, have a look at the different isolation levels: http://dev.mysql.com/doc/refman/5.0/en/commit.html http://dev.mysql.com/doc/refman/5.0/en/set-transaction.html Depending on your isolation level, for example, you will see new rows in the table between your begin transaction and select max... or between two select max statements. Other isolation levels will give you the same max value when reading the value twice, even though someone else inserted a new row. 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: Transactions and locking
On Nov 12, 2007 1:25 PM, Yves Goergen [EMAIL PROTECTED] wrote: When I start a transaction, then find the maximum value of a column and use that + 1 to write a new row into the table, how do transactions protect me from somebody else doing the same thing so that we'd both end up writing a new row with the same value? Usually you would use an auto_increment column for this. If you want to do it manually, you either need to lock the whole table (to prevent rows from being added) or do the work in one statement (untested): INSERT INTO table (id) values (SELECT MAX(id) + 1 FROM table); You could also keep a separate table that just holds the current ID in a single row and use an update to get it (also untested): UPDATE counter SET id = LAST_INSERT_ID(id + 1); Putting the LAST_INSERT_ID in there lets you grab the id afterward in the same way you get it from an auto_increment, without doing another select. - Perrin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Transactions and locking
Okay, I feel like I need to clarify some things. I do have a UNIQUE INDEX constraint on those columns, so the other user won't actually write the same value another time, but it will fail at a level which it should not. I don't want to use AUTO_INCREMENT because it's not portable. My application should work on MySQL and SQLite (and maybe someday it will also run on many other systems - today, incompatibilities are just too big). Here's another example: SELECT COUNT(*) FROM table WHERE name = ? -- a short delay which is long enough for a concurrent request :( UPDATE table SET name = ? WHERE id = ? I do the first query to find out whether my new name is already assigned. Each name can only appear one time. If I just try and update the row, the query will fail, but I don't know why. All I could do is try and parse the error message, but this will by DBMS-dependent. I'd like to do it in a way so that I can tell the user whether the name was not unique or there was another error. But this case should be detected separately. I'll have a look at those isolation levels though. Maybe it's what I'm looking for. -- Yves Goergen LonelyPixel [EMAIL PROTECTED] Visit my web laboratory at http://beta.unclassified.de -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Transactions and locking
On Nov 12, 2007 2:43 PM, Yves Goergen [EMAIL PROTECTED] wrote: SELECT COUNT(*) FROM table WHERE name = ? -- a short delay which is long enough for a concurrent request :( UPDATE table SET name = ? WHERE id = ? I think that even with SERIALIZABLE isolation level, this won't lock anything if it doesn't match any rows, so someone could do an insert between those statements. I could be wrong about that. The alternative is to lock the table. I'm not sure how that would be done in SQLite, although SQLite works by taking an exclusive write lock on the entire database so it may not be an issue. - Perrin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Transactions and locking
On 12.11.2007 20:43 CE(S)T, Yves Goergen wrote: I'll have a look at those isolation levels though. Maybe it's what I'm looking for. Not quite. But I'm going the LOCK TABLES way now. Locking a single table exclusively for those rare moments seems to be the best solution. I could also implement an abstraction for that, because other DBMS have different syntax to do the same thing. Since I only need these locks for a very short time and a single table with no transaction support, this works fine for me. -- Yves Goergen LonelyPixel [EMAIL PROTECTED] Visit my web laboratory at http://beta.unclassified.de -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Transactions and locking
On 12.11.2007 22:16 CE(S)T, Yves Goergen wrote: Since I only need these locks for a very short time and a single table with no transaction support, this works fine for me. Damn, I found out that I need table locking *and* transactions. I'm lost... Maybe I'm really better off using a sequence (like the one PostgreSQL offers and like it is available as an add-on for Perl [1]). But then again, I need queries outside of a transaction so that the sequence's next number is immediately commited and visible to other users. I have the impression that it all doesn't work. [1] http://search.cpan.org/~adamk/DBIx-MySQLSequence-1.00/lib/DBIx/MySQLSequence.pm -- Yves Goergen LonelyPixel [EMAIL PROTECTED] Visit my web laboratory at http://beta.unclassified.de -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Transactions and locking
On Nov 12, 2007 5:24 PM, Yves Goergen [EMAIL PROTECTED] wrote: Damn, I found out that I need table locking *and* transactions. What makes you say that? Maybe I'm really better off using a sequence (like the one PostgreSQL offers and like it is available as an add-on for Perl [1]). That Perl module uses the exact technique I described to you with updates and LAST_INSERT_ID(). Frankly, doing the insert and checking for an error seems like a pretty reasonable solution to me, since you only have two databases to care about at this point. - Perrin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Transactions and locking
On 12.11.2007 23:31 CE(S)T, Perrin Harkins wrote: On Nov 12, 2007 5:24 PM, Yves Goergen [EMAIL PROTECTED] wrote: Damn, I found out that I need table locking *and* transactions. What makes you say that? BEGIN TRANSACTION SELECT MAX(id) FROM table INSERT INTO table (id) VALUES (?) INSERT INTO othertable (id) VALUES (?) COMMIT First I find a new id value, then I do several INSERTs that need to be atomic, and especially roll back completely if a later one fails. That Perl module uses the exact technique I described to you with updates and LAST_INSERT_ID(). AUTO_INCREMENT isn't portable. Now I only support MySQL and SQLite. But I also did PostgreSQL (until it failed one of the more complex queries, maybe it comes back one day) and maybe Oracle or whatever will be compatible, too, so that I then stand there with my AUTO_INCREMENT and can't use it. Frankly, doing the insert and checking for an error seems like a pretty reasonable solution to me, since you only have two databases to care about at this point. I wonder if I can safely use an error code to determine this error condition and then just retry. Here's an interesting page: http://dev.mysql.com/doc/refman/5.0/en/error-messages-server.html Error: 1022 SQLSTATE: 23000 (ER_DUP_KEY) Message: Can't write; duplicate key in table '%s' No documentation for SQLite. PostgreSQL uses several SQLSTATE codes for this situation. (http://www.postgresql.org/docs/8.2/interactive/errcodes-appendix.html) Something must be wrong with SQL-92 because the two reference tables have no common SQLSTATE values for related error conditions. But generally I think that an SQLSTATE beginning with 23 is close enough for a match. -- Yves Goergen LonelyPixel [EMAIL PROTECTED] Visit my web laboratory at http://beta.unclassified.de -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Transactions and locking
On Nov 12, 2007 5:58 PM, Yves Goergen [EMAIL PROTECTED] wrote: BEGIN TRANSACTION SELECT MAX(id) FROM table INSERT INTO table (id) VALUES (?) INSERT INTO othertable (id) VALUES (?) COMMIT First I find a new id value, then I do several INSERTs that need to be atomic, and especially roll back completely if a later one fails. If you use a table lock on the first table where you get the ID, you know that ID is safe to use. Using a table lock when you get the ID and then trusting transactions to roll back all the inserts in the event of a later failure should work fine. That Perl module uses the exact technique I described to you with updates and LAST_INSERT_ID(). AUTO_INCREMENT isn't portable. You're misunderstanding. The LAST_INSERT_ID() function doesn't use AUTO_INCREMENT. That's why the perl module uses it. It just copies the value you pass to it and makes that available without another select. It's not portable to SQLite, but you can use a sequence there instead. - Perrin -- 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]
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]
MySQL XA Transactions and PHP
Hi, Is it possible using PHP (PDO or native mysql/mysqli drivers) to do XA transactions over two seperate database servers. In my case I need to process remove rows from a production database server only once the slave has processed the rows, and using XA transactions sounds like the better way to do this sort of thing, to prevent loosing any data (if both deletes don't commit then none do). Does anyone have any experience using XA transactions? Regards --jm This message was sent using IMP, the Internet Messaging Program. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Why innodb can give the same X gap lock to two transactions?
Leo, Leo Huang wrote: Heikki, Thanks for you help! I also read the comment in file of innodbase/lock/lock0lock.c in which you said Different transaction can have conflicting locks set on the gap at the same time.. I think that the innodb gap lock's behavior just like an IX lock's behavior. hmm... yes, we could think that when a row is inserted, the inserted needs an 'X-lock on the whole gap'! Then the 'IX-locks' on the gap stop the insertion. When a transaction want to insert a record, it must also get the LOCK_INSERT_INTENTION of the gap, isn't it? Yes, but in the above analogy, an insert is really requesting an 'X lock on the whole gap'. The name LOCK_INSERT_INTENTION is then somewhat misleading. I have read some source code in innodbase/lock/lock0lock.c. But I can't get a clear view of innodb lock modes and lock ways? Can you give me more information? On tables, InnoDB has X, S, IX, IS, and AUTO-INC type locks. InnoDB has basically just X and S type locks on records and gaps. The complexity comes from this: lock0lock.h in 5.0: #define LOCK_ORDINARY 0 /* this flag denotes an ordinary next-key lock in contrast to LOCK_GAP or LOCK_REC_NOT_GAP */ #define LOCK_GAP512 /* this gap bit should be so high that it can be ORed to the other flags; when this bit is set, it means that the lock holds only on the gap before the record; for instance, an x-lock on the gap does not give permission to modify the record on which the bit is set; locks of this type are created when records are removed from the index chain of records */ #define LOCK_REC_NOT_GAP 1024 /* this bit means that the lock is only on the index record and does NOT block inserts to the gap before the index record; this is used in the case when we retrieve a record with a unique key, and is also used in locking plain SELECTs (not part of UPDATE or DELETE) when the user has set the READ COMMITTED isolation level */ #define LOCK_INSERT_INTENTION 2048 /* this bit is set when we place a waiting gap type record lock request in order to let an insert of an index record to wait until there are no conflicting locks by other transactions on the gap; note that this flag remains set when the waiting lock is granted, or if the lock is inherited to a neighboring record */ Unfortunately, the only existing documentation of the details of gap locking is in the source code and comments in lock0lock.c. PS: hi, Eric, Our MySQL version is 4.1.18. Thx! Regards, Heikki -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Why innodb can give the same X gap lock to two transactions?
Leo, 'gap' locks in InnoDB are purely 'inhibitive': they block inserts to the locked gap. But they do not give the holder of the lock any right to insert. Several transactions can own X-lock on the same gap. The reason why we let 'conflicting' locks of different transactions on a gap is that this way there are less lock waits and less deadlocks. In Eric Bergen's example, there was a row with id 6, and there the locks were not gap locks. Best regards, Heikki Oracle Corp./Innobase Oy InnoDB - transactions, row level locking, and foreign keys for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables http://www.innodb.com/order.php . From: leo huang Date: December 12 2006 7:46am Subject: Why innodb can give the same X gap lock to two transactions? Get Plain Text Hi, all, We have an innodb table named test. It has some rows as follow: mysql show create table test; +---+-+ | Table | Create Table | +---+-+ | test | CREATE TABLE `test` ( `id` int(11) NOT NULL default '0', `name` char(20) default NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 | +---+-+ 1 row in set (1.75 sec) mysql select * from test; ++-+ | id | name| ++-+ | 1 | huangjy | | 2 | huangjy | | 3 | huangjy | | 4 | huangjy | | 5 | huangjy | | 7 | huangjy | | 8 | huangjy | | 9 | huangjy | ++-+ 8 rows in set (1.98 sec) When I start two transactions as follow: Transaction 1: mysql begin; Query OK, 0 rows affected (2.51 sec) mysql select * from test where id=6 for update; Empty set (2.17 sec) Transaction 2: mysql begin; Query OK, 0 rows affected (1.56 sec) mysql select * from test where id=6 for update; Empty set (2.27 sec) Now, I use show engine innodb status to see the innodb lock status. The output as follow: TRANSACTIONS Trx id counter 0 5168907 Purge done for trx's n:o 0 5168898 undo n:o 0 0 History list length 2 Total number of lock structs in row lock hash table 2 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 0 0, not started, process no 30668, OS thread id 2484620208 MySQL thread id 2, query id 46 localhost root show engine innodb status ---TRANSACTION 0 5168906, ACTIVE 83 sec, process no 30668, OS thread id 2484820912 2 lock struct(s), heap size 320 MySQL thread id 1, query id 45 localhost root TABLE LOCK table `test/test` trx id 0 5168906 lock mode IX RECORD LOCKS space id 0 page no 54 n bits 80 index `PRIMARY` of table `test/test` trx id 0 5168906 lock_mode X locks gap before rec Record lock, heap no 8 PHYSICAL RECORD: n_fields 4; 1-byte offs TRUE; info bits 0 0: len 4; hex 8007; asc ;; 1: len 6; hex 004eb50c; asc N ;; 2: len 7; hex 008013285c; asc (\;; 3: len 20; hex 6875616e676a7920202020202020202020202020; asc huangjy ;; ---TRANSACTION 0 5168905, ACTIVE 120 sec, process no 30668, OS thread id 2484419504 2 lock struct(s), heap size 320 MySQL thread id 3, query id 43 localhost root TABLE LOCK table `test/test` trx id 0 5168905 lock mode IX RECORD LOCKS space id 0 page no 54 n bits 80 index `PRIMARY` of table `test/test` trx id 0 5168905 lock_mode X locks gap before rec Record lock, heap no 8 PHYSICAL RECORD: n_fields 4; 1-byte offs TRUE; info bits 0 0: len 4; hex 8007; asc ;; 1: len 6; hex 004eb50c; asc N ;; 2: len 7; hex 008013285c; asc (\;; 3: len 20; hex 6875616e676a7920202020202020202020202020; asc huangjy ... As you can see, TRANSACTION 0 5168906 and TRANSACTION 0 5168905 both get the X gap locks on the same record. The MySQL Manual said that X lock is an exclusive lock. Why two transactions can get the same X lock? Any comment will be welcomed? Best regards, Leo Huang -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Why innodb can give the same X gap lock to two transactions?
Heikki, Thanks for you help! I also read the comment in file of innodbase/lock/lock0lock.c in which you said Different transaction can have conflicting locks set on the gap at the same time.. I think that the innodb gap lock's behavior just like an IX lock's behavior. When a transaction want to insert a record, it must also get the LOCK_INSERT_INTENTION of the gap, isn't it? I have read some source code in innodbase/lock/lock0lock.c. But I can't get a clear view of innodb lock modes and lock ways? Can you give me more information? PS: hi, Eric, Our MySQL version is 4.1.18. Thx! -- Best regards, Leo Huang 2006/12/18, Heikki Tuuri [EMAIL PROTECTED]: Leo, 'gap' locks in InnoDB are purely 'inhibitive': they block inserts to the locked gap. But they do not give the holder of the lock any right to insert. Several transactions can own X-lock on the same gap. The reason why we let 'conflicting' locks of different transactions on a gap is that this way there are less lock waits and less deadlocks. In Eric Bergen's example, there was a row with id 6, and there the locks were not gap locks. Best regards, Heikki Oracle Corp./Innobase Oy InnoDB - transactions, row level locking, and foreign keys for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables http://www.innodb.com/order.php . From: leo huang Date: December 12 2006 7:46am Subject: Why innodb can give the same X gap lock to two transactions? Get Plain Text Hi, all, We have an innodb table named test. It has some rows as follow: mysql show create table test; +---+-+ | Table | Create Table | +---+-+ | test | CREATE TABLE `test` ( `id` int(11) NOT NULL default '0', `name` char(20) default NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 | +---+-+ 1 row in set (1.75 sec) mysql select * from test; ++-+ | id | name| ++-+ | 1 | huangjy | | 2 | huangjy | | 3 | huangjy | | 4 | huangjy | | 5 | huangjy | | 7 | huangjy | | 8 | huangjy | | 9 | huangjy | ++-+ 8 rows in set (1.98 sec) When I start two transactions as follow: Transaction 1: mysql begin; Query OK, 0 rows affected (2.51 sec) mysql select * from test where id=6 for update; Empty set (2.17 sec) Transaction 2: mysql begin; Query OK, 0 rows affected (1.56 sec) mysql select * from test where id=6 for update; Empty set (2.27 sec) Now, I use show engine innodb status to see the innodb lock status. The output as follow: TRANSACTIONS Trx id counter 0 5168907 Purge done for trx's n:o 0 5168898 undo n:o 0 0 History list length 2 Total number of lock structs in row lock hash table 2 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 0 0, not started, process no 30668, OS thread id 2484620208 MySQL thread id 2, query id 46 localhost root show engine innodb status ---TRANSACTION 0 5168906, ACTIVE 83 sec, process no 30668, OS thread id 2484820912 2 lock struct(s), heap size 320 MySQL thread id 1, query id 45 localhost root TABLE LOCK table `test/test` trx id 0 5168906 lock mode IX RECORD LOCKS space id 0 page no 54 n bits 80 index `PRIMARY` of table `test/test` trx id 0 5168906 lock_mode X locks gap before rec Record lock, heap no 8 PHYSICAL RECORD: n_fields 4; 1-byte offs TRUE; info bits 0 0: len 4; hex 8007; asc ;; 1: len 6; hex 004eb50c; asc N ;; 2: len 7; hex 008013285c; asc (\;; 3: len 20; hex 6875616e676a7920202020202020202020202020; asc huangjy ;; ---TRANSACTION 0 5168905, ACTIVE 120 sec, process no 30668, OS thread id 2484419504 2 lock struct(s), heap size 320 MySQL thread id 3, query id 43 localhost root TABLE LOCK table `test/test` trx id 0 5168905 lock mode IX RECORD LOCKS space id 0 page no 54 n bits 80 index `PRIMARY` of table `test/test` trx id 0 5168905 lock_mode X locks gap before rec Record lock, heap no 8 PHYSICAL RECORD: n_fields 4; 1-byte offs TRUE; info bits 0 0: len 4; hex 8007; asc ;; 1: len 6; hex 004eb50c; asc N ;; 2: len 7; hex 008013285c; asc (\;; 3: len 20; hex 6875616e676a7920202020202020202020202020; asc huangjy ... As you can see, TRANSACTION 0 5168906 and TRANSACTION 0 5168905 both get the X gap locks on the same record. The MySQL Manual said that X lock is an exclusive lock. Why two transactions can get the same X lock? Any comment will be welcomed? Best regards, Leo Huang -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql
Re: Why innodb can give the same X gap lock to two transactions?
Which version of mysql is this? In 5.1.12 when I run your test the section transaction blocks waiting for the lock (as it should). My show innodb status output is: TRANSACTIONS Trx id counter 0 1300 Purge done for trx's n:o 0 1288 undo n:o 0 0 History list length 1 Total number of lock structs in row lock hash table 2 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 0 1284, not started, process no 23890, OS thread id 1116363696 MySQL thread id 2, query id 25 localhost root ---TRANSACTION 0 1299, ACTIVE 21 sec, process no 23890, OS thread id 1116765104 starting index read mysql tables in use 1, locked 1 LOCK WAIT 2 lock struct(s), heap size 3201 row lock(s) MySQL thread id 5, query id 58 localhost root statistics select * from test where id=6 for update Trx has approximately 1 row locks --- TRX HAS BEEN WAITING 21 SEC FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 0 page no 52 n bits 80 index `PRIMARY` of table `test`.`test` trx id 0 1299 lock_mode X locks rec but not gap waiting Record lock, heap no 7 PHYSICAL RECORD: n_fields 4; compact format; info bits 0 0: len 4; hex 8006; asc ;; 1: len 6; hex 0510; asc ;; 2: len 7; hex 80002d0110; asc - ;; 3: len 20; hex 6875616e676a7920202020202020202020202020; asc huangjy ;; On 12/11/06, leo huang [EMAIL PROTECTED] wrote: Hi, all, We have an innodb table named test. It has some rows as follow: mysql show create table test; +---+-+ | Table | Create Table | +---+-+ | test | CREATE TABLE `test` ( `id` int(11) NOT NULL default '0', `name` char(20) default NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 | +---+-+ 1 row in set (1.75 sec) mysql select * from test; ++-+ | id | name| ++-+ | 1 | huangjy | | 2 | huangjy | | 3 | huangjy | | 4 | huangjy | | 5 | huangjy | | 7 | huangjy | | 8 | huangjy | | 9 | huangjy | ++-+ 8 rows in set (1.98 sec) When I start two transactions as follow: Transaction 1: mysql begin; Query OK, 0 rows affected (2.51 sec) mysql select * from test where id=6 for update; Empty set (2.17 sec) Transaction 2: mysql begin; Query OK, 0 rows affected (1.56 sec) mysql select * from test where id=6 for update; Empty set (2.27 sec) Now, I use show engine innodb status to see the innodb lock status. The output as follow: TRANSACTIONS Trx id counter 0 5168907 Purge done for trx's n:o 0 5168898 undo n:o 0 0 History list length 2 Total number of lock structs in row lock hash table 2 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 0 0, not started, process no 30668, OS thread id 2484620208 MySQL thread id 2, query id 46 localhost root show engine innodb status ---TRANSACTION 0 5168906, ACTIVE 83 sec, process no 30668, OS thread id 2484820912 2 lock struct(s), heap size 320 MySQL thread id 1, query id 45 localhost root TABLE LOCK table `test/test` trx id 0 5168906 lock mode IX RECORD LOCKS space id 0 page no 54 n bits 80 index `PRIMARY` of table `test/test` trx id 0 5168906 lock_mode X locks gap before rec Record lock, heap no 8 PHYSICAL RECORD: n_fields 4; 1-byte offs TRUE; info bits 0 0: len 4; hex 8007; asc ;; 1: len 6; hex 004eb50c; asc N ;; 2: len 7; hex 008013285c; asc (\;; 3: len 20; hex 6875616e676a7920202020202020202020202020; asc huangjy ;; ---TRANSACTION 0 5168905, ACTIVE 120 sec, process no 30668, OS thread id 2484419504 2 lock struct(s), heap size 320 MySQL thread id 3, query id 43 localhost root TABLE LOCK table `test/test` trx id 0 5168905 lock mode IX RECORD LOCKS space id 0 page no 54 n bits 80 index `PRIMARY` of table `test/test` trx id 0 5168905 lock_mode X locks gap before rec Record lock, heap no 8 PHYSICAL RECORD: n_fields 4; 1-byte offs TRUE; info bits 0 0: len 4; hex 8007; asc ;; 1: len 6; hex 004eb50c; asc N ;; 2: len 7; hex 008013285c; asc (\;; 3: len 20; hex 6875616e676a7920202020202020202020202020; asc huangjy ... As you can see, TRANSACTION 0 5168906 and TRANSACTION 0 5168905 both get the X gap locks on the same record. The MySQL Manual said that X lock is an exclusive lock. Why two transactions can get the same X lock? Any comment will be welcomed? Best regards, Leo Huang -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Eric Bergen [EMAIL PROTECTED] http://www.provenscaling.com
Why innodb can give the same X gap lock to two transactions?
Hi, all, We have an innodb table named test. It has some rows as follow: mysql show create table test; +---+-+ | Table | Create Table | +---+-+ | test | CREATE TABLE `test` ( `id` int(11) NOT NULL default '0', `name` char(20) default NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 | +---+-+ 1 row in set (1.75 sec) mysql select * from test; ++-+ | id | name| ++-+ | 1 | huangjy | | 2 | huangjy | | 3 | huangjy | | 4 | huangjy | | 5 | huangjy | | 7 | huangjy | | 8 | huangjy | | 9 | huangjy | ++-+ 8 rows in set (1.98 sec) When I start two transactions as follow: Transaction 1: mysql begin; Query OK, 0 rows affected (2.51 sec) mysql select * from test where id=6 for update; Empty set (2.17 sec) Transaction 2: mysql begin; Query OK, 0 rows affected (1.56 sec) mysql select * from test where id=6 for update; Empty set (2.27 sec) Now, I use show engine innodb status to see the innodb lock status. The output as follow: TRANSACTIONS Trx id counter 0 5168907 Purge done for trx's n:o 0 5168898 undo n:o 0 0 History list length 2 Total number of lock structs in row lock hash table 2 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 0 0, not started, process no 30668, OS thread id 2484620208 MySQL thread id 2, query id 46 localhost root show engine innodb status ---TRANSACTION 0 5168906, ACTIVE 83 sec, process no 30668, OS thread id 2484820912 2 lock struct(s), heap size 320 MySQL thread id 1, query id 45 localhost root TABLE LOCK table `test/test` trx id 0 5168906 lock mode IX RECORD LOCKS space id 0 page no 54 n bits 80 index `PRIMARY` of table `test/test` trx id 0 5168906 lock_mode X locks gap before rec Record lock, heap no 8 PHYSICAL RECORD: n_fields 4; 1-byte offs TRUE; info bits 0 0: len 4; hex 8007; asc ;; 1: len 6; hex 004eb50c; asc N ;; 2: len 7; hex 008013285c; asc (\;; 3: len 20; hex 6875616e676a7920202020202020202020202020; asc huangjy ;; ---TRANSACTION 0 5168905, ACTIVE 120 sec, process no 30668, OS thread id 2484419504 2 lock struct(s), heap size 320 MySQL thread id 3, query id 43 localhost root TABLE LOCK table `test/test` trx id 0 5168905 lock mode IX RECORD LOCKS space id 0 page no 54 n bits 80 index `PRIMARY` of table `test/test` trx id 0 5168905 lock_mode X locks gap before rec Record lock, heap no 8 PHYSICAL RECORD: n_fields 4; 1-byte offs TRUE; info bits 0 0: len 4; hex 8007; asc ;; 1: len 6; hex 004eb50c; asc N ;; 2: len 7; hex 008013285c; asc (\;; 3: len 20; hex 6875616e676a7920202020202020202020202020; asc huangjy ... As you can see, TRANSACTION 0 5168906 and TRANSACTION 0 5168905 both get the X gap locks on the same record. The MySQL Manual said that X lock is an exclusive lock. Why two transactions can get the same X lock? Any comment will be welcomed? Best regards, Leo Huang -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Using MySQL to log and report transactions
Hi: I'm going use MySQL to log transactions so that I can report on them later. Ex: CREATE TABLE statistics ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT , user_id BIGINT UNSIGNED , entity_id INT UNSIGNED , transaction_type INTEGER(2) UNSIGNED , datetime_logged DATETIME , datetime_logged_gmt DATETIME , PRIMARY KEY (id) ); What are the queries that would get me the data organized by: Months Weeks Days Day of Week? I guess I can add the fields: month day year hour dayofyear and that would make reporting a lot easier! How do people typically go about doing this without breaking down the datetime? -James
Re: Using MySQL to log and report transactions
In the last episode (Nov 10), James Tu said: I'm going use MySQL to log transactions so that I can report on them later. Ex: CREATE TABLE statistics ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT , user_id BIGINT UNSIGNED , entity_id INT UNSIGNED , transaction_type INTEGER(2) UNSIGNED , datetime_logged DATETIME , datetime_logged_gmt DATETIME , PRIMARY KEY (id) ); What are the queries that would get me the data organized by: Months Weeks Days Day of Week? Use the date functions (day(), month(), etc) to extract the granularity you want, then group by those. SELECT DAY(datetime_logged), user_id, COUNT(*) FROM statistics GROUP BY DAY(datetime_logged), user_id; http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html -- 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: Transactions in MySQL.
Hi, All locking in *MySQL* is deadlock-free. This is managed by always requesting all needed locks at once at the beginning of a query and always locking the tables in the same order. The --external-locking and --skip-external-locking options explicitly enable and disable external locking. The LOCK TABLES and UNLOCK TABLES statements use internal locking, so you can use them even if external locking is disabled. A call to LOCK TABLES tries to lock any tables you list so that the current thread can work with it without interference. A call to UNLOCK TABLES releases any locks that this thread holds.There are two main types of locks: read and write. When a lock is released, the lock is made available to the threads in the write lock queue, then to the threads in the read lock queue. External locking is a part of configuration and Internal locking is a part of query. Thanks ViSolve DB Team - Original Message - From: [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Monday, September 25, 2006 11:42 AM Subject: Transactions in MySQL. Hi All, How transactions and locking are handled in MYSQL? Is it a part of configuration? Or a query (lock tables, Unlock tables) for each set of queries? Regards, Ravi K The information contained in this electronic message and any attachments to this message are intended for the exclusive use of the addressee(s) and may contain proprietary, confidential or privileged information. If you are not the intended recipient, you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately and destroy all copies of this message and any attachments. WARNING: Computer viruses can be transmitted via email. The recipient should check this email and any attachments for the presence of viruses. The company accepts no liability for any damage caused by any virus transmitted by this email. www.wipro.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Transactions in MySQL.
Hi All, How transactions and locking are handled in MYSQL? Is it a part of configuration? Or a query (lock tables, Unlock tables) for each set of queries? Regards, Ravi K The information contained in this electronic message and any attachments to this message are intended for the exclusive use of the addressee(s) and may contain proprietary, confidential or privileged information. If you are not the intended recipient, you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately and destroy all copies of this message and any attachments. WARNING: Computer viruses can be transmitted via email. The recipient should check this email and any attachments for the presence of viruses. The company accepts no liability for any damage caused by any virus transmitted by this email. www.wipro.com
Re: Transactions in MySQL.
It only works with engines that support transactions like innodb and solid, i strongly sugget to read these links from the manual. http://dev.mysql.com/doc/refman/5.0/en/ansi-diff-transactions.html http://dev.mysql.com/doc/refman/5.0/en/innodb-transaction-model.html Carlos On 9/25/06, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: Hi All, How transactions and locking are handled in MYSQL? Is it a part of configuration? Or a query (lock tables, Unlock tables) for each set of queries? Regards, Ravi K The information contained in this electronic message and any attachments to this message are intended for the exclusive use of the addressee(s) and may contain proprietary, confidential or privileged information. If you are not the intended recipient, you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately and destroy all copies of this message and any attachments. WARNING: Computer viruses can be transmitted via email. The recipient should check this email and any attachments for the presence of viruses. The company accepts no liability for any damage caused by any virus transmitted by this email. www.wipro.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Problem with INNODB transactions
Hi, I am facing a strange problem with INNODB. My application communicates with mysql server using JDBC. I am using mysql 5.1 version. Even after issuing connection.commit() / connection.rollback() commands, still on the sql side the transactions are not getting closed properly. In our application we never try to acquire locks on the same row from different threads. A request is sent only when the previous transaction is closed by issuing commit or rollback. But still the following error is seen : Lock wait timeout exceeded; try restarting transaction. The transaction isolation level used is READ-COMMITTED. We maintain a database connection pool and try to reuse the connections from the pool instead of trying to close and create the connections everytime. In our application there is a continuous database updates happening at a very high rate. Could you please suggest what could be going wrong. Thanks Prasad The information contained in this electronic message and any attachments to this message are intended for the exclusive use of the addressee(s) and may contain proprietary, confidential or privileged information. If you are not the intended recipient, you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately and destroy all copies of this message and any attachments. WARNING: Computer viruses can be transmitted via email. The recipient should check this email and any attachments for the presence of viruses. The company accepts no liability for any damage caused by any virus transmitted by this email. www.wipro.com
RE: Problem with INNODB transactions
What connection pool code are you using? My guess is that the problem is in your code somewhere. Either transactions are not being closed (i.e. because of a connection pool flaw maybe?) or you have two threads trying to update the same row at the same time (in which case this would be expected behavior). R. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 23, 2006 9:31 AM To: mysql@lists.mysql.com Subject: Problem with INNODB transactions Hi, I am facing a strange problem with INNODB. My application communicates with mysql server using JDBC. I am using mysql 5.1 version. Even after issuing connection.commit() / connection.rollback() commands, still on the sql side the transactions are not getting closed properly. In our application we never try to acquire locks on the same row from different threads. A request is sent only when the previous transaction is closed by issuing commit or rollback. But still the following error is seen : Lock wait timeout exceeded; try restarting transaction. The transaction isolation level used is READ-COMMITTED. We maintain a database connection pool and try to reuse the connections from the pool instead of trying to close and create the connections everytime. In our application there is a continuous database updates happening at a very high rate. Could you please suggest what could be going wrong. Thanks Prasad The information contained in this electronic message and any attachments to this message are intended for the exclusive use of the addressee(s) and may contain proprietary, confidential or privileged information. If you are not the intended recipient, you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately and destroy all copies of this message and any attachments. WARNING: Computer viruses can be transmitted via email. The recipient should check this email and any attachments for the presence of viruses. The company accepts no liability for any damage caused by any virus transmitted by this email. www.wipro.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Problem with INNODB transactions
Hi, We maintain a pool ..it is just a collection, where we maintain a list of connections. Regards prasad -Original Message- From: Robert DiFalco [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 23, 2006 11:41 PM To: Prasad Ramisetti (WT01 - Broadband Networks); mysql@lists.mysql.com Subject: RE: Problem with INNODB transactions What connection pool code are you using? My guess is that the problem is in your code somewhere. Either transactions are not being closed (i.e. because of a connection pool flaw maybe?) or you have two threads trying to update the same row at the same time (in which case this would be expected behavior). R. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 23, 2006 9:31 AM To: mysql@lists.mysql.com Subject: Problem with INNODB transactions Hi, I am facing a strange problem with INNODB. My application communicates with mysql server using JDBC. I am using mysql 5.1 version. Even after issuing connection.commit() / connection.rollback() commands, still on the sql side the transactions are not getting closed properly. In our application we never try to acquire locks on the same row from different threads. A request is sent only when the previous transaction is closed by issuing commit or rollback. But still the following error is seen : Lock wait timeout exceeded; try restarting transaction. The transaction isolation level used is READ-COMMITTED. We maintain a database connection pool and try to reuse the connections from the pool instead of trying to close and create the connections everytime. In our application there is a continuous database updates happening at a very high rate. Could you please suggest what could be going wrong. Thanks Prasad The information contained in this electronic message and any attachments to this message are intended for the exclusive use of the addressee(s) and may contain proprietary, confidential or privileged information. If you are not the intended recipient, you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately and destroy all copies of this message and any attachments. WARNING: Computer viruses can be transmitted via email. The recipient should check this email and any attachments for the presence of viruses. The company accepts no liability for any damage caused by any virus transmitted by this email. www.wipro.com The information contained in this electronic message and any attachments to this message are intended for the exclusive use of the addressee(s) and may contain proprietary, confidential or privileged information. If you are not the intended recipient, you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately and destroy all copies of this message and any attachments. WARNING: Computer viruses can be transmitted via email. The recipient should check this email and any attachments for the presence of viruses. The company accepts no liability for any damage caused by any virus transmitted by this email. www.wipro.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Text search + transactions?
Hi, I need fast text searching on a transactional table. Is it possible to use transactions and text-search on a table together yet in any production stable version of mysql? Thanks, LL -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Text search + transactions?
On Mon, 2006-08-07 at 08:49 +0100, Lexington Luthor wrote: Hi, I need fast text searching on a transactional table. Is it possible to use transactions and text-search on a table together yet in any production stable version of mysql? Hi, One of approaches is to have shadow MyISAM table in addition to your Innodb table to perform full text search, which you can update in bulks or via triggers. You also can try sphinx: http://www.sphinxsearch.com/ which works with any storage engine and also much faster. -- Peter Zaitsev, MySQL Performance Expert MySQL Performance Blog - http://www.mysqlperformanceblog.com MySQL Consulting http://www.mysqlperformanceblog.com/mysql-consulting/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Text search + transactions?
Peter Zaitsev wrote: Hi, One of approaches is to have shadow MyISAM table in addition to your Innodb table to perform full text search, which you can update in bulks or via triggers. How can I ensure isolation for queries on this shadow table? The documentation says that the table type does not support transactions. Sorry for being a bit dense here, but what do you mean exactly? Will updates to the shadow table only be visible in their own transaction until commit? Will they be rolled back on transaction abort? You also can try sphinx: http://www.sphinxsearch.com/ which works with any storage engine and also much faster. From what I can tell from the Sphinx docs, it is not transactional either. Not only that, it does not support SQL tables at all, it is simply a wrapper for a search API using the mysql storage engine interface. Can you please elaborate on what you mean? Thanks, LL -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Text search + transactions?
On Mon, 2006-08-07 at 09:49 +0100, Lexington Luthor wrote: Peter Zaitsev wrote: Hi, One of approaches is to have shadow MyISAM table in addition to your Innodb table to perform full text search, which you can update in bulks or via triggers. How can I ensure isolation for queries on this shadow table? The documentation says that the table type does not support transactions. Right. If you want full text search to follow transaction isolation as well you're in trouble.In most search applications however it is not that critical. For some cases some extra filtering (ie by join with Innodb table) can help to ensure row versions match each other. If even that one would not work you would need to implement your own little search engine in SQL (ie creating dictionary table + word list table) - with this one you can make it to follow transaction isolation but it will be very slow. Sorry for being a bit dense here, but what do you mean exactly? Will updates to the shadow table only be visible in their own transaction until commit? Will they be rolled back on transaction abort? No. MyISAM does not support transactions. So you would need to ensure shadow table updates handle it in some way. You also can try sphinx: http://www.sphinxsearch.com/ which works with any storage engine and also much faster. From what I can tell from the Sphinx docs, it is not transactional either. Not only that, it does not support SQL tables at all, it is simply a wrapper for a search API using the mysql storage engine interface. Can you please elaborate on what you mean? Right. I assumed you want to use Innodb tables because you want transactions but you did not really need search queries to follow same isolation mode. -- Peter Zaitsev, MySQL Performance Expert MySQL Performance Blog - http://www.mysqlperformanceblog.com MySQL Consulting http://www.mysqlperformanceblog.com/mysql-consulting/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Text search + transactions?
Peter Zaitsev wrote: Right. If you want full text search to follow transaction isolation as well you're in trouble.In most search applications however it is not that critical. Thats a pity. I will have to port the application to PostgreSQL then. Thanks anyway, LL -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Transactions and testing an Insert statement
Hello,
Me again. Excuse for sending two questions so closely together.
I'm looking through the MySQL manual (as well as searching Google and
the PHP site's MySQL functions) trying to find out how to test an Insert
statement (or any other statement for that matter).
Although I haven't found a direct answer, my searching usually points me
to transactions in InnoDB. Is this what I will need to use to do what I
want?
I'm preparing to import a bunch of data that is coming from an Excel
file from one the vendors we deal with and I want to find out what
manual data preparation I need to do. I'm using PHP's
mysql_real_escape_string as well as some other custom functions but I
need to find out if this is enough.
As I imagine it in my head:
TEST INSERT INTO `table` VALUES ('value', 'value');
And then get back a success or fail error code.
Using MySQL 4.1.
Thank you for your time,
Chris.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Transactions and testing an Insert statement
Chris W. Parker wrote:
Hello,
Me again. Excuse for sending two questions so closely together.
I'm looking through the MySQL manual (as well as searching Google and
the PHP site's MySQL functions) trying to find out how to test an Insert
statement (or any other statement for that matter).
Although I haven't found a direct answer, my searching usually points me
to transactions in InnoDB. Is this what I will need to use to do what I
want?
I'm preparing to import a bunch of data that is coming from an Excel
file from one the vendors we deal with and I want to find out what
manual data preparation I need to do. I'm using PHP's
mysql_real_escape_string as well as some other custom functions but I
need to find out if this is enough.
As I imagine it in my head:
TEST INSERT INTO `table` VALUES ('value', 'value');
You can't test an insert like this but you could do:
begin;
insert into table values(value1, value2);
rollback;
which does mean you need innodb tables. That will rollback any changes
that the insert does, however I'm not sure what happens to an
auto_increment field in that situation (ie does the value get rolled
back or is it left incremented).
The mysql_query function in php returns a resource or failure, so you
could do:
$result = mysql_query($my_query);
if (!$result) {
echo Query ${my_query} failed: . mysql_error() . br/;
} else {
echo Query ${my_query} worked!br/;
}
see php.net/mysql_query for more info.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Why do these transactions show table locks?
Robert, - Original Message - From: Robert DiFalco [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Sunday, February 26, 2006 8:27 PM Subject: RE: Why do these transactions show table locks? It might be important to note that I have a delete trigger on the ELEMS table, also, this INSERT call is being made from a stored procedure. The stored procedure only has one line, this INSERT statement. Could this have anything to do with bug# 16229? http://bugs.mysql.com/bug.php?id=3D16229=20 yes, I think this is: http://bugs.mysql.com/bug.php?id=16229 which is fixed in 5.0.19. I just tested that an ordinary INSERT in 5.0.18 does not use full explicit table locks in InnoDB. Best regards, Heikki Oracle Corp./Innobase Oy InnoDB - transactions, row level locking, and foreign keys for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables http://www.innodb.com/order.php -Original Message- From: Robert DiFalco [mailto:[EMAIL PROTECTED] Sent: Sunday, February 26, 2006 9:33 AM To: mysql@lists.mysql.com; [EMAIL PROTECTED] Subject: Why do these transactions show table locks? My understanding is that innodb should not be using table locks for insert, update, or delete. However, the following transactions are showing table locks. What's up? R. ---TRANSACTION 0 4573, ACTIVE 1 sec, OS thread id 3112 setting table lock mysql tables in use 1, locked 0 LOCK WAIT 2 lock struct(s), heap size 320, undo log entries 250 MySQL thread id 4, query id 566875 squid.tripwire.com 10.150.1.30 root System lock INSERT INTO ELEMS(oid,E_NID,E_RID,E_NMID,E_INSC,E_ET,E_BLID,E_LCID,E_LCNO,E_LCTYP,E_ LCSEV) VALUES(-9223372036854523873,-9223372036854775299,-9223372036854775181,-9 223372036854744697,1,-1,-9223372036854027123,-9223372036854027123,1,0,0) --- TRX HAS BEEN WAITING 1 SEC FOR THIS LOCK TO BE GRANTED: TABLE LOCK table `space/elems` trx id 0 4573 lock mode X waiting -- ---TRANSACTION 0 4569, ACTIVE 1 sec, OS thread id 1708 setting table lock mysql tables in use 1, locked 0 LOCK WAIT 2 lock struct(s), heap size 320, undo log entries 250 MySQL thread id 6, query id 565737 squid.tripwire.com 10.150.1.30 root System lock INSERT INTO ELEMS(oid,E_NID,E_RID,E_NMID,E_INSC,E_ET,E_BLID,E_LCID,E_LCNO,E_LCTYP,E_ LCSEV) VALUES(-9223372036854557373,-9223372036854775352,-9223372036854775181,-9 223372036854601648,1,-1,-9223372036854027623,-9223372036854027623,1,0,0) --- TRX HAS BEEN WAITING 1 SEC FOR THIS LOCK TO BE GRANTED: TABLE LOCK table `space/elems` trx id 0 4569 lock mode X waiting -- ---TRANSACTION 0 4565, ACTIVE 2 sec, OS thread id 4008 setting table lock mysql tables in use 1, locked 0 LOCK WAIT 2 lock struct(s), heap size 320, undo log entries 250 MySQL thread id 5, query id 564870 squid.tripwire.com 10.150.1.30 root System lock INSERT INTO ELEMS(oid,E_NID,E_RID,E_NMID,E_INSC,E_ET,E_BLID,E_LCID,E_LCNO,E_LCTYP,E_ LCSEV) VALUES(-9223372036854578873,-9223372036854775355,-9223372036854775181,-9 223372036854744697,1,-1,-9223372036854033123,-9223372036854033123,1,0,0) --- TRX HAS BEEN WAITING 1 SEC FOR THIS LOCK TO BE GRANTED: TABLE LOCK table `space/elems` trx id 0 4565 lock mode X waiting -- ---TRANSACTION 0 4561, ACTIVE 2 sec, OS thread id 3148 setting table lock mysql tables in use 1, locked 0 LOCK WAIT 2 lock struct(s), heap size 320, undo log entries 250 MySQL thread id 7, query id 563809 squid.tripwire.com 10.150.1.30 root System lock INSERT INTO ELEMS(oid,E_NID,E_RID,E_NMID,E_INSC,E_ET,E_BLID,E_LCID,E_LCNO,E_LCTYP,E_ LCSEV) VALUES(-9223372036854557623,-9223372036854775352,-9223372036854775181,-9 223372036854757305,1,-1,-9223372036854033623,-9223372036854033623,1,0,0) --- TRX HAS BEEN WAITING 2 SEC FOR THIS LOCK TO BE GRANTED: TABLE LOCK table `space/elems` trx id 0 4561 lock mode X waiting -- -- 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]
Why do these transactions show table locks?
My understanding is that innodb should not be using table locks for insert, update, or delete. However, the following transactions are showing table locks. What's up? R. ---TRANSACTION 0 4573, ACTIVE 1 sec, OS thread id 3112 setting table lock mysql tables in use 1, locked 0 LOCK WAIT 2 lock struct(s), heap size 320, undo log entries 250 MySQL thread id 4, query id 566875 squid.tripwire.com 10.150.1.30 root System lock INSERT INTO ELEMS(oid,E_NID,E_RID,E_NMID,E_INSC,E_ET,E_BLID,E_LCID,E_LCNO,E_LCTYP,E_ LCSEV) VALUES(-9223372036854523873,-9223372036854775299,-9223372036854775181,-9 223372036854744697,1,-1,-9223372036854027123,-9223372036854027123,1,0,0) --- TRX HAS BEEN WAITING 1 SEC FOR THIS LOCK TO BE GRANTED: TABLE LOCK table `space/elems` trx id 0 4573 lock mode X waiting -- ---TRANSACTION 0 4569, ACTIVE 1 sec, OS thread id 1708 setting table lock mysql tables in use 1, locked 0 LOCK WAIT 2 lock struct(s), heap size 320, undo log entries 250 MySQL thread id 6, query id 565737 squid.tripwire.com 10.150.1.30 root System lock INSERT INTO ELEMS(oid,E_NID,E_RID,E_NMID,E_INSC,E_ET,E_BLID,E_LCID,E_LCNO,E_LCTYP,E_ LCSEV) VALUES(-9223372036854557373,-9223372036854775352,-9223372036854775181,-9 223372036854601648,1,-1,-9223372036854027623,-9223372036854027623,1,0,0) --- TRX HAS BEEN WAITING 1 SEC FOR THIS LOCK TO BE GRANTED: TABLE LOCK table `space/elems` trx id 0 4569 lock mode X waiting -- ---TRANSACTION 0 4565, ACTIVE 2 sec, OS thread id 4008 setting table lock mysql tables in use 1, locked 0 LOCK WAIT 2 lock struct(s), heap size 320, undo log entries 250 MySQL thread id 5, query id 564870 squid.tripwire.com 10.150.1.30 root System lock INSERT INTO ELEMS(oid,E_NID,E_RID,E_NMID,E_INSC,E_ET,E_BLID,E_LCID,E_LCNO,E_LCTYP,E_ LCSEV) VALUES(-9223372036854578873,-9223372036854775355,-9223372036854775181,-9 223372036854744697,1,-1,-9223372036854033123,-9223372036854033123,1,0,0) --- TRX HAS BEEN WAITING 1 SEC FOR THIS LOCK TO BE GRANTED: TABLE LOCK table `space/elems` trx id 0 4565 lock mode X waiting -- ---TRANSACTION 0 4561, ACTIVE 2 sec, OS thread id 3148 setting table lock mysql tables in use 1, locked 0 LOCK WAIT 2 lock struct(s), heap size 320, undo log entries 250 MySQL thread id 7, query id 563809 squid.tripwire.com 10.150.1.30 root System lock INSERT INTO ELEMS(oid,E_NID,E_RID,E_NMID,E_INSC,E_ET,E_BLID,E_LCID,E_LCNO,E_LCTYP,E_ LCSEV) VALUES(-9223372036854557623,-9223372036854775352,-9223372036854775181,-9 223372036854757305,1,-1,-9223372036854033623,-9223372036854033623,1,0,0) --- TRX HAS BEEN WAITING 2 SEC FOR THIS LOCK TO BE GRANTED: TABLE LOCK table `space/elems` trx id 0 4561 lock mode X waiting -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Why do these transactions show table locks?
It might be important to note that I have a delete trigger on the ELEMS table, also, this INSERT call is being made from a stored procedure. The stored procedure only has one line, this INSERT statement. Could this have anything to do with bug# 16229? http://bugs.mysql.com/bug.php?id=16229 -Original Message- From: Robert DiFalco [mailto:[EMAIL PROTECTED] Sent: Sunday, February 26, 2006 9:33 AM To: mysql@lists.mysql.com; [EMAIL PROTECTED] Subject: Why do these transactions show table locks? My understanding is that innodb should not be using table locks for insert, update, or delete. However, the following transactions are showing table locks. What's up? R. ---TRANSACTION 0 4573, ACTIVE 1 sec, OS thread id 3112 setting table lock mysql tables in use 1, locked 0 LOCK WAIT 2 lock struct(s), heap size 320, undo log entries 250 MySQL thread id 4, query id 566875 squid.tripwire.com 10.150.1.30 root System lock INSERT INTO ELEMS(oid,E_NID,E_RID,E_NMID,E_INSC,E_ET,E_BLID,E_LCID,E_LCNO,E_LCTYP,E_ LCSEV) VALUES(-9223372036854523873,-9223372036854775299,-9223372036854775181,-9 223372036854744697,1,-1,-9223372036854027123,-9223372036854027123,1,0,0) --- TRX HAS BEEN WAITING 1 SEC FOR THIS LOCK TO BE GRANTED: TABLE LOCK table `space/elems` trx id 0 4573 lock mode X waiting -- ---TRANSACTION 0 4569, ACTIVE 1 sec, OS thread id 1708 setting table lock mysql tables in use 1, locked 0 LOCK WAIT 2 lock struct(s), heap size 320, undo log entries 250 MySQL thread id 6, query id 565737 squid.tripwire.com 10.150.1.30 root System lock INSERT INTO ELEMS(oid,E_NID,E_RID,E_NMID,E_INSC,E_ET,E_BLID,E_LCID,E_LCNO,E_LCTYP,E_ LCSEV) VALUES(-9223372036854557373,-9223372036854775352,-9223372036854775181,-9 223372036854601648,1,-1,-9223372036854027623,-9223372036854027623,1,0,0) --- TRX HAS BEEN WAITING 1 SEC FOR THIS LOCK TO BE GRANTED: TABLE LOCK table `space/elems` trx id 0 4569 lock mode X waiting -- ---TRANSACTION 0 4565, ACTIVE 2 sec, OS thread id 4008 setting table lock mysql tables in use 1, locked 0 LOCK WAIT 2 lock struct(s), heap size 320, undo log entries 250 MySQL thread id 5, query id 564870 squid.tripwire.com 10.150.1.30 root System lock INSERT INTO ELEMS(oid,E_NID,E_RID,E_NMID,E_INSC,E_ET,E_BLID,E_LCID,E_LCNO,E_LCTYP,E_ LCSEV) VALUES(-9223372036854578873,-9223372036854775355,-9223372036854775181,-9 223372036854744697,1,-1,-9223372036854033123,-9223372036854033123,1,0,0) --- TRX HAS BEEN WAITING 1 SEC FOR THIS LOCK TO BE GRANTED: TABLE LOCK table `space/elems` trx id 0 4565 lock mode X waiting -- ---TRANSACTION 0 4561, ACTIVE 2 sec, OS thread id 3148 setting table lock mysql tables in use 1, locked 0 LOCK WAIT 2 lock struct(s), heap size 320, undo log entries 250 MySQL thread id 7, query id 563809 squid.tripwire.com 10.150.1.30 root System lock INSERT INTO ELEMS(oid,E_NID,E_RID,E_NMID,E_INSC,E_ET,E_BLID,E_LCID,E_LCNO,E_LCTYP,E_ LCSEV) VALUES(-9223372036854557623,-9223372036854775352,-9223372036854775181,-9 223372036854757305,1,-1,-9223372036854033623,-9223372036854033623,1,0,0) --- TRX HAS BEEN WAITING 2 SEC FOR THIS LOCK TO BE GRANTED: TABLE LOCK table `space/elems` trx id 0 4561 lock mode X waiting -- -- 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]
Best practice: FULLTEXT search InnoDB transactions replication
Hi, We have moved from Mysql4 to MySQL5 and are currently planning our new database schema. In this new approach we would like to move to InnoDB's storage engine for transaction support and still want to use MySQL's FULLTEXT search capabillities. And to make things easy we also want to replicate all our data to a second database. Now I have two different possible approaches: 1. All tables are of type InnoDB, except one table which is of type MyIsam = the FULLTEXT searchable table. This searchable table would have a column with searchable text and a few meta data columns to identify the originating table, column and row. I could use the triggers to index the desired columns on Inserts, updates and deletes and insert the indexed data into the MyIsam search-table. Replication would be straigtforward 1-to-1 replication in this aproach. 2. Still all tables would be of type InnoDB, but instead of creating a single searchable MyIsam table I could also alter the storage engine type for the searchable tables on de replication slave to MyIsam and delegate all searches to the slave. Which even may improve performance, because the master wont be doing full text searches anymore. Replication would be a bit more tricky because of having the InnoDB tables in the master and their corresponding MyIsam tables in the slave. I'm wondering which, if any, of the above aproaches is advisable or if there are other aproaches which are even better. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Two transactions cannot have the AUTO-INC lock on the same table simultaneously ... what happened if it happened
From MySQL 5.0.18 manual == When accessing the auto-increment counter, InnoDB uses a special table level AUTO-INC lock that it keeps to the end of the current SQL statement, not to the end of the transaction. The special lock release strategy was introduced to improve concurrency for inserts into a table containing an AUTO_INCREMENT column. Two transactions cannot have the AUTO-INC lock on the same table simultaneously. What happened if it exist?... Any idea? ---TRANSACTION 0 461360628, ACTIVE 19 sec, process no 734, OS thread id 3136353728 setting auto-inc lock mysql tables in use 1, locked 1 LOCK WAIT 1 lock struct(s), heap size 320 MySQL thread id 28241, query id 13012404 10.1.30.70 root update INSERT INTO sms_9388_telkomsel.t_outgoing_sms (out_sms_time, in_sms_time, out_sms_pin_quiz, out_sms_trx_id, in_sms_message_id , out_sms_dest, out_sms_typePremium, out_sms_msg, out_sms_typeService_telkomsel,out_sms_quiz_keycode) values (NOW(),NOW(),'92 66715','9266715','9266715','6281356059825','TELKOMSEL_LOVE_2000','LOVE: Setelah berkencan, jangan lupa untuk menghubunginya k eesokan hari dan katakan betapa mengesankan pertemuan kemarin dan berharap bertemu lagi.','2000','LOVE') --- TRX HAS BEEN WAITING 19 SEC FOR THIS LOCK TO BE GRANTED: TABLE LOCK table `sms_9388_telkomsel/t_outgoing_sms` trx id 0 461360628 lock mode AUTO-INC waiting -- ---TRANSACTION 0 461360624, ACTIVE 22 sec, process no 734, OS thread id 3133603008 setting auto-inc lock mysql tables in use 1, locked 1 LOCK WAIT 1 lock struct(s), heap size 320 MySQL thread id 28227, query id 13012396 10.1.30.70 root update INSERT INTO sms_9388_telkomsel.t_outgoing_sms (out_sms_time, in_sms_time, out_sms_pin_quiz, out_sms_trx_id, in_sms_message_id , out_sms_dest, out_sms_typePremium, out_sms_msg, out_sms_typeService_telkomsel,out_sms_quiz_keycode) values (NOW(),NOW(),'92 66743','9266743','9266743','6281375092919','TELKOMSEL_LOVE_2000','LOVE: Setelah berkencan, jangan lupa untuk menghubunginya k eesokan hari dan katakan betapa mengesankan pertemuan kemarin dan berharap bertemu lagi.','2000','LOVE') --- TRX HAS BEEN WAITING 22 SEC FOR THIS LOCK TO BE GRANTED: TABLE LOCK table `sms_9388_telkomsel/t_outgoing_sms` trx id 0 461360624 lock mode AUTO-INC waiting -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Transactions (not rolling back on error)
I think it might have something to do with the fact that I'm running the NDB engine. I'm not sure... It doesn't seem to have the same problem on Inno (another transaction-safe engine) I think I need to send MySQL some info so they can try to duplicate it. I'm wondering if anyone else running cluster has had any issues with transactions??? Anyone? I'd _really_ like to get this working since my work-around is in my web application until I can get it working... Good to see you on this list too James! :) Cory. James Harvard wrote: Hi Cory - nice to see a fellow Lasso user here! I've not use transactions myself but I think you might be having a problem with autocommit. http://dev.mysql.com/doc/refman/5.0/en/commit.html HTH, James Harvard At 12:44 am -0700 15/12/05, Cory @ SkyVantage wrote: I have a transaction that is very simple, I need to create records in multiple tables that are related. If any one insert statement fails or throws an error I want to rollback the ENTIRE transaction. I thought that this was the default functionality, but apparently that's not the case here. I'm running MySQL-Cluster 5.0.something. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Transactions (not rolling back on error)
Hi Cory - nice to see a fellow Lasso user here! I've not use transactions myself but I think you might be having a problem with autocommit. http://dev.mysql.com/doc/refman/5.0/en/commit.html HTH, James Harvard At 12:44 am -0700 15/12/05, Cory @ SkyVantage wrote: I have a transaction that is very simple, I need to create records in multiple tables that are related. If any one insert statement fails or throws an error I want to rollback the ENTIRE transaction. I thought that this was the default functionality, but apparently that's not the case here. I'm running MySQL-Cluster 5.0.something. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Transactions (not rolling back on error)
This should be working. You can respond directly to me with schema and SQL that you are using and I can try to reproduce in-house. Thanks, Jonathan Miller Austin, Texas USA Senior Quality Assurance Developer MySQL AB www.mysql.com __ ___ ___ __ / |/ /_ __/ __/ __ \/ / / /|_/ / // /\ \/ /_/ / /__ /_/ /_/\_, /___/\___\_\___/ ___/ www.mysql.com Jumpstart your cluster! http://www.mysql.com/consulting/packaged/cluster.html Get training on clusters http://www.mysql.com/training/courses/mysql_cluster.html All-in-one Enterprise-grade Database, Support and Services http://www.mysql.com/network/ - -Original Message- - From: Cory @ SkyVantage [mailto:[EMAIL PROTECTED] - Sent: Thursday, December 15, 2005 1:45 AM - To: [EMAIL PROTECTED]; mysql@lists.mysql.com - Subject: Transactions (not rolling back on error) - - I have a transaction that is very simple, I need to create records in - multiple tables that are related. If any one insert statement fails or - throws an error I want to rollback the ENTIRE transaction. - - I thought that this was the default functionality, but apparently that's - not the case here. - - I'm running MySQL-Cluster 5.0.something. - - Here's a simplified example.. - - START TRANSACTION; - INSERT INTO; - INSERT INTO; - INSERT INTO; - COMMIT; - - I'm getting data inserted into some tables when others (or one) throw an - error.What am I doing wrong? Do I need to put in a conditional - that checks for an error between each statement? If so, what might that - look like? - - I guess the reason this is so important to me is that Cluster doesn't - enforce Foriegn Key Rules, so it's important that my relationships are - maintained by the transaction properly or else I'll have orphaned - records all over the place... - - Thanks in advance! - - Cory. - - -- - MySQL Cluster Mailing List - For list archives: http://lists.mysql.com/cluster - 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]
Transactions (not rolling back on error)
I have a transaction that is very simple, I need to create records in multiple tables that are related. If any one insert statement fails or throws an error I want to rollback the ENTIRE transaction. I thought that this was the default functionality, but apparently that's not the case here. I'm running MySQL-Cluster 5.0.something. Here's a simplified example.. START TRANSACTION; INSERT INTO; INSERT INTO; INSERT INTO; COMMIT; I'm getting data inserted into some tables when others (or one) throw an error.What am I doing wrong? Do I need to put in a conditional that checks for an error between each statement? If so, what might that look like? I guess the reason this is so important to me is that Cluster doesn't enforce Foriegn Key Rules, so it's important that my relationships are maintained by the transaction properly or else I'll have orphaned records all over the place... Thanks in advance! Cory. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Transactions are not enable
Hi i use a sofware to connect to mysql and it can connect with succes but wanted to share acces to mysql to many poeple and it popup [MUSQL][ODBC 3.51 Driver] Transaction are not enable Is ot a mysql server message or not ( my application ) thank's -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Transactions are not enable
Hello. See: http://dev.mysql.com/doc/mysql/en/transaction.html liofr [EMAIL PROTECTED] wrote: Hi i use a sofware to connect to mysql and it can connect with succes but wanted to share acces to mysql to many poeple and it popup [MUSQL][ODBC 3.51 Driver] Transaction are not enable Is ot a mysql server message or not ( my application ) thank's -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [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: Transactions in Java - JDBC
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 C.F. Scheidecker Antunes wrote: Hello, Can anyone tell me what to do in order to use transactions on a java application? Is there any howto regarding this issu? Thanks, C.F. C.F. First, make sure you're using the InnoDB storage engine (which supports transactions): http://dev.mysql.com/doc/mysql/en/using-innodb-tables.html Then use Connection.setAutoCommit(false) before starting your transaction, and Connection.commit()/Connection.rollback() to commit or rollback transactions: http://java.sun.com/docs/books/tutorial/jdbc/basics/transactions.html -Mark - -- Mark Matthews MySQL AB, Software Development Manager - Connectivity www.mysql.com -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.1 (GNU/Linux) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFC+OdZtvXNTca6JD8RApjfAJ4q5K0N/Tnn5hpQYzJapO8AoDZEFQCfXsE7 laCWxC37BdRNqC3E6qenBzw= =Rab0 -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Transactions in Java - JDBC
Hello, Can anyone tell me what to do in order to use transactions on a java application? Is there any howto regarding this issu? Thanks, C.F. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Can I dissable transactions?
Ben Clewett wrote: Jeff, Thanks for your idea. Deleting data x rows at a time would certainly help, if AUTOCOMMIT=TRUE. But I have no idea how :) I have tried: DELETE FROM very_large_table WHERE delete_flag=1 LIMIT 10; But the LIMIT is not understood (4.1.8). What do you mean? That appears to be valid syntax. Do you get an error or unexpected results? (You might also want to try a smaller limit). Unfortunately my 'delete_flag' is not key. Therefore repeated attempts at deleting small amounts of data is very slow. Right, no index on delete_flag means a table scan. The LIMIT N should cause mysql to stop as soon as it finds (and deletes) the Nth matching row, though. The trick is to set N low enough to get the speed you need. Unfortunately, each run will probably take longer than the one before, as there will be farther to go to find the first match. I expect delete_flag is either 0 or 1. What percent of rows have delete_flag = 1? If small enough, an index on delete_flag would be used for this statement to speed things up. Of course, that's an extra index probably not used for anything else. If you haven't read them already, you may find these pages from the manual helpful: http://dev.mysql.com/doc/mysql/en/delete.html http://dev.mysql.com/doc/mysql/en/delete-speed.html The fastest way seem to be to dump the data, edit the file, and re-insert the data. But you have given my my solution: If I cannot disable transactions, I'll have to work with one of the keys and iterate through that key bit bit. So thanks, I'll go off and give it a go... Ben. Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Can I dissable transactions?
Jeff, Thanks for your idea. Deleting data x rows at a time would certainly help, if AUTOCOMMIT=TRUE. But I have no idea how :) I have tried: DELETE FROM very_large_table WHERE delete_flag=1 LIMIT 10; But the LIMIT is not understood (4.1.8). Unfortunately my 'delete_flag' is not key. Therefore repeated attempts at deleting small amounts of data is very slow. The fastest way seem to be to dump the data, edit the file, and re-insert the data. But you have given my my solution: If I cannot disable transactions, I'll have to work with one of the keys and iterate through that key bit bit. So thanks, I'll go off and give it a go... Ben. Jeff Smelser wrote: On Monday 14 February 2005 03:52 am, Ben Clewett wrote: I am having a lot of problems deleting a large amount of data. Say 20GB from a 40GB table. I seem to get failure quite a lot (due NOT to mysql, but bad hardware), then MySQL roles back the transaction, which takes as many hours and starting the transaction. I also get this a lot: There is a feature of DB2 that can do this.. Its really not always all its cracked up to be.. In this case, it would happily delete, if something goes wrong, your table is now marked bad.. The other 20million rows are now gone.. Is that what you want? What you need to do, is set up a simple script to delete 20,000 rows a time, and commit, just keep doing it till its done.. This way you could do 20,000 rows, wait a bit, do it again. or whatever. If it fails, you only rollback what it was doing during the transaction and you wont have to start all over. Jeff -- Ben Clewett Road Tech Computer System Ltd [EMAIL PROTECTED] http://www.roadrunner.uk.com +44(0)1923 46 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Can I dissable transactions?
Dear MySQL, I am having a lot of problems deleting a large amount of data. Say 20GB from a 40GB table. I seem to get failure quite a lot (due NOT to mysql, but bad hardware), then MySQL roles back the transaction, which takes as many hours and starting the transaction. I also get this a lot: Lock wait timeout exceeded; Try restarting transaction With InnoDB is there a way of completely disabling transactions on a session. So I can delete data without rollback and on bad termination, can restart and continue deleting where I left off? Many thanks for an answer to this problem, Ben Clewett. -- Ben Clewett Road Tech Computer System Ltd [EMAIL PROTECTED] http://www.roadrunner.uk.com +44(0)1923 46 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
