List:General Discussion« Previous MessageNext Message »
From:Baron Schwartz Date:November 13 2007 7:57pm
Subject:Re: Transactions and locking
View as plain text  
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
Thread
Transactions and lockingYves Goergen12 Nov
  • Re: Transactions and lockingMartijn Tonies12 Nov
  • Re: Transactions and lockingPerrin Harkins12 Nov
    • Re: Transactions and lockingYves Goergen12 Nov
      • Re: Transactions and lockingPerrin Harkins12 Nov
      • Re: Transactions and lockingYves Goergen12 Nov
        • Re: Transactions and lockingYves Goergen12 Nov
          • Re: Transactions and lockingPerrin Harkins12 Nov
            • Re: Transactions and lockingYves Goergen12 Nov
              • Re: Transactions and lockingPerrin Harkins13 Nov
                • Re: Transactions and lockingYves Goergen13 Nov
                  • mysqlhotcopyMalka Cymbalista13 Nov
                  • Re: Transactions and lockingBaron Schwartz13 Nov
                    • Re: Transactions and lockingmark addison13 Nov
                      • Re: Transactions and lockingYves Goergen13 Nov
                        • Re: Transactions and lockingBaron Schwartz13 Nov
                          • Re: Transactions and lockingYves Goergen13 Nov
                            • Re: Transactions and lockingBaron Schwartz13 Nov
                          • Re: Transactions and lockingPerrin Harkins13 Nov
                            • Re: Transactions and lockingYves Goergen13 Nov
                              • Re: Transactions and lockingBaron Schwartz13 Nov
                                • Re: Transactions and lockingYves Goergen13 Nov
                    • Re: Transactions and lockingYves Goergen13 Nov
                    • Re: Transactions and lockingYves Goergen13 Nov
                      • Re: Transactions and lockingBaron Schwartz13 Nov
                        • Re: Transactions and lockingYves Goergen13 Nov
                        • Re: Transactions and lockingYves Goergen13 Nov
                          • Re: Transactions and lockingPerrin Harkins13 Nov
  • Re: Transactions and lockingMartijn Tonies13 Nov
  • Re: Transactions and lockingMartijn Tonies14 Nov
    • Re: Transactions and lockingYves Goergen15 Nov
  • Re: Transactions and lockingMartijn Tonies15 Nov
Re: Transactions and lockingYves Goergen13 Nov
  • Re: Transactions and lockingPerrin Harkins13 Nov