Re: Read past Equivalent in MySQL
On Friday 13 May 2005 18:21, Gordon wrote: If you can add a table structure why not create a SELECTED table with REPORT ID and PERSON ID as the 2 field PRIMARY KEY. Then you could INSERT IGNORE into this table [with no BEGN/COMMIT] and the IGNORE would throw away those already selected. Mostly because I've glossed over the exact details of what I'm doing. The table in question is a reporting table storing a list of IDs (multiple per key) and things like purge time etc. When the reporting script runs, it needs to deal with rows that haven't been dealt with before, but not with rows that are currently being processed by another reporting script that's already processing rows not dealt with before. In theory, no two scripts should ever access the table at the same time - they run 15 minutes apart. However, I'd rather not leave it to chance. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Read past Equivalent in MySQL
hi, i followed this thread and really think that this isn't a locking problem, but a table structure problem. if there is a column in table with a boolean flag (dealt yes/no) the queries go just looking for rows where dealt=0 (or no). Mathias Selon Duncan Hill [EMAIL PROTECTED]: On Friday 13 May 2005 18:21, Gordon wrote: If you can add a table structure why not create a SELECTED table with REPORT ID and PERSON ID as the 2 field PRIMARY KEY. Then you could INSERT IGNORE into this table [with no BEGN/COMMIT] and the IGNORE would throw away those already selected. Mostly because I've glossed over the exact details of what I'm doing. The table in question is a reporting table storing a list of IDs (multiple per key) and things like purge time etc. When the reporting script runs, it needs to deal with rows that haven't been dealt with before, but not with rows that are currently being processed by another reporting script that's already processing rows not dealt with before. In theory, no two scripts should ever access the table at the same time - they run 15 minutes apart. However, I'd rather not leave it to chance. -- 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: Read past Equivalent in MySQL
Hi, you're ooking for the opposite of what can be done. One can select in share mode or for update : http://dev.mysql.com/doc/mysql/en/innodb-locking-reads.html this prevents data from being incoherent. If you want skip waiting for locks, you can make for each user a temp table containing the result of the select without for update : - create temporary table tempo select ... from table - update tempo - update table where - drop tempo even then, the table will be locked for the update statement. But you can test it. Mathias Selon Ramesh G [EMAIL PROTECTED]: Hi All, Is there a way by which I can tell the Mysql to ignore the rows that are locked by someone else and take the next available record. The problem is, I have a Query like this: Select * from Table1 where Fld1=2 FOR UPDATE Limit 1 I will have multiple clients running this same query with the same where clause. For the second instance of the query mysql seems to wait till the transaction of the first instance gets completed. This makes this query slow as the time taken for the transaction to complete is somewhere between 1 and 1.5 seconds. Regards, Ramesh G -- 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: Read past Equivalent in MySQL
look also using READ UNCOMMITTED http://dev.mysql.com/doc/mysql/en/innodb-transaction-isolation.html Mathias Selon [EMAIL PROTECTED]: Hi, you're ooking for the opposite of what can be done. One can select in share mode or for update : http://dev.mysql.com/doc/mysql/en/innodb-locking-reads.html this prevents data from being incoherent. If you want skip waiting for locks, you can make for each user a temp table containing the result of the select without for update : - create temporary table tempo select ... from table - update tempo - update table where - drop tempo even then, the table will be locked for the update statement. But you can test it. Mathias Selon Ramesh G [EMAIL PROTECTED]: Hi All, Is there a way by which I can tell the Mysql to ignore the rows that are locked by someone else and take the next available record. The problem is, I have a Query like this: Select * from Table1 where Fld1=2 FOR UPDATE Limit 1 I will have multiple clients running this same query with the same where clause. For the second instance of the query mysql seems to wait till the transaction of the first instance gets completed. This makes this query slow as the time taken for the transaction to complete is somewhere between 1 and 1.5 seconds. Regards, Ramesh G -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Read past Equivalent in MySQL
Hi Mathias, Thanks a lot for your comments. In MS SQL we have something which can achieve this very simply: select Top 1 * from Table1 with (updlock,readpast) I am looking for something exactly similar to this in MySQL. Creating temp tables will not work for me as the no of users for the system could be as high as 500. Regards, Ramesh G On Fri, 13 May 2005 08:19:32 +0200, [EMAIL PROTECTED] wrote: Hi, you're ooking for the opposite of what can be done. One can select in share mode or for update : http://dev.mysql.com/doc/mysql/en/innodb-locking-reads.html this prevents data from being incoherent. If you want skip waiting for locks, you can make for each user a temp table containing the result of the select without for update : - create temporary table tempo select ... from table - update tempo - update table where - drop tempo even then, the table will be locked for the update statement. But you can test it. Mathias Selon Ramesh G [EMAIL PROTECTED]: Hi All, Is there a way by which I can tell the Mysql to ignore the rows that are locked by someone else and take the next available record. The problem is, I have a Query like this: Select * from Table1 where Fld1=2 FOR UPDATE Limit 1 I will have multiple clients running this same query with the same where clause. For the second instance of the query mysql seems to wait till the transaction of the first instance gets completed. This makes this query slow as the time taken for the transaction to complete is somewhere between 1 and 1.5 seconds. Regards, Ramesh G -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Using Opera's revolutionary e-mail client: http://www.opera.com/m2/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Read past Equivalent in MySQL
Ramesh, Thanks a lot for your comments. In MS SQL we have something which can achieve this very simply: select Top 1 * from Table1 with (updlock,readpast) I am looking for something exactly similar to this in MySQL. This actually is a work-around for that bloody MS SQL locking crap. Creating temp tables will not work for me as the no of users for the system could be as high as 500. As far as I know, InnoDB uses multi-versioning to AVOID locks. MS SQL Yukon uses this technique as well and will probably tout it as new! while the technique is way old and first commercially used in InterBase. With regards, Martijn Tonies Database Workbench - tool for InterBase, Firebird, MySQL, Oracle MS SQL Server Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Read past Equivalent in MySQL
look also using READ UNCOMMITTED http://dev.mysql.com/doc/mysql/en/innodb-transaction-isolation.html Read UNCOMMITTED is an abomination and should be avoided. With regards, Martijn Tonies Database Workbench - tool for InterBase, Firebird, MySQL, Oracle MS SQL Server Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Read past Equivalent in MySQL
Yes, that's what i said. He is trying to ovverride data consistency, and read uncommitted is so possible. So why not use it if it solves the problem. else, read uncommitted sould be droped from mysql. But i agree with you Mathias Selon Martijn Tonies [EMAIL PROTECTED]: look also using READ UNCOMMITTED http://dev.mysql.com/doc/mysql/en/innodb-transaction-isolation.html Read UNCOMMITTED is an abomination and should be avoided. With regards, Martijn Tonies Database Workbench - tool for InterBase, Firebird, MySQL, Oracle MS SQL Server Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Read past Equivalent in MySQL
Yes, that's what i said. He is trying to ovverride data consistency, and read uncommitted is so possible. So why not use it if it solves the problem. else, read uncommitted sould be droped from mysql. I don't think he is trying to override data consistency... with MS SQL, read past wil simply skip the locked records and return a resultset without em. With regards, Martijn Tonies Database Workbench - tool for InterBase, Firebird, MySQL, Oracle MS SQL Server Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Read past Equivalent in MySQL
Yes. Martijn is correct. I am trying to skip locked rows and get the next unlocked row available. Reading uncommited data will cause unexpected problems. I don't want to do that. Is there a way to do this? Regards Ramesh On Fri, 13 May 2005 11:54:11 +0200, Martijn Tonies [EMAIL PROTECTED] wrote: Yes, that's what i said. He is trying to ovverride data consistency, and read uncommitted is so possible. So why not use it if it solves the problem. else, read uncommitted sould be droped from mysql. I don't think he is trying to override data consistency... with MS SQL, read past wil simply skip the locked records and return a resultset without em. With regards, Martijn Tonies Database Workbench - tool for InterBase, Firebird, MySQL, Oracle MS SQL Server Upscene Productions http://www.upscene.com -- Using Opera's revolutionary e-mail client: http://www.opera.com/m2/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Read past Equivalent in MySQL
Yes. Martijn is correct. I am trying to skip locked rows and get the next unlocked row available. Reading uncommited data will cause unexpected problems. I don't want to do that. Is there a way to do this? Use InnoDB and you don't _have_ to skip past locked records :-) With regards, Martijn Tonies Database Workbench - tool for InterBase, Firebird, MySQL, Oracle MS SQL Server Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Read past Equivalent in MySQL
I am using InnoDB only. But, it's not skipping locked rows. regards Ramesh On Fri, 13 May 2005 12:11:12 +0200, Martijn Tonies [EMAIL PROTECTED] wrote: Yes. Martijn is correct. I am trying to skip locked rows and get the next unlocked row available. Reading uncommited data will cause unexpected problems. I don't want to do that. Is there a way to do this? Use InnoDB and you don't _have_ to skip past locked records :-) With regards, Martijn Tonies Database Workbench - tool for InterBase, Firebird, MySQL, Oracle MS SQL Server Upscene Productions http://www.upscene.com -- Using Opera's revolutionary e-mail client: http://www.opera.com/m2/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Read past Equivalent in MySQL
On Friday 13 May 2005 11:34, Ramesh G typed: I am using InnoDB only. But, it's not skipping locked rows. Ditto that here. CREATE TABLE `a` ( `b` int(11) NOT NULL auto_increment, PRIMARY KEY (`b`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 select * from a; +---+ | b | +---+ | 1 | | 2 | | 3 | | 4 | | 5 | | 6 | | 7 | +---+ 1-mysql begin; Query OK, 0 rows affected (0.00 sec) 1-mysql select * from a where b 4 for update; +---+ | b | +---+ | 1 | | 2 | | 3 | +---+ 3 rows in set (0.00 sec) 2-mysql select * from a; +---+ | b | +---+ | 1 | | 2 | | 3 | | 4 | | 5 | | 6 | | 7 | +---+ 7 rows in set (0.01 sec) 2-mysql select * from a for update; ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction Even though session 1 has indicated it wants update rights on everything less than 4, session 2 is still able to see those records. If session 1 updates a row, session 2 doesn't see the update yet, which is correct, but not the behaviour I need (and I surmise not the behaviour the OP needs). In the end, I had to add a flag to my rows that I toggle when right after select for update in the transaction. This means that other sessions won't see the rows because they check for the toggle being set. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Read past Equivalent in MySQL
I am using InnoDB only. But, it's not skipping locked rows. Ditto that here. Actually, I consider that a good thing... What's the point in leaving out rows that have not been modified yet but are about to be updated? The transaction that has the rows locked might as well be rolled back. The data that is visible is the data as available at that moment. With regards, Martijn Tonies Database Workbench - tool for InterBase, Firebird, MySQL, Oracle MS SQL Server Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Read past Equivalent in MySQL
I agree. It sounds like you could use plain repeatable read isolation transactions. If someone else is modifying those rows you get an older version from when your transaction was started. No need for skipping anything. Martijn Tonies wrote: I am using InnoDB only. But, it's not skipping locked rows. Ditto that here. Actually, I consider that a good thing... What's the point in leaving out rows that have not been modified yet but are about to be updated? The transaction that has the rows locked might as well be rolled back. The data that is visible is the data as available at that moment. With regards, Martijn Tonies Database Workbench - tool for InterBase, Firebird, MySQL, Oracle MS SQL Server Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Read past Equivalent in MySQL
On Friday 13 May 2005 16:19, Eric Bergen typed: I agree. It sounds like you could use plain repeatable read isolation transactions. If someone else is modifying those rows you get an older version from when your transaction was started. No need for skipping anything. In the case of what I'm programming, I need to be able to skip records that have been selected by another instance of the program (don't want to send the same person 40 reports with the same content). Hence why I use flags on the table to say 'in progress, don't read me'. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Read past Equivalent in MySQL
If you can add a table structure why not create a SELECTED table with REPORT ID and PERSON ID as the 2 field PRIMARY KEY. Then you could INSERT IGNORE into this table [with no BEGN/COMMIT] and the IGNORE would throw away those already selected. -Original Message- From: Duncan Hill [mailto:[EMAIL PROTECTED] Sent: Friday, May 13, 2005 10:25 AM To: mysql@lists.mysql.com Subject: Re: Read past Equivalent in MySQL On Friday 13 May 2005 16:19, Eric Bergen typed: I agree. It sounds like you could use plain repeatable read isolation transactions. If someone else is modifying those rows you get an older version from when your transaction was started. No need for skipping anything. In the case of what I'm programming, I need to be able to skip records that have been selected by another instance of the program (don't want to send the same person 40 reports with the same content). Hence why I use flags on the table to say 'in progress, don't read me'. -- 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]
Read past Equivalent in MySQL
Hi All, Is there a way by which I can tell the Mysql to ignore the rows that are locked by someone else and take the next available record. The problem is, I have a Query like this: Select * from Table1 where Fld1=2 FOR UPDATE Limit 1 I will have multiple clients running this same query with the same where clause. For the second instance of the query mysql seems to wait till the transaction of the first instance gets completed. This makes this query slow as the time taken for the transaction to complete is somewhere between 1 and 1.5 seconds. Regards, Ramesh G -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
