java.sql.SQLException: Lock wait timeout exceeded; try restarting transaction

By -
innodb_buffer_pool_size and key_buffer_size
MySQL has very limited memory allocation for cache use. If you are using Atmail with MySQL in production, it is imperative you review and optimize MySQL for your hardware and memory. You can increase performance dramatically and reduce I/O usage by tweaking MySQL to use more of your system memory for the cache and buffers.
The two easy options for increasing performance for mysql are the innodb_buffer_pool_size andkey_buffer_size options. Atmail uses InnoDB tables for the user authentication & log-files, and if you have a large userbase performance can be dramatically improved by increasing the innodb_buffer_pool_size. Other tables such as the UserSettings, Abook, use the MyISAM table format which uses the key_buffer_size option.
For a production machine running all the Atmail services with 2GB of RAM we'd recommend the following option:


innodb_buffer_pool_size=256M
key_buffer_size=256M


innodb_lock_wait_timeout
The default value is 50 seconds. A transaction that tries to access a row that is locked by another InnoDB transaction will hang for at most this many seconds before issuing the following error:
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
When a lock wait timeout occurs, the current statement is not executed. The current transaction is not rolled back. (Until MySQL 5.0.13 InnoDB rolled back the entire transaction if a lock wait timeout happened. You can restore this behavior by starting the server with the --innodb_rollback_on_timeoutoption
By default, the transaction will not be rolled back. It is the responsibility of your application code to decide how to handle this error, whether that's trying again, or rolling back.
Also you can see more details of the event by issuing a "SHOW ENGINE INNODB STATUS" on server after getting this error.


innodb_lock_wait_timeout = 123

Steps to change values in my.cnf file (for below mysql 5.5)
1.    Then take back up my.cnf file which is located on /etc/ folder
2.    Stop the my sql server using commond service mysqld stop
3.    Then open my.cnf file and change

innodb_buffer_pool_size=1024M
key_buffer_size=256M
innodb_lock_wait_timeout = 900

4.    Take back up of all log file /var/lib/mysql/ib_logfile0
5.    And /var/lib/mysql/ib_logfile1
6.    Remove log files from /var/lib/mysql/ (ib_logfile0, ib_logfile1, etc..)
7.    Restart the mysql server service mysqld stop
8.    Check buffer size

Steps to change values in my.cnf file (for above mysql 5.5)


SET GLOBAL innodb_lock_wait_timeout = 120;
               or
SET innodb_lock_wait_timeout = 120;



Comments

Post a Comment

Popular posts from this blog

SinglePass Terms of Service

By -
SinglePass Terms of Service It is important that you read this carefully because you will be legally bound to these terms. Your access to and use of the Service is based on your acceptance of and compliance with these Terms. These Terms apply to all users others who access or use the SinglePass application . By accessing or using the Service you agree to be bound by these Terms and accept all legal consequences. If you do not agree to the terms and conditions of this Agreement, in whole or in part, please do not use the SinglePass application . PREAMBLE Whereas The TechSaga (Anand Gunasekaran) has developed a SinglePass system through which it provides Customers with access to save their password or notes in his/her mobile phone or other mobile devices(the “Device”) and comprising of service options, as detailed in the TECHSAGA Mobile Service terms and conditions accessible on the TECHSAGA   Website ( https://outofboundexception.blogspot.com/2019/...
Read more

Jasper Report Viruatization

By -
Image
Jasper Report Viruatization There are certain things to care while implementing the Jasper Reports for huge data-set to handle the memory efficiently, so that the application does not go out of memory. Pagination of the data and use of JRDataSource Viruatization of the report. Problem:- When there is a huge data set, it is not a good idea to retrieve all the data at one time.The application will hog up the memory and you’re application will go out of memory even before coming to the jasper report engine to fill up the data Solution :- To avoid that, the service layer/Db layer should return the data in pages and you gather the data in chunks and return the records in the chunks using JRDataSource interface, when the records are over in the current chunk, get the next chunk until all the chunks gets over Even after returning the data in chunks, finally the report has to be a single file.Jasper engine build the JasperPrint object for this. To avoid the piling up of me...
Read more

JasperReports Tutorial

By -
Image
Jasper Report is an open source java reporting engine, is Java based and doesn't have its own expression syntax. As JasperReports is a Java class library, and is not meant for end users, but rather is targeted towards Java developers who need to add reporting capabilities to their applications Report template Generally the following report layout is followed to generate reports by many of the commercial report generating tools. Following is the description of each element mentioned in the diagram. Element Description title Title contains the title of the report. It appears only once at the very beginning of the report, for example, "Tutorials Point Report". pageHeader PageHeader may contain date and time information and/or organization name. This appears at top of each page. columnHeader ColumnHeader lists the names of those specific fields which you want to display in the report, for example, "Author Name", "Starting Hour", "Finis...
Read more