Databases
StackOverflow2013
Postsdbo
POSELECT ... FOR UPDATE locking trouble when applied on date range
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POSELECT ... FOR UPDATE locking trouble when applied on date range
    primarykey
    data
    text
    <p><br/> I'm having following table structure.</p> <pre><code> reservation: (InnoDB) ------------------------------------------ id INT, date DATE, item_id INT, slot VARCHAR(50); PRIMARY KEY(id), UNIQUE KEY(item_id,date). </code></pre> <p>Now i'm trying to use SELECT.....FOR UPDATE on reservation table inside a transaction to lock specific rows within date range(eg:2012-06-15 to 2012-06-16) of a particular item_id.</p> <pre><code>SELECT availability FROM reservation WHERE item_id={$item_id} AND (date&gt;='{$to_date}' AND date&lt;='{$from_date}') FOR UPDATE </code></pre> <p>NOW when i use the above statement, it is blocking all the rows of particular item_id even beyond the date range.I'm also using this UNIQUE KEY(item_id,date).</p> <p>How to lock only particular date range of a specific item_id?</p> <p>regards,<br/> ravi.</p> <p>ADDED:</p> <pre><code> | ===================================== 120615 20:14:13 INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the last 24 seconds ---------- SEMAPHORES ---------- OS WAIT ARRAY INFO: reservation count 12, signal count 12 Mutex spin waits 0, rounds 180, OS waits 4 RW-shared spins 10, OS waits 5; RW-excl spins 2, OS waits 2 ------------ TRANSACTIONS ------------ Trx id counter 0 15147 Purge done for trx's n:o &lt; 0 15136 undo n:o &lt; 0 0 History list length 2 Total number of lock structs in row lock hash table 2 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 0 0, not started, OS thread id 2940 MySQL thread id 76, query id 1487 localhost 127.0.0.1 root SHOW innodb status ---TRANSACTION 0 15143, not started, OS thread id 2588 MySQL thread id 10, query id 578 localhost 127.0.0.1 root ---TRANSACTION 0 15144, ACTIVE 11526 sec, OS thread id 2740 3 lock struct(s), heap size 320 MySQL thread id 11, query id 577 localhost 127.0.0.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 (write thread) Pending normal aio reads: 0, aio writes: 0, ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0 Pending flushes (fsync) log: 0; buffer pool: 0 187 OS file reads, 75 OS file writes, 29 OS fsyncs 0.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 inserts, 0 merged recs, 0 merges Hash table size 62383, used cells 0, node heap has 1 buffer(s) 0.00 hash searches/s, 0.00 non-hash searches/s --- LOG --- Log sequence number 0 8287163 Log flushed up to 0 8287163 Last checkpoint at 0 8287163 0 pending log writes, 0 pending chkp writes 24 log i/o's done, 0.00 log i/o's/second ---------------------- BUFFER POOL AND MEMORY ---------------------- Total memory allocated 23277934; in additional pool allocated 1430400 Buffer pool size 960 Free buffers 895 Database pages 64 Modified db pages 0 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages read 64, created 0, written 51 0.00 reads/s, 0.00 creates/s, 0.00 writes/s Buffer pool hit rate 1000 / 1000 -------------- ROW OPERATIONS -------------- 0 queries inside InnoDB, 0 queries in queue 1 read views open inside InnoDB Main thread id 1544, state: waiting for server activity Number of rows inserted 10, updated 0, deleted 0, read 66 0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s ---------------------------- END OF INNODB MONITOR OUTPUT ============================ | </code></pre>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USravi
    1. USravi
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. VO
      singulars
      1. POSELECT ... FOR UPDATE locking trouble when applied on date range
      1. This table or related slice is empty.
      1. VTUndeletion
    1. COI don't know about locking in mysql, but logically I think your query tries to lock all the item_id items _outside_ your date constraint ...
      singulars
      1. POSELECT ... FOR UPDATE locking trouble when applied on date range
      1. USuser1129682
    2. COtry using a between clause
      singulars
      1. POSELECT ... FOR UPDATE locking trouble when applied on date range
      1. USSashi Kant
    3. CO@SashiKant no use with between also.
      singulars
      1. POSELECT ... FOR UPDATE locking trouble when applied on date range
      1. USravi
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload