Re: Trans.: Re: Read locks with InnoDB?
In article [EMAIL PROTECTED], Julien ALLANOS [EMAIL PROTECTED] writes: Thanks, I've already read these pages. Here is a test example I've done: 1/ User A: SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED; 2/ User B: SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED; 3/ User A: START TRANSACTION; 4/ User B: START TRANSACTION; 5/ User A: SELECT * FROM MyTable LIMIT = 1; I got a one-row result. 6/ User B: SELECT * FROM MyTable LIMIT = 1; I got the same one-row result, and I need to get the next one instead, because this one is already being treated by user A! I really want to have a SELECT FOR DELETE thing: as soon as a row is read, it is deleted. Is this possible? Not quite, but close. You need a SELECT.. FOR UPDATE. Then in step 6/ above the B connection will get blocked. Now you can delete the row in the A connection and commit. Then B will get unblocked and return the next row. Just two things: 1. Use ORDER BY pkey LIMIT 1 instead of just LIMIT 1. You might get away without the ORDER, but the behavior is undefined. 2. Remember the result of the SELECT.. FRO UPDATE, do the DELETE and COMMIT immediately, and process the row values afterwards if possible. This will cut down the time B gets blocked. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Trans.: Re: Read locks with InnoDB?
Selon Harald Fuchs [EMAIL PROTECTED]: In article [EMAIL PROTECTED], Julien ALLANOS [EMAIL PROTECTED] writes: Thanks, I've already read these pages. Here is a test example I've done: 1/ User A: SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED; 2/ User B: SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED; 3/ User A: START TRANSACTION; 4/ User B: START TRANSACTION; 5/ User A: SELECT * FROM MyTable LIMIT = 1; I got a one-row result. 6/ User B: SELECT * FROM MyTable LIMIT = 1; I got the same one-row result, and I need to get the next one instead, because this one is already being treated by user A! I really want to have a SELECT FOR DELETE thing: as soon as a row is read, it is deleted. Is this possible? Not quite, but close. You need a SELECT.. FOR UPDATE. Then in step 6/ above the B connection will get blocked. Now you can delete the row in the A connection and commit. Then B will get unblocked and return the next row. Just two things: 1. Use ORDER BY pkey LIMIT 1 instead of just LIMIT 1. You might get away without the ORDER, but the behavior is undefined. 2. Remember the result of the SELECT.. FRO UPDATE, do the DELETE and COMMIT immediately, and process the row values afterwards if possible. This will cut down the time B gets blocked. Well, I've tried the following scenario: 1/ User A: SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED; 2/ User B: SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED; 3/ User A: START TRANSACTION; 4/ User B: START TRANSACTION; 5/ User A: SELECT * FROM MyTable LIMIT = 1 ORDER BY pkey FOR UPDATE; I got a one-row result. 6/ User B: SELECT * FROM MyTable LIMIT = 1 ORDER BY pkey FOR UPDATE; I got the same one-row result again! B isn't blocked and gets the same result than A, though A hasn't commited/rollbacked yet. Furthermore, I don't want B to be blocked: I want B to get the next result while A is processing data and deleting the current row. Any solution to this please? Thanks. -- Julien ALLANOS Silicomp-AQL The contents of this email and any attachments are confidential. They are intended for the named recipient(s) only. If you have received this email in error please notify the system manager or the sender immediately and do not disclose the contents to anyone or make copies. * email scanned for viruses, vandals and malicious content * -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Trans.: Re: Read locks with InnoDB?
In article [EMAIL PROTECTED], Julien ALLANOS [EMAIL PROTECTED] writes: Well, I've tried the following scenario: 1/ User A: SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED; 2/ User B: SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED; 3/ User A: START TRANSACTION; 4/ User B: START TRANSACTION; 5/ User A: SELECT * FROM MyTable LIMIT = 1 ORDER BY pkey FOR UPDATE; I got a one-row result. 6/ User B: SELECT * FROM MyTable LIMIT = 1 ORDER BY pkey FOR UPDATE; I got the same one-row result again! B isn't blocked and gets the same result than A, though A hasn't commited/rollbacked yet. That's probably because you changed the transaction isolation level. Just leave it at repeatable-read, and B will block. By the way, your SELECT syntax is wrong. You need SELECT * FROM MyTable ORDER BY pkey LIMIT 1 FOR UPDATE Furthermore, I don't want B to be blocked: I want B to get the next result while A is processing data and deleting the current row. I don't know any way how to get this. That's why I proposed to do the data processing after the DELETE and COMMIT which means that B will get blocked only for a few microseconds or so. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Trans.: Re: Read locks with InnoDB?
Selon Harald Fuchs [EMAIL PROTECTED]: In article [EMAIL PROTECTED], Julien ALLANOS [EMAIL PROTECTED] writes: Well, I've tried the following scenario: 1/ User A: SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED; 2/ User B: SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED; 3/ User A: START TRANSACTION; 4/ User B: START TRANSACTION; 5/ User A: SELECT * FROM MyTable LIMIT = 1 ORDER BY pkey FOR UPDATE; I got a one-row result. 6/ User B: SELECT * FROM MyTable LIMIT = 1 ORDER BY pkey FOR UPDATE; I got the same one-row result again! B isn't blocked and gets the same result than A, though A hasn't commited/rollbacked yet. That's probably because you changed the transaction isolation level. Just leave it at repeatable-read, and B will block. well, i don't know what i did to make it fail but now, in repeatable-read or in read-commited mode, B blocks. By the way, your SELECT syntax is wrong. You need SELECT * FROM MyTable ORDER BY pkey LIMIT 1 FOR UPDATE yeah, mine was obviously wrong. Furthermore, I don't want B to be blocked: I want B to get the next result while A is processing data and deleting the current row. I don't know any way how to get this. That's why I proposed to do the data processing after the DELETE and COMMIT which means that B will get blocked only for a few microseconds or so. Thanks for your advice, i'm going to set the thing up this way, and, eventually re-INSERT (outside the transaction) the record if data processing returns with errors. -- Julien ALLANOS Silicomp-AQL The contents of this email and any attachments are confidential. They are intended for the named recipient(s) only. If you have received this email in error please notify the system manager or the sender immediately and do not disclose the contents to anyone or make copies. * email scanned for viruses, vandals and malicious content * -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Read locks with InnoDB?
Hello, Basically, I have multiple MySQL clients that have to delete entries from a InnoDB table in a concurrent environment. What they do is: 1 - getting the first entry of the table that needs to by deleted, by executing a SELECT ... LIMIT 1 query, 2 - doing stuff with the query result, 3 - deleting the entry from the table if everything went right. However, let's say user A has executed the SELECT query and starts doing stuff. Before A has deleted the entry from the table, user B executes the SELECT query: he gets the same result as A, and starts doing the *same* stuff as A, which is something I need to avoid. How could I achieve this? Is it possible to have blocking SELECT queries, or to set read locks on one row (without locking all the table, to let others users purge the following entries)? Thanks for feedback. -- Julien ALLANOS Silicomp-AQL The contents of this email and any attachments are confidential. They are intended for the named recipient(s) only. If you have received this email in error please notify the system manager or the sender immediately and do not disclose the contents to anyone or make copies. * email scanned for viruses, vandals and malicious content * -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Read locks with InnoDB?
Hello. Transactions are usually used for such purposes, just as 'select ... for update'. You may have interest in: http://dev.mysql.com/doc/mysql/en/InnoDB_locking_reads.html http://dev.mysql.com/doc/mysql/en/Transactional_Commands.html Julien ALLANOS [EMAIL PROTECTED] wrote: Hello, Basically, I have multiple MySQL clients that have to delete entries from= a InnoDB table in a concurrent environment. What they do is: 1 - getting the first entry of the table that needs to by deleted, by exe= cuting a SELECT ... LIMIT 1 query, 2 - doing stuff with the query result, 3 - deleting the entry from the table if everything went right. However, let's say user A has executed the SELECT query and starts doing stuff. Before A has deleted the entry from the table, user B executes th= e SELECT query: he gets the same result as A, and starts doing the *same* s= tuff as A, which is something I need to avoid. How could I achieve this? Is it possible to have blocking SELECT queries,= or to set read locks on one row (without locking all the table, to let others u= sers purge the following entries)? Thanks for feedback. --=20 Julien ALLANOS Silicomp-AQL The contents of this email and any attachments are confidential. They are intended for the named recipient(s) only. If you have received this email in error please notify the system manager or the sender immediately and do not disclose the contents to anyone or make copies. * email scanned for viruses, vandals and malicious content * -- 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]
Trans.: Re: Read locks with InnoDB?
Date : Tue, 9 Nov 2004 18:35:40 +0100 De : Julien ALLANOS [EMAIL PROTECTED] Adresse de retour :Julien ALLANOS [EMAIL PROTECTED] Sujet : Re: Read locks with InnoDB? : Gleb Paharenko [EMAIL PROTECTED] Julien ALLANOS [EMAIL PROTECTED] wrote: Hello, Basically, I have multiple MySQL clients that have to delete entries from= a InnoDB table in a concurrent environment. What they do is: 1 - getting the first entry of the table that needs to by deleted, by exe= cuting a SELECT ... LIMIT 1 query, 2 - doing stuff with the query result, 3 - deleting the entry from the table if everything went right. However, let's say user A has executed the SELECT query and starts doing stuff. Before A has deleted the entry from the table, user B executes th= e SELECT query: he gets the same result as A, and starts doing the *same* s= tuff as A, which is something I need to avoid. How could I achieve this? Is it possible to have blocking SELECT queries,= or to set read locks on one row (without locking all the table, to let others u= sers purge the following entries)? Thanks for feedback. -- Julien ALLANOS Silicomp-AQL Selon Gleb Paharenko [EMAIL PROTECTED]: Hello. Transactions are usually used for such purposes, just as 'select ... for update'. You may have interest in: http://dev.mysql.com/doc/mysql/en/InnoDB_locking_reads.html http://dev.mysql.com/doc/mysql/en/Transactional_Commands.html Thanks, I've already read these pages. Here is a test example I've done: 1/ User A: SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED; 2/ User B: SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED; 3/ User A: START TRANSACTION; 4/ User B: START TRANSACTION; 5/ User A: SELECT * FROM MyTable LIMIT = 1; I got a one-row result. 6/ User B: SELECT * FROM MyTable LIMIT = 1; I got the same one-row result, and I need to get the next one instead, because this one is already being treated by user A! I really want to have a SELECT FOR DELETE thing: as soon as a row is read, it is deleted. Is this possible? -- Julien ALLANOS Silicomp-AQL The contents of this email and any attachments are confidential. They are intended for the named recipient(s) only. If you have received this email in error please notify the system manager or the sender immediately and do not disclose the contents to anyone or make copies. * email scanned for viruses, vandals and malicious content * -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
