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]
