Implementing a simple distributed lock using SQL_PHP tutorial

Use SQL to implement simple distributed locks

The main difference between distributed locks and ordinary locks is that the participating subjects span different nodes, so node failures and network failures need to be taken into consideration. To understand the key points of the problem, you can use various different things to implement it, such as Redis, ZooKeeper, etc. But it is actually very easy to implement using SQL. The following uses PostgreSQL as an example to illustrate.

1. Method 1: Session lock

Use the unique exclusive session level advisory lock in PostgreSQL.
pg_advisory_lock(key bigint)
pg_advisory_unlock(key bigint)
pg_try_advisory_lock(key bigint)

Detailed reference: http://www.postgres.cn/docs/9.4/functions-admin .html#FUNCTIONS-ADVISORY-LOCKS-TABLE

This kind of lock is session level. Before releasing the lock, the acquirer of the lock must always hold the session, that is, the connection, otherwise the lock will be release.
This feature naturally solves the problem of lock release when the lock acquirer fails.
However, for locks that need to be held for a long time, it will generate long connections, and database connections are relatively resource-intensive. When the number is larger, there are usually only a few thousand. This is something that needs to be paid attention to.
Another issue that needs to be considered is that when a network or node fails, both ends of the connection may not be aware of it immediately, so TCP KeepAlive is necessary. Fortunately, both the PostgreSQL client and server support this setting.
The following are the parameters of the server:
tcp_keepalives_idle
tcp_keepalives_interval
tcp_keepalives_count

2. Method 2: Period lock

The lock object is persistent to prevent it from being obtained The client of the lock crashes and the lock cannot be released. Each lock has an expiration date.
In PostgreSQL, it can be implemented in the following way

Create table

1. postgres=# create table distlock(id int primary key,expired_time interval,owner text,ts timestamptz) ;
   
2. CREATE TABLE
   
3. postgres=# insert into distlock(id) values(1);
   
4. INSERT 0 1

Lock and renewal

1. postgres=# update distlock set owner='node1',ts=now(),expired_time=interval '20 second' where id =1 and (owner='node1' or owner is null or now() > ts expired_time);
   
2. UPDATE 1

The client that gets the lock if you want If you hold a lock for a long time, you must regularly perform the same method to renew the lock, otherwise the lock will be lost.

At this time, other clients will fail to obtain the lock

1. postgres=# update distlock set owner='node2',ts=now(),expired_time=interval '20 second' where id=1 and (owner='node2' or owner is null or now() > ts expired_time);
   
2. UPDATE 0

Waiting for locks The lock was successfully retrieved after expiration

1. postgres=# update distlock set owner='node2',ts=now(),expired_time=interval '20 second' where id=1 and (owner='node2' or owner is null or now() > ts expired_time);
   
2. UPDATE 1

Release lock

1. postgres= # update distlock set owner=null,ts=now() where id=1 and owner='node2';
   
2. UPDATE 1

3. Summary

It can be seen that implementing distributed locks using a relational database is not complicated. In particular, the above table-based lock implementation coupled with reliable HA deployment can ensure the durability and non-loss of lock information. However, using table updates to implement locks is relatively heavy and is not suitable for scenarios that require very high lock performance.