Announcement

Announcement Module
Collapse
No announcement yet.

How to Test "Select for Update" in MySQL

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • How to Test "Select for Update" in MySQL

    I am performing
    SELECT ... FOR UPDATE
    or row level locking with InnoDB tables.

    My intention is to only one request can read the same row. So if two users make request for the same data as the same time. Only one of them get data, who fires the query first.

    But How can i test that locking is placed or not. as I am testing it by retrieving the same data at same time and both users getting the data.

    Note: My tables are InnoDB, My query executes in transaction, my query as below:


    SELECT * FROM table_name WHERE cond FOR UPDATE;


    Any other thing I have to check for this to make work?

    As far as I know, When we use FOR UPDATE in SELECT query then selected rows will get locked until transaction commits or Roll back and no other mysql thread can even select those rows. Is that correct?

  • #2
    You need the following to be true:

    1) it's an InnoDB table
    2) you are in a transaction
    3) you do not have AUTOCOMMIT set.

    Check all three of these very carefully, even if you think you know. (Your InnoDB table might be MyISAM, for example.)

    Comment


    • #3
      Ok, have checked all things twice.

      I am using PDO for my DB related work and for starting the transaction I am using the start transaction method which is provided by the PDO.


      PDO::beginTransaction()


      For the third point, You mean to say that I have to set AUTOCOMMIT to 0 in query even if I have started the transaction with above method?

      Comment


      • #4
        Yes, autocommit has to be 0.

        Comment


        • #5
          So, Starting the transaction will not set AUTOCOMMIT to 0?

          http://php.net/manual/en/pdo.begintransaction.php

          As per the above link, it will Turns off autocommit mode. Then now what is the correct one?

          and can you please show me one example?

          Comment


          • #6
            Your application and all of the software between your code and MySQL is too complex for me to dig into on this forum, but we can simplify: make a simple test case script that connects, starts a transaction, and does the SELECT FOR UPDATE. Immediately afterwards, and before committing, sleep for 60 seconds or so. Now run two instances of this script and see what happens. While this is in progress, log into the server and run SHOW ENGINE INNODB STATUS and you should see one active transaction with locks, and another active transaction in LOCK WAIT status. If you don't, then somewhere, something is wrong. But I can pretty much promise it's not inside the MySQL server where the problem is happening

            Comment


            • #7
              I am not getting any thing with "SHOW ENGINE INNODB STATUS",

              can you please elaborate how can I check this?

              Comment


              • #8
                What are you getting? An error message? Nothing at all?

                What is the result of the following queries?

                SHOW VARIABLES LIKE 'have_innodb';
                SHOW VARIABLES LIKE 'version';

                Comment


                • #9
                  Here is what I have got the output of the "SHOW ENGINE INNODB STATUS"


                  '=====================================111130 19:38:48 INNODB MONITOR OUTPUT=====================================Per second averages calculated from the last 29 seconds-----------------BACKGROUND THREAD-----------------srv_master_thread loops: 7150 1_second, 7150 sleeps, 629 10_second, 1109 background, 1109 flushsrv_master_thread log flush and writes: 7253----------SEMAPHORES----------OS WAIT ARRAY INFO: reservation count 3718, signal count 3676Mutex spin waits 2083, rounds 9050, OS waits 74RW-shared spins 2024, rounds 60321, OS waits 2002RW-excl spins 1642, rounds 49333, OS waits 1639Spin rounds per wait: 4.34 mutex, 29.80 RW-shared, 30.04 RW-excl------------TRANSACTIONS------------Trx id counter 235B571Purge done for trx''s n < 235B55B undo n < 0History list length 1807LIST OF TRANSACTIONS FOR EACH SESSION:---TRANSACTION 235AE18, not started, OS thread id 2652MySQL thread id 47, query id 139856 Win7-PC 192.168.101.111 rootSHOW ENGINE INNODB STATUS--------FILE I/O--------I/O thread 0 state: wait Windows aio (insert buffer thread)I/O thread 1 state: wait Windows aio (log thread)I/O thread 2 state: wait Windows aio (read thread)I/O thread 3 state: wait Windows aio (read thread)I/O thread 4 state: wait Windows aio (read thread)I/O thread 5 state: wait Windows aio (read thread)I/O thread 6 state: wait Windows aio (write thread)I/O thread 7 state: wait Windows aio (write thread)I/O thread 8 state: wait Windows aio (write thread)I/O thread 9 state: wait Windows aio (write thread)Pending normal aio reads: 0 [0, 0, 0, 0] , aio writes: 0 [0, 0, 0, 0] , ibuf aio reads: 0, log i/o''s: 0, sync i/o''s: 0Pending flushes (fsync) log: 0; buffer pool: 0356585 OS file reads, 13778 OS file writes, 5351 OS fsyncs0.03 reads/s, 16384 avg bytes/read, 0.41 writes/s, 0.14 fsyncs/s-------------------------------------INSERT BUFFER AND ADAPTIVE HASH INDEX-------------------------------------Ibuf: size 1, free list len 5, seg size 7, 0 mergesmerged operations: insert 0, delete mark 0, delete 0discarded operations: insert 0, delete mark 0, delete 0Hash table size 178567, node heap has 1 buffer(s)0.00 hash searches/s, 0.41 non-hash searches/s---LOG---Log sequence number 153507782328Log flushed up to 153507782328Last checkpoint at 1535077823280 pending log writes, 0 pending chkp writes3382 log i/o''s done, 0.07 log i/o''s/second----------------------BUFFER POOL AND MEMORY----------------------Total memory allocated 45694976; in additional pool allocated 0Dictionary memory allocated 1027309Buffer pool size 2752Free buffers 0Database pages 2751Old database pages 995Modified db pages 0Pending reads 0Pending writes: LRU 0, flush list 0, single page 0Pages made young 362009, not young 00.03 youngs/s, 0.00 non-youngs/sPages read 356574, created 734, written 94770.03 reads/s, 0.03 creates/s, 0.31 writes/sBuffer pool hit rate 978 / 1000, young-making rate 22 / 1000 not 0 / 1000Pages read ahead 0.00/s, evicted without access 0.03/sLRU len: 2751, unzip_LRU len: 0I/O sum[10]:cur[0], unzip sum[0]:cur[0]--------------ROW OPERATIONS--------------0 queries inside InnoDB, 0 queries in queue1 read views open inside InnoDBMain thread id 2028, state: waiting for server activityNumber of rows inserted 1425, updated 1644, deleted 16, read 5668700.03 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.31 reads/s----------------------------END OF INNODB MONITOR OUTPUT

                  ============================
                  '

                  Comment


                  • #10
                    I don't see any active transactions.

                    Comment


                    • #11
                      Right. There are none. Your transactions are getting committed somehow. The problem is explained -- now you have to go figure out what is causing it It's probably something in your PDO settings, the optinos you use to open the connection, something like that. Have fun.

                      Comment


                      • #12
                        Sorry but I have posted the above content before starting.

                        Here is what I have got after starting the transaction.

                        Is that ok?


                        =====================================111130 22:21:04 INNODB MONITOR OUTPUT=====================================Per second averages calculated from the last 45 seconds-----------------BACKGROUND THREAD-----------------srv_master_thread loops: 2 1_second, 2 sleeps, 0 10_second, 3 background, 3 flushsrv_master_thread log flush and writes: 5----------SEMAPHORES----------OS WAIT ARRAY INFO: reservation count 3, signal count 3Mutex spin waits 0, rounds 0, OS waits 0RW-shared spins 3, rounds 90, OS waits 3RW-excl spins 0, rounds 0, OS waits 0Spin rounds per wait: 0.00 mutex, 30.00 RW-shared, 0.00 RW-excl------------TRANSACTIONS------------Trx id counter 50401Purge done for trx's n < 50206 undo n < 0History list length 1228LIST OF TRANSACTIONS FOR EACH SESSION:---TRANSACTION 0, not startedMySQL thread id 2, query id 132 localhost 127.0.0.1 rootSHOW ENGINE INNODB STATUS---TRANSACTION 50400, ACTIVE 111 sec3 lock struct(s), heap size 320, 2 row lock(s)MySQL thread id 5, query id 81 localhost ::1 root--------FILE I/O--------I/O thread 0 state: wait Windows aio (insert buffer thread)I/O thread 1 state: wait Windows aio (log thread)I/O thread 2 state: wait Windows aio (read thread)I/O thread 3 state: wait Windows aio (read thread)I/O thread 4 state: wait Windows aio (read thread)I/O thread 5 state: wait Windows aio (read thread)I/O thread 6 state: wait Windows aio (write thread)I/O thread 7 state: wait Windows aio (write thread)I/O thread 8 state: wait Windows aio (write thread)I/O thread 9 state: wait Windows aio (write thread)Pending normal aio reads: 0 [0, 0, 0, 0] , aio writes: 0 [0, 0, 0, 0] , ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0Pending flushes (fsync) log: 0; buffer pool: 0417 OS file reads, 7 OS file writes, 7 OS fsyncs0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s-------------------------------------INSERT BUFFER AND ADAPTIVE HASH INDEX-------------------------------------Ibuf: size 1, free list len 0, seg size 2, 0 mergesmerged operations: insert 0, delete mark 0, delete 0discarded operations: insert 0, delete mark 0, delete 0Hash table size 195193, node heap has 1 buffer(s)0.00 hash searches/s, 0.00 non-hash searches/s---LOG---Log sequence number 14135224032Log flushed up to 14135224032Last checkpoint at 141352240320 pending log writes, 0 pending chkp writes10 log i/o's done, 0.00 log i/o's/second----------------------BUFFER POOL AND MEMORY----------------------Total memory allocated 49938432; in additional pool allocated 0Dictionary memory allocated 82289Buffer pool size 3008Free buffers 2601Database pages 406Old database pages 0Modified db pages 0Pending reads 0Pending writes: LRU 0, flush list 0, single page 0Pages made young 0, not young 00.00 youngs/s, 0.00 non-youngs/sPages read 406, created 0, written 10.00 reads/s, 0.00 creates/s, 0.00 writes/sNo buffer pool page gets since the last printoutPages read ahead 0.00/s, evicted without access 0.00/sLRU len: 406, unzip_LRU len: 0I/O sum[0]:cur[0], unzip sum[0]:cur[0]--------------ROW OPERATIONS--------------0 queries inside InnoDB, 0 queries in queue1 read views open inside InnoDBMain thread id 2848, state: waiting for server activityNumber of rows inserted 0, updated 0, deleted 0, read 10.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s----------------------------END OF INNODB MONITOR OUTPUT============================

                        Comment


                        • #13
                          I see one transaction with 2 row locks. Now how does this output change if you select the same rows (FOR UPDATE) while that one transaction still has its 2 row locks?

                          Comment


                          • #14
                            so you mean 2 lock with same transaction is not proper thing?

                            Comment

                            Working...
                            X