0
votes
0answers
21 views

MySQL Upgrade from 5.5 to 5.6 causes system / table locks

We recently upgraded from MySQL 5.5 to 5.6 on AWS RDS. Before, we had a relatively performant database - definitely had its quirks, but it seemed to be tuned relatively well. After the upgrade, we're ...
0
votes
1answer
29 views

MySQL 5.6 replication causes 'waiting for table lock'

All of the sudden queries on slave server stopped with status "Waiting for table level lock" I restart mysql service and stop replications and locking does not show up anymore. Once I turn ...
1
vote
1answer
31 views

What happens when you have two events scheduled to run after identical n seconds?

I am running a survey and i am getting lots of data and i need to process that data quickly from within mysql.I am using a mysql events to help me process the hundreds of rows that i have. I have ...
1
vote
1answer
37 views

Is it possible that records are changed while a read query is still executing?

Suppose a read (select) query R is reading some 100 records from a table, 50 records are read so far, could in the meanwhile a write query W change some of the unread records? We didn't take any ...
0
votes
0answers
45 views

Unresponsive MySQL database

I've setup a MySQL server on Ubuntu machine. When running a heavy query (the table has over 1M rows), the database becomes unresponsive. Upon connection it returns: Can't connect to MySQL server ...
1
vote
1answer
59 views

MySQL consistent nonlocking reads vs. INSERT … SELECT

So, from my reading of the MySQL 5.5 documentation and some experiments I did with colleagues, I understand that the following is the case: With InnoDB tables, a simple SELECT statement (one that ...
2
votes
1answer
355 views

What could cause waiting for table metadata lock?

I am using a MySQL 5.6 DB with MyISAM tables under FreeBSD 8.3. The main part of code is in PHP, but the problem arises, when I launch the self-written on C++ chat-server. I use the mysql.h for DB ...
1
vote
0answers
42 views

MySQL WRITE LOCK Not Preventing Inserts

This should be a simple task. Can someone tell me why the below code is still inserting rows? lock table wp_postmeta write; insert into wp_postmeta values (null, 1,'test','test'); I've checked my ...
0
votes
1answer
25 views

Upgrade mysql from 5.0 to 5.5 in master-master configuration leads to broken replication

We created two new mysql servers running 5.5. Our old server were 5.0. The two servers are set up with master-master replication - this was the case for the two 5.0 servers which had no issues. We ...
0
votes
1answer
21 views

Is there a way to enumerate all locks that are currently being held via get_lock()?

I'm not seeing a way to list all locks currently being held by get_lock. I see you can check is_free if you know the name and release as well. I don't see an obvious function or infomration_schema ...
0
votes
1answer
47 views

MySQL locking tables / rows on select statement

I have a web app that does an addition to a field based on a user input. So say for example User-A inputs 7, my web app would first query the contents of the field for the current number in there, ...
1
vote
0answers
58 views

MySQL replication of single DB to same server

I have a DB Server running CentOS 6.4 and MySQL 5.5 from RPMs (not source). I have dozens of databases but one of them is being hit quite hard with large selects which locks out other updates which ...
0
votes
0answers
293 views

Why is InnoDB waiting for table metadata lock in simple INSERT/DELETE + SELECT?

I've started using a new MySQL server and moved an InnoDB table there. I'm seeing a weird behavior that when I have a long running INSERT / DELETE, any SELECT from the same table is locked which is ...
2
votes
1answer
142 views

Does MySQL guarantee atomic upgrade from read lock to write lock?

If a client session is currently holding a read lock (to allow concurrent reads while already preventing writes), and then acquires a write lock on the same table(s), does MySQL guarantee that the ...
-1
votes
2answers
91 views

When do I need transactions /locks?

I know that transactions are meant to group several operations as one. But if for example in the same table one transaction does DELETE FROM table where state = 'done' and another does either SELECT ...
0
votes
0answers
46 views

Why does the delete/update block each other in this case?

The following drives me crazy. I have: session1>begin; session2>begin; session2>update dummy set salary=1000 where id=11; session1>delete from dummy where id=1; id is the PK ...
1
vote
2answers
97 views

Is there a rule of thumb regarding the size and number of a buffer pool instances?

I know the consensus seems to be approximately 75% of ram should be dedicated to the buffer pool. But, I'm more interested in the optimal number of buffer pool instances. What are some things to ...
1
vote
2answers
366 views

MySQL views and table locking (5.1 MyISAM)

I have a large (160 million rows, 50GB) table in MySQL 5.1 using MyISAM. If I were to construct a view defined by a fairly complicated query, would a SELECT on the view lock the underlying table?
0
votes
0answers
30 views

Why are there more MySQL deadlocks on my 8-core machine than 16-core?

I'm running a bunch of scripts in parallel that do: START TRANSACTION REPLACE INTO... REPLACE INTO... REPLACE INTO... ... COMMIT On my 8-core machine, where 8 of these processes are running in ...
0
votes
0answers
51 views

MySQL Multiple threads inserting *SAME* record a Bad Thing?

Okay, as I've noted in other questions, I'm not a sophisticated database user even though I took my first course in relational algebra from ted Codd. I've now inherited an application that ...
1
vote
1answer
469 views

How does locking work in INSERT..ON DUPLICATE KEY UPDATE statements?

I am encountering deadlocks while trying to update multiple rows (a batch) using INSERT..ON DUPLICATE KEY UPDATE. Does MySQL lock all the rows in one batch or does it lock only the row it is updating ...
0
votes
0answers
74 views

Lock timeouts on a table with two keys, where only one connection writes for a given value of the first key at a time

I have a table user_rollup_message_list that is keyed by (user_id, message_reference). It is a write-heavy table, and gets enough writes that I get many lock timeouts, with a timeout of 50 seconds, ...
0
votes
1answer
7k views

What is causing Waiting for table level lock errors?

We got the database hanging twice already and trying to find a cause. show processlist Waiting for global read lock | INSERT INTO {myisam_table} ... In here the disk space was full so we thought ...
1
vote
1answer
538 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 ...
0
votes
2answers
288 views

Idle connection plus schema-modifying query causing locked database

As part of our automated deployment process for a web app running on a LAMP stack, we drop all our triggers and stored procedures and recreate them from source control. It turns out there was a hidden ...
1
vote
1answer
175 views

LOAD DATA INFILE on a MyISAM table blocks user read-queries, can we get around this?

I've got a large reference table, 100GB, in a MyISAM table, load it up from scratch goes well (4ish hours) using LOAD DATA INFILE. But we have 1GB each day we want to update in a batch process using ...
2
votes
1answer
764 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
424 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 ...
2
votes
3answers
5k 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
94 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 ...
1
vote
2answers
107 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
77 views

Which mysql server 5.5 variables would be involved in queries locking in sending data state?

A large portion of my queries (~58%) be it for queries with previously quick return rates or other, are spending in my monitor as sending data. I made some configuration changes but it was very broad ...
0
votes
1answer
460 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 ...
0
votes
2answers
411 views

MySQL concurrent INSERTs

I have a MySQL database with InnoDB tables. There are different client processes making SELECT (to check the existence of a value) and INSERT or UPDATE (depending on the result of the select) ...
6
votes
4answers
2k 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 ...
4
votes
1answer
155 views

Do isolation levels only apply to SELECTS and not UPDATES? [closed]

Scenario that might demonstrate different behavior for SELECTS depending on the isolation level: 1) 0:00 Thread A runs a query that returns 1000 rows that takes 5 minutes to complete 2) 0:02 Thread B ...
5
votes
2answers
4k views

