introduction

As developers, we often deal with databases.

When we modify the database, such as adding fields and updating records, we do not correctly evaluate the frequency of use of the table at this time, so we directly modify it, resulting in a long time of unresponsiveness to the modification operation and locking the table. In mysql, if the alter operation causes a Waiting for table metadata lock type locking table, It will make any operation unavailable, and the consequences are disastrous.

Unreasonable program design and frequent modification of the same table may also lead to table locking.

Once the database table locking problem occurs, it is often fatal. If the table locking time is too long, it may cause a large number of request blocking exceptions and affect the normal development of system business. This article will elaborate on the reasons for locking the database table, unlocking and how to avoid locking the table. I hope it can be helpful to you.

Scenario and reason of locking table

Scenario:

Locking the table usually occurs in DML (insert, update, delete) statements. For example, program A modifies the A data of table A, and there is no commit or rollback. At this time, program B modifies the A database of table A, which will produce an exception that the resources are busy, that is, locking the table.

DDL will also trigger table locking. For example, when MySql operates a large table and uses the alter statement to modify or add fields, there happens to be a long transaction (including reading) operating the table, which will trigger modification waiting, resulting in table locking.

reason:

When multiple transactions access multiple resources at the same time, if both parties have locked some resources but also need the locked resources of the other party, they cannot fully obtain the required resources in a limited time, they will be in an infinite waiting state, resulting in deadlock on their resource requirements and table locking.

How to unlock

Once the lock table is generated, we need to unlock it as soon as possible to release resources, otherwise it will be blocked all the time. The following mainly explains the unlocking methods of MySql and Oracle databases.

MySql unlock:

Execute sql:

select * from information_schema.processlist where command not in ('Sleep') ORDER BY time desc

The following contents can be queried through this sql:

The sql has been sorted according to the blocking duration from large to small. Find the long-time record id and kill:

kill 16519789

Through this command, you can also query the slow sql statements of mysql for optimization. The info field is the specific executed sql statement.

oracle unlock:

Execute the following sql:

SELECT SESS.SID,  
SESS.SERIAL#,  
LO.ORACLE_USERNAME,  
LO.OS_USER_NAME,  
AO.OBJECT_NAME Locked object name, 
LO.LOCKED_MODE Lock mode, 
sess.LOGON_TIME Login database time,
'ALTER SYSTEM KILL SESSION ''' || SESS.SID || ','||SESS.SERIAL#||'''' FREESQL
FROM V$LOCKED_OBJECT LO,  DBA_OBJECTS AO,  V$SESSION SESS 
WHERE AO.OBJECT_ID = LO.OBJECT_ID 
AND LO.SESSION_ID = SESS.SID 
ORDER BY sid, sess.serial#;

The following contents can be queried through this sql:

Then copy the contents of the last free SQL column and execute it directly.

After copying, an error may be reported: ORA-00031: session marked for kill, which means that ORACLE has marked it as a killed process, but it cannot kill it completely for the time being. At this time, we need to execute the following sql to find out its process id on the server:

# sid is the sid found in the sql above
select spid, osuser, s.program
   from v$session s,v$process p
   where s.paddr=p.addr
   and s.sid='24986' 

The process id on the server can be obtained through the sql above. Log in to the server where the database is located and kill it with the Kill Command:

kill -9 12009(Found out spid)

oracle queries slow sql within the specified time:

select *
 from (select sa.SQL_TEXT,
        sa.SQL_FULLTEXT,
        sa.EXECUTIONS "Number of executions",
        round(sa.ELAPSED_TIME / 1000000, 2) "Total execution time",
        round(sa.ELAPSED_TIME / 1000000 / sa.EXECUTIONS, 2) "Average execution time",
        sa.COMMAND_TYPE,
        sa.PARSING_USER_ID "user ID",
        u.username "user name",
        sa.HASH_VALUE
     from v$sqlarea sa
     left join all_users u
      on sa.PARSING_USER_ID = u.user_id
     where sa.EXECUTIONS > 0 and sa.LAST_ACTIVE_TIME >to_date( '2021-11-18 00:00:00','yyyy-mm-dd hh24:mi:ss')
     order by (sa.ELAPSED_TIME / sa.EXECUTIONS) desc)
 where rownum <= 50;

Relevant sql can be optimized according to the query results.

How to avoid locking tables

Generally, database lock tables are mostly caused by unreasonable program design. When writing code, we should fully consider the business scenario and try to achieve the following two points:

1. Minimize the time spent on DML (insert, update, delete) operations in the program, and isolate and control such operations to prevent blocking.
2. If the transaction generates an exception, ensure that the transaction can be rolled back normally.

In addition, do not easily perform DDL operations on the online database, especially the data tables that are used heavily and frequently. Forced modification may lead to disastrous consequences. At present, there are mainly the following ways to modify the online table structure:

1. Wait until the usage is small (such as midnight), but there are some risks
2. Temporarily stop the service and go online after modification, but it will affect the user experience
3. Copy the old table structure to the new table, create a trigger for the old table, synchronize the old table operation to the new table, then modify the table structure for the new table, synchronize the old data to the new table, lock the old table (read-only), and finally replace the old table with the new table. This scheme basically has no impact on the user experience, but the operation is complex.
4. Expand the table. If fields are used frequently, performance will be affected.

In actual production, we need to select the table structure change method suitable for ourselves according to the specific scenario.

epilogue

This paper discusses the problem of database locking table in detail. Friends who like it can collect it for emergencies.

In a real production environment, a set of application for monitoring the database is generally deployed. Once a locked table or slow sql is found, it will give an alarm in time and notify the relevant person in charge for processing. However, we still need to master the query of the locked table and the unlocked sql, which may play a role at the key moment.

Focus on the official account spiral programming geeks first time unlock the exciting content, and get exclusive research and development code generator to enhance development efficiency.