How to deal with MySQL deadlocks

Tags

, ,

In my application, I found Query failed several times with this error report.

‘Deadlock found when trying to get lock; try restarting transaction’

I will not discuss about deadlock and why deadlock occurs. We all know that more than one operations in a single resource make deadlock situation.

So, how to fix this?

At first we have to know the details of the deadlock. I know these two ways.

  1. The latest deadlock can be reviewed using SHOW ENGINE INNODB STATUS command.
  2. With MySQL 5.6, you can enable a new variable innodb_print_all_deadlocks to have all deadlocks in InnoDB recorded in mysqld error log.

https://dev.mysql.com/doc/refman/5.5/en/innodb-standard-monitor.html

You will see like this.

------------------------
LATEST DETECTED DEADLOCK
------------------------
160428 12:13:23
*** (1) TRANSACTION:
TRANSACTION 766ABFF1, ACTIVE 1 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1248, 2 row lock(s)
MySQL thread id 3, OS thread handle 0x2ab5dc319940, query id 14760240 10.231.0.28 root Searching rows for update
UPDATE smsoutbox SET InstanceID = 28 WHERE schedule < NOW() AND ServiceID = '21270_Game_Factory_Daily' AND srcMN = '21270' AND msgStatus = 'QUE' AND InstanceID IS NULL Order by msgID LIMIT 50
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 1662 n bits 168 index `PRIMARY` of table `smsgw_4_0`.`smsoutbox` trx id 766ABFF1 lock_mode X locks rec but not gap waiting
Record lock, heap no 87 PHYSICAL RECORD: n_fields 29; compact format; info bits 0
 0: len 4; hex 00034b45; asc   KE;;
 1: len 6; hex 000043ae5f5e; asc   C _^;;
 2: len 7; hex 430000110f1de6; asc C      ;;
 3: len 5; hex 3231323730; asc 21270;;
 4: len 13; hex 38383031393430343939393437; asc 8801940499947;

*** (2) TRANSACTION:
TRANSACTION 766ABFC8, ACTIVE 3 sec fetching rows
mysql tables in use 1, locked 1
27209 lock struct(s), heap size 2652600, 1843804 row lock(s)
MySQL thread id 1, OS thread handle 0x2ab5dc081940, query id 14720720 10.231.0.28 root Updating
UPDATE smsoutbox SET msgStatus = 'que', retryCount = retryCount+1, IsChargingEnable = 0 WHERE msgStatus = 'failed' AND retryCount < 3  and (NOW() - sentTime)> 10 AND InstanceID = 28 AND (CASE WHEN IsChargingEnable = 1 THEN ChargeResponse WHEN IsChargingEnable = 0 THEN '0' END) = '0'
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 0 page no 1662 n bits 168 index `PRIMARY` of table `smsgw_4_0`.`smsoutbox` trx id 766ABFC8 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

Record lock, heap no 2 PHYSICAL RECORD: n_fields 29; compact format; info bits 0
 0: len 4; hex 00034af0; asc   J ;;
 1: len 6; hex 000043ae5f5e; asc   C _^;;
 2: len 7; hex 430000110f1445; asc C     E;;
 3: len 4; hex 32353830; asc 2580;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 38404 n bits 120 index `PRIMARY` of table `smsgw_4_0`.`smsoutbox` trx id 766ABFC8 lock_mode X waiting
Record lock, heap no 52 PHYSICAL RECORD: n_fields 29; compact format; info bits 0
 0: len 4; hex 0022aa2a; asc  " *;;
 1: len 6; hex 0000766abff2; asc   vj  ;;
 2: len 7; hex c20000019e0110; asc        ;;
 3: len 4; hex 32303038; asc 2008;;

*** WE ROLL BACK TRANSACTION (1)
------------

Here you can find those two queries which have made the deadlock with details ( 2nd transactions holds the lock, 1st transaction waits).

Now where is the problem?

