Welcome Guest, Not a member yet? Register   Sign In
Lock contention using ci_session (auth_lib) and MySQL
#1

[eluser][email protected][/eluser]
Hey guys,

I am experiencing some ci_session lock contention using ci_sessions (auth_lib) on MySQL db. Some queries are taking 100+ seconds to execute, and then when the exact queries are rerun they are executed in .005s.

I know this is a database issue, but was wondering if anyone is willing to help me resolve this.

thanks
#2

[eluser]Las3r[/eluser]
locks are usually caused by 'too many' reads (that either do a page lock, or a row lock). The page locks are the 'assholes' here, they put the rest of the queries on-hold.

Perhaps you could try indexing the table (set a primary key?), and add some indexes, so that mysql knows where to find 'entries' so it doesnt have to lock the whole table in order to find something.

In mssql we use "select xxx from yyy with (NOLOCK) where zzz=zyzy", the - with (NOLOCK) - prevents the table from being locked, and forces the DBMS to read-only. google it to make sure it works for mysql, i can't verify that.

My experience with mysql is rather limited, i mostly use MSSQL for my applications, but I hope it helps...




Theme © iAndrew 2016 - Forum software by © MyBB