Error 1205: Lock wait timeout exceeded; try restarting transaction

[Carl Schroeder]

Error 1205: Lock wait timeout exceeded; try restarting transaction 

Just started getting these this afternoon. This is in the Google APIs console "SQL Prompt" window on deletes and updates.

I am 82MB into my 100GB limit.

Using python from app engine fails silently when trying to update. Very distressing.

Anyone have any ideas?

[Razvan Musaloiu-E.]

Can you tell us your instance name?

-- Razvan ME

--
You received this message because you are subscribed to the Google Groups "Google Cloud SQL discuss" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-cloud-sql-d...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.
 
 

[Carl Schroeder]

lemurspot-project:lemur-prime is the instance name

On Wednesday, March 20, 2013 5:57:51 PM UTC-7, Razvan Musaloiu-E. wrote:

Can you tell us your instance name?

-- Razvan ME

[Razvan Musaloiu-E.]

I don't see anything unusual with your instance. Are you doing any queries that grab the same locks? Can you investigate using SHOW PROCESSLIST and SHOW ENGINE INNODB STATUS using the command line tool?

-- Razvan ME

[Carl Schroeder]

Process list shows 3 sleeping processes.

=====================================

130321  1:33:37 INNODB MONITOR OUTPUT

=====================================

Per second averages calculated from the last 50 seconds

-----------------

BACKGROUND THREAD

-----------------

srv_master_thread loops: 444 1_second, 444 sleeps, 40 10_second, 44 background, 44 flush

srv_master_thread log flush and writes: 446

----------

SEMAPHORES

----------

OS WAIT ARRAY INFO: reservation count 40, signal count 40

Mutex spin waits 33, rounds 96, OS waits 0

RW-shared spins 40, rounds 1200, OS waits 40

RW-excl spins 0, rounds 0, OS waits 0

Spin rounds per wait: 2.91 mutex, 30.00 RW-shared, 0.00 RW-excl

------------

TRANSACTIONS

------------

Trx id counter BFF5A

Purge done for trx's n:o < BFF5A undo n:o < 0

History list length 2388

LIST OF TRANSACTIONS FOR EACH SESSION:

---TRANSACTION 0, not started

MySQL thread id 56, OS thread handle 0x7f8303f4e700, query id 737 localhost root

SHOW ENGINE INNODB STATUS

---TRANSACTION BFF58, not started

MySQL thread id 52, OS thread handle 0x7f8304011700, query id 712 localhost root

---TRANSACTION BFF56, not started

MySQL thread id 51, OS thread handle 0x7f8303d36700, query id 705 localhost root

---TRANSACTION BFF54, not started

MySQL thread id 50, OS thread handle 0x7f8303d77700, query id 698 localhost root

---TRANSACTION BFF52, not started

MySQL thread id 49, OS thread handle 0x7f8303e3a700, query id 691 localhost root

---TRANSACTION BFF50, not started

MySQL thread id 48, OS thread handle 0x7f8303df9700, query id 684 localhost root

---TRANSACTION BFF4E, not started

MySQL thread id 47, OS thread handle 0x7f8303bb0700, query id 677 localhost root

---TRANSACTION BFF4C, not started

MySQL thread id 46, OS thread handle 0x7f8303db8700, query id 670 localhost root

---TRANSACTION BFF4A, not started

MySQL thread id 45, OS thread handle 0x7f8303c73700, query id 662 localhost root

---TRANSACTION BFF48, not started

MySQL thread id 44, OS thread handle 0x7f8303c32700, query id 655 localhost root

---TRANSACTION BFF46, not started

MySQL thread id 43, OS thread handle 0x7f8303cf5700, query id 648 localhost root

---TRANSACTION BFF44, not started

MySQL thread id 42, OS thread handle 0x7f8303bf1700, query id 641 localhost root

---TRANSACTION BFF42, not started

MySQL thread id 41, OS thread handle 0x7f8303e8b700, query id 634 localhost root

---TRANSACTION BFF40, not started

MySQL thread id 40, OS thread handle 0x7f8303ecc700, query id 627 localhost root

---TRANSACTION BFF36, not started

MySQL thread id 35, OS thread handle 0x7f8304052700, query id 740 localhost root

--------

FILE I/O

--------

I/O thread 0 state: waiting for i/o request (insert buffer thread)

I/O thread 1 state: waiting for i/o request (log thread)

I/O thread 2 state: waiting for i/o request (read thread)

I/O thread 3 state: waiting for i/o request (read thread)

I/O thread 4 state: waiting for i/o request (read thread)

I/O thread 5 state: waiting for i/o request (read thread)

I/O thread 6 state: waiting for i/o request (write thread)

I/O thread 7 state: waiting for i/o request (write thread)

I/O thread 8 state: waiting for i/o request (write thread)

I/O thread 9 state: waiting for i/o request (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: 0

Pending flushes (fsync) log: 0; buffer pool: 0

395 OS file reads, 201 OS file writes, 123 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 merges

merged operations:

 insert 0, delete mark 0, delete 0

discarded operations:

 insert 0, delete mark 0, delete 0

Hash table size 796937, node heap has 1 buffer(s)

0.00 hash searches/s, 0.00 non-hash searches/s

---

LOG

---

Log sequence number 3752258

Log flushed up to   3752258

Last checkpoint at  3752258

0 pending log writes, 0 pending chkp writes

89 log i/o's done, 0.00 log i/o's/second

----------------------

BUFFER POOL AND MEMORY

----------------------

Total memory allocated 415236096; in additional pool allocated 0

Dictionary memory allocated 43152

Buffer pool size   24575

Free buffers       24190

Database pages     384

Old database pages 0

Modified db pages  0

Pending reads 0

Pending writes: LRU 0, flush list 0, single page 0

Pages made young 0, not young 0

0.00 youngs/s, 0.00 non-youngs/s

Pages read 384, created 0, written 121

0.00 reads/s, 0.00 creates/s, 0.00 writes/s

No buffer pool page gets since the last printout

Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s

LRU len: 384, unzip_LRU len: 0

I/O sum[0]:cur[0], unzip sum[0]:cur[0]

--------------

ROW OPERATIONS

--------------

0 queries inside InnoDB, 0 queries in queue

1 read views open inside InnoDB

Main thread id 140200693815040, state: waiting for server activity

Number of rows inserted 1, updated 40, deleted 0, read 531

0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s

----------------------------

END OF INNODB MONITOR OUTPUT

============================

To unsubscribe from this group and stop receiving emails from it, send an email to google-cloud-sql-discuss+unsub...@googlegroups.com.

[Carl Schroeder]

I think I have it sorted, thanks for the help. ;)

[Razvan Musaloiu-E.]

May I ask what was the issue? :P

-- Razvan ME

To unsubscribe from this group and stop receiving emails from it, send an email to google-cloud-sql-d...@googlegroups.com.

[Carl Schroeder]

An errant commit was leaving transactions hanging. Very bad for business as it turns out. ;)

To unsubscribe from this group and stop receiving emails from it, send an email to google-cloud-sql-discuss+unsubscr...@googlegroups.com.

For more options, visit https://groups.google.com/groups/opt_out.