Tagged Questions
1
vote
1answer
36 views
How can I make a select statement get blocked?
Hello I am trying to intentionally make a SQL select statement get blocked by another simple SQL delete or update statement, for the purpose of learning. I prefer only InnoDB tables.
To prepare the ...
2
votes
1answer
63 views
Minimize locking on UPDATE with multiple WHERE columns
I have a problematic query that's causing a lock timeout:
UPDATE <some_table> SET row1=<some value>
WHERE row1 IS NULL AND row2 > <some value>
ORDER BY row2
LIMIT 100
...
0
votes
2answers
99 views
How are DB locks tied to connections and sessions?
I've heard of page- and row-level locks, but never heard them used with regards to sessions and connections. Today, our (MySQL) DBA was talking about row locks and implied that their scope can be set ...
1
vote
3answers
760 views
Avoiding “Waiting for table metadata lock” when `ALTER TABLE DROP PARTITION`?
I have some tables that many users need to access to:
mysql> show create table v3_cam_date\G
*************************** 1. row ***************************
Table: v3_cam_date
Create Table: ...
0
votes
0answers
40 views
What would this non-'unique index with a unique search condition` query lock?
I am not sure about this part of the transaction manual
For locking reads (SELECT with FOR UPDATE or LOCK IN SHARE MODE), UPDATE, and DELETE statements, locking depends on whether the statement ...
0
votes
1answer
52 views
Do I need a locking read?
I am inserting rows in a table, based on a value and on the existence of a row, which I get from SELECT statements. This function can be run concurrently, so I need to do some extra obviously. The ...
0
votes
0answers
217 views
InnoDB row level locks without SELECTs?
I have several (>30) MySQL clients inserting into InnoDB tables concurrently. I keep encountering lock wait timeouts and can't figure out why. Here is the situation:
Each client runs the following ...
5
votes
4answers
486 views
MySQL InnoDB locks primary key on delete even in READ COMMITTED
Preface
Our application runs several threads that execute DELETE queries in parallel. The queries affect isolated data, i.e. there should be no possibility that concurrent DELETE occurs on the same ...
1
vote
2answers
893 views
Cannot write to mysql database while select query in process
Running Mysql 5.1.57 on InnoDB, and have a database locking issue.
I have two separate sessions connected to a single database MySQL. On the first session, I run a long SELECT query (technically a ...
0
votes
1answer
597 views
Lock wait timeout exceeded on insert despite low lock_time in the slow query log
Our Java/Spring application reports occasional lock wait timeouts on single record INSERTs to a MySQL 5.5 InnoDB table. Here are a few slow query log records for some of the failed INSERTs.
# ...
2
votes
1answer
277 views
In InnoDB, does a Transaction imply any implicit locking of a table?
The Isolation level is REPEATABLE_READ.
The logic is as under:
Transaction begins
Read data from Table A
If (Table A has Any Data) End Transaction and exit
If Table A has No Data, Proceed further
...
2
votes
1answer
1k views
How can I find who holds the lock based on the hex-dumped?
I've been reading the Diagnosing MySQL InnoDB Locks article. Karl E. Jørgensen writes on 2008 so I'm consfusing it is in effect.
I would like to supply a snippet of the SHOW ENGINE INNODB STATUS:
...
2
votes
1answer
1k views
Why can't I get a lock on an InnoDB table?
I have problem with lock table command: LOCK TABLE v3_cam_date WRITE;
the command always running with state: "Waiting for table metadata lock".
Try to find out which thread was hold the lock on ...
2
votes
2answers
2k views
LOAD DATA (400k rows) INFILE takes about 7 minutes, cannot kill the “logging slow query” process?
MySQL version: 5.5.13
The query that I'm using:
SET AUTOCOMMIT=0;
LOAD DATA INFILE '/data10/select_into.outfile/v3_zone_date.out' INTO TABLE v3_zone_date FIELDS TERMINATED BY ',';
COMMIT;
...
3
votes
1answer
371 views
mysqladmin debug writes out Current locks but PROCESSLIST and INNODB STATUS don't show any
I'm using the 1.1.8 mysql-cacti-templates to gather metrics on MySQL 5.0.77 (CentOS 5.7).
Both the InnoDB Current Lock Waits and InnoDB Lock Structures graphs are charting with Cur[rent] values but ...
1
vote
3answers
437 views
Improve `Update` performance (rows locking issue)
I am running 30 scripts (PHP CLI) on Linux , each script are updating (loop) the data in the MySQL database.
When I typed 'mysqladmin proc' in terminal, I can see a lot of rows has been locked for ...
