asked
lock timeout exceeded happening on one table only not other
I have table named user having 25000 entries are there. During a Day at particular time I get my sql error “Lock wait timeout exceeded; try restarting transaction”. all my table column are indexed properly. The the error happens only for this table as I have many more tables which are having more data than this table. it those table Inever got this timeout error.please help me out. thanks in advance
the query is like "update user set login status=1 where user_id=? ". i checked the process list when this happens it takes 41 seconds to update and even more some time. But this" time out error" does not occur every time it occurs occasionally.Like today i did not get any error but past two days i was getting the error.
A 41 second update is very long unless you're updating many thousands of records. I would double check if your indexes are working as well as you think they are by looking at the execution plan. I'd also look to the wait statistics for the query in question to see what it is waiting on. From the sounds of things, you're getting intermittent blocking caused by the update against this table. While the other tables may have more data, there's a good chance that their indexes are better supporting the query. Also, you could be seeing uncommitted transactions from an application that's holding open locks. When the query is running long, check for blocking or blocked processes on the server. Also, it's worth checking your statistics. Maybe the updates are against data that falls outside the existing histogram. That could cause bad plans to get created.
17 People are following this question.