How to correctly implement optimistic locking in MySQL

How does one correctly implement optimistic locking in MySQL? Our team has deduced that we must do #4 below or else there is a risk that another thread can update the same version of the record, but ...
10
votes
1answer
2k views

How do I swap tables in MySQL?

Suppose, I have a table foo, which contains some statistics that are computed every now and then. It is heavily used by other queries. That's why I want to compute more recent statistics in foo_new ...
3
votes
2answers
324 views

Writing transactions to .MYD file to take Backup

I'm trying to backup MySQL DB by archiving the data directory of required databases. I'm doing FLUSH TABLES WITH READ LOCK before taking the backup, but even then some of the transactions were not ...
4
votes
2answers
18k views

way to prevent queries from waiting for table level lock

We've encountered a problem after moving the database of our customer to an extra server. This should have had positive effects on the site's performance, but there is a problem with table locking in ...
3
votes
3answers
2k 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
1k 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. # ...
4
votes
1answer
4k views

MySQL get next unique value without auto increment

I have an MySQL database (InnoDB) that I'm developing for tracking Work Orders at my organization. There are multiple 'sites', and each site has work order numbers starting at 100. WorkorderID ...
2
votes
1answer
851 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
2answers
1k views

Will an ALTER TABLE ADD COLUMN on a slave break replication?

I need to add a last_modified column to a MyISAM table tbl_items. The crux of this problem is that tbl_items houses several gigabytes of data. Also of note, I am using a master-slave deployment with ...
3
votes
2answers
2k views

MySQL Locks while CREATE TABLE AS SELECT

I am running the following (dummy) query CREATE TABLE large_temp_table AS SELECT a.*, b.*, c.* FROM a LEFT JOIN b ON a.foo = b.foo LEFT JOIN c ON a.bar = c.bar Suppose the query ...
3
votes
1answer
3k 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
2k 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 ...
1
vote
1answer
774 views

MySQL hang in load data infile command

From the link: LOAD DATA (400k rows) INFILE takes about 7 minutes, cannot kill the "logging slow query" process? I had changed the command order (unlock before commit + replace some ...
3
votes
2answers
3k 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; ...