Two queries are different, where conditions are also different. So, two queries should not make deadlock. But it creates deadlock. Now after some studies I found the main reason.

InnoDB does not remember the exact WHERE condition, but only knows which index ranges were scanned. The locks are normally next-key locks that also block inserts into the gap immediately before the record. (http://dev.mysql.com/doc/refman/5.7/en/innodb-locks-set.html)

If you have no indexes suitable for your statement and MySQL must scan the entire table to process the statement, every row of the table becomes locked, which in turn blocks all inserts by other users to the table. It is important to create good indexes so that your queries do not unnecessarily scan many rows.

As both of my queries use same index (index ‘Primary’), deadlock is created. So, indexing is the solution. I have added index.

If two queries use different index, they will not make deadlock.

Now how to be sure that two queries are using different index? Use EXPLAIN keyword in front of your queries and see which key is using.

EXPLAIN SELECT * FROM smsoutbox
WHERE SCHEDULE < NOW() AND ServiceID = ‘21270_Game_Factory_Daily’ AND srcMN = ‘21270’ AND msgStatus = ‘QUE’ AND InstanceID IS NULL ORDER BY msgID LIMIT 50.Capture

So, if you are working with InnoDB, you have to think about index to avoid deadlock.

Thank you.

You can find some details from this link also. https://www.percona.com/blog/2014/10/28/how-to-deal-with-mysql-deadlocks/

Unicode in MySQL and C

Tags

,

For my application, I have to save Unicode message in MySQL database. And my C application will pick that value.

In MySQL part,

# For each database:
ALTER DATABASE database_name CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;
# For each table:
ALTER TABLE table_name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
# For each column:
ALTER TABLE table_name CHANGE column_name column_name VARCHAR(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

# (Don’t blindly copy-paste this! You have to use your own values.)

Now check.

SHOW VARIABLES WHERE Variable_name LIKE 'character\_set\_%' OR Variable_name LIKE 'collation%';

For MySQL part, You can get details from

https://mathiasbynens.be/notes/mysql-utf8mb4

 

Now, what I have to do in my C application

 mysql_init(&mysql);    
 
 // these two lines extra for unicode
 mysql_options(&mysql, MYSQL_SET_CHARSET_NAME, "utf8"); 
 mysql_options(&mysql, MYSQL_INIT_COMMAND, "SET NAMES utf8"); 

 mysql_real_connect(&mysql,CTH_SERVER,CTH_USER,CTH_PASSWORD,CTH_DB_IN,0,0,0)); 

Now you can use normal procedure.

ret = mysql_query(con, your_query);

Binary to Decimal and Decimal to Binary Conversion

Tags

, , ,

#include <stdio.h>
#include <math.h>

int binary_to_decimal(int binary) /* Function to convert binary to decimal.*/
{
int decimal = 0, i = 0, reminder;
while (binary != 0)
{
reminder = binary % 10;
binary /= 10;
decimal += reminder*pow(2.0,i);
++i;
}
return decimal;
}

int decimal_to_binary(int decimal) /* Function to convert decimal to binary.*/
{
int reminder, i = 1, binary = 0;
while (decimal != 0)
{
reminder = decimal%2;
decimal /= 2;
binary += reminder*i;
i *= 10;
}
return binary;
}

Introduction to Android Native Development

Tags

, , ,

When I had started Android Native Development, I had to face some problems. So, I am writing this tutorial for the beginners who is just starting.

Environment Setup:
1. Eclipse 4.2
2. JDk7
3. Android SDK
4. CDT Plugin
5. ADT Plugin
6. NDK

Now Install JDK, Eclipse. Configure SDK location. Install CDT Plugin. Then Install ADT Plugin (If you don’t install CDT, you won’t install NDK plugin).
After you install NDK plugin, you will find a new sub-menu option of Android(Windows-> Preference->Android). Set NDK location (be sure the path can’t contain a space).

Note: In some tutorial you may find that, they mentioned about Cygwin or others to compile NDK. But latest, even I used android-ndk-r8, I didn’t need those.

Create Project
1. Create a new Android project.
2. Right click on project explorer. you find “Android tools” in the menu.
3. Select “Add Native Support…”. Write your library name. You will find a new folder in project explorer (jni). jni contains a cpp file and Android.mk.
4. Now write your method in cpp.
JNIEXPORT return_type JNICALL Java_yourPackage_YourJavaClass_MethodName(JNIEnv *env, jobject thiz)

JNIEnv *env, jobject thiz MUST be in parameters. If you need more, you can add after those. As it is cpp file, you need to use extern “C”.

Here I used.
extern “C” jint Java_com_example_ndktest_MainActivity_AddValue(JNIEnv *env,
jobject thiz,
jint nValue1,
jint nValue2)

In your MainActivity class, You should write.
static
{
System.loadLibrary(“MathSDK”); // libMathSDK.so is the lib name
}

public native int AddValue(int nValue1, int nValue2);

Now You can use AddValue Method as a member method of MainActivity class.
You don’t need to write anything till now in Android.mk.

Now build and run.

Convert char[] to hex string


char strInput[] = "yourchardata";
char chHex[3] = "";
int nLength = strlen(strInput);

char* chResut = new char[(nLength*2) + 1];
memset(chResut, 0, (nLength*2) + 1);

for (int i = 0; i < nLength; i++)
{
sprintf(chHex, "%02X", strInput[i] & 0x00FF);
memcpy(&(chResut[i*2]), chHex, 2);
}

printf("\n%s", chResut);
delete chResut;
chResut = NULL;

Create and Delete Thread in MFC

Tags

,

CWinThread* m_pThread;

// Creating Thread
if (NULL == m_pThread)
m_pThread = AfxBeginThread(ThreadProc,
NULL,
THREAD_PRIORITY_NORMAL,
0,
CREATE_SUSPENDED,
NULL);

m_pThread->m_bAutoDelete = false;
m_pThread->ResumeThread();

TerminateThread forces another thread to exit. You should avoid calling it at all costs as it will stop a thread dead in it’s tracks without any chance to cleanup. This includes any CRT memory allocated.

ExitThread is for the currently running thread to stop itself nice and cleanly. When you called it above, you likely forced the main (UI) thread to exit and likely left the running threads still lingering around. Hence, your program was still running as evidenced in Task Manager. GetExitCodeThread was also likely failing since the threads had not actually exited.

But the right way to stop a thread is to cleanly signal by any clean means necessary that it should exit. Then allow the threads to exit on their own before allowing the main thread to exit.

// Deleting thread
WaitForSingleObject(m_pThread->m_hThread, INFINITE);

// get the thread's exit code (I'm not sure why you need it)
DWORD dwExitCode;
BOOL bRet = GetExitCodeThread(m_pThread->m_hThread, &dwExitCode);

// cleanup the thread
CloseHandle(m_pThread->m_hThread);
m_pThread->m_hThread = NULL;

delete m_pThread;
m_pThread = NULL;

Get console output from exe in C++

Tags

, ,

I have an executable file which prints “Hello World”;  File name “PrintOutput.exe”.

I have to get the console output that is “Hello World” in C++ project. How will I do that?

int _tmain(int argc, _TCHAR* argv[])
{
char   psBuffer[128];
FILE   *pPipe;

pPipe = _popen( “PrintOutput.exe”, “rt” );
if (NULL == pPipe)
{
printf(“error input”);
return 0;

}
while(NULL != fgets(psBuffer, 128, pPipe))
{
printf(psBuffer); // print the console output of “PrintOutput.exe”

}

_pclose( pPipe );

}

 

Get console output from exe in php

Tags

, ,

I have an executable file which prints “Hello World”;  File name “PrintOutput.exe”.

I have to get the console output that is “Hello World” in my PHP page. How will I do that?

Solution:

<?php
// put your code here

$output = shell_exec(“PrintOutput.exe”);

echo $output;     // it will print “Hello World”

?>

 

 

Unsorted map

Tags

, ,

In C++, most of the programmers use map to store elements formed by a combination of a key value and a mapped value, following a specific order.

But for some cases, I needed an unsorted map. I am explaining.

I have some data,of string type such as date, account number, account name, balance, debit amount, credit amount etc. All are key values here. I have to store values associated with these.

Problem is, map is sorting my data. that is account name, account number, balance, date order. I am not getting data in my inserted order.

So, How did I solve this problem?

I used.

struct Cell
{
string keyValue;
string data;
}

vector<Cell> cells;

One of the most benefits is, vector is faster than map.

Check before joining a software firm

Tags

In my job experience, I got names of many software companies. Some seem good, but there are some internal issues. If anyone miss to know any of these, he/she may suffer after joining there.

I think it is a good idea to have a checklist for this. Here it goes!

WORK CULTURE or PROCESS:

1. Does that company follow software engineering process?
In my experience, I found some senior people who STRONGLY BELIEVE that no process can be applied in their projects. Also, some even don’t know the difference between Software Engineer and Programmer.

2. How about their interview process? That is they will take your interview and you will also get idea about them.
Some companies don’t know how to take interview for Senior software engineer, software engineers or programmers. All seem same to them. So, if you are appearing interview for sr s/w engg, but their interview seems that for programmers, you should know that they will assign you task of programmers.

3. How many hours do you “actually” need to work a day, on average? Can a programmer can work more than 8hrs a day? Every overtime just add bugs in projects.

4. How frequent is overtime and working in weekend? In some companies, 11/12 hrs a day is normal working hours and their projects are always buggy. And same about weekend, on an average 3/4 weekend are added to working day in a month.

5. How friendly is the relationship among the engineers and also with management?

6. Notice period. when employees try to resign or company try to terminate an employee’s job, what is the notice period. Some companies don’t follow the this rule. They just terminate job from next day and pay 1 or 2 months salary.

 

PROJECT:

1. How many projects are currently going on?

2. What is the average age/length of the projects? How many years employees are involved there? that is, project is long, but for some reason, employees can not stay there.

3. Do they work on products or projects for clients? Products are so long and all are preplanned. So. company has to release 1/2 times a year with the specific features. So, work load is distributed in whole year. but if projects for clients, sometime no task, sometime so much pressure.

4. Which technologies does the company work with? which domain etc.

SALARY and other benefits:

1. Office space or location. It shows how much the company is serious about their business, how much the company are investing for this business. It shows the job security, financial strength.

2. What is the salary range for a fresher?

3. How many times in a year do your company give salary increment? Do they review in time or some months delay?

4. What is the average percentage of increment?

5. Do you feel that your job is secured? How many years employees stay at that firm? Can this company show any long-term vision to employees?

6. What are the other benefits?

7. Do they use documents such as offer letter/appointment letter, experience letter, release letter? Get the offer letter, read the terms and conditions. You and the company will sign. You will take one copy and company will take another copy. some companies have some plan to cheat. They won’t use any documents of proof of job.

8. What is the payment method? Bank account or cheque or cash. That is, check whether their salary payment method is official or not. If they pay salary in cash, what are the proofs that you got salary, or you got some part of salary and some part will be paid, or how can you claim that your company didn’t pay your salary.

And at last Joel Test

The Joel Test

Do you use source control?
Can you make a build in one step?
Do you make daily builds?
Do you have a bug database?
Do you fix bugs before writing new code?
Do you have an up-to-date schedule?
Do you have a spec?
Do programmers have quiet working conditions?
Do you use the best tools money can buy?
Do you have testers?
Do new candidates write code during their interview?
Do you do hallway usability testing?

[1] http://www.joelonsoftware.com/articles/fog0000000043.html
Better score, better company..

So, you should choose your employer as like as employers choose their employees.

[ Idea courtesy: Ahmad Ferdous Bin Alam’s post  Checklist Before You Join a New Company ]