How to use database to realize the problem of competing resources for multiple servers

Simple method:

The process is divided into three steps:

1, from the database to query a state for the use of records (different database notation will not be the same)

SELECT * FROM table1 where state = ' unused ' limit 1

2. Update the data status to used, if the update succeeds (the number of records is 1 successful) means that the data is successful, otherwise this data has been robbed by other servers or threads.

Update table1 Set state = ' used ' where id= ' data id ' state = ' unused ';

3, the use of data, if the data is not properly used to restore the data state to unused

Upgrade Method:

There is a flaw in the above method, if the data after the server hangs, will result in the data is not properly processed, you can consider another method processing: Add a table for recording data acquisition table2 (TABLE1_ID (primary key), GUID (used to uniquely identify the data put), Expired (expiry time))

1, from the database to query a state for the record, different database writing will not be the same

Delete from table2 where expired <= ' current time ';(delete expired records first, possibly the acquiring party has been hung off)

SELECT * FROM table1 where state = ' unused ' and NOT EXISTS (SELECT * from table2 where table1_id = table1.id) limit 1;

2. Insert a data table name in the Table2 this party has obtained the data, if the insertion succeeds (the number of records is 1, the exception that does not throw a primary key conflict) indicates that the data obtained is successful, otherwise this data has been robbed by other servers or threads.

INSERT into table2 (...) VALUES (the ID of the record, the unique identity of the party, the current time + a reasonable value as the expiration time);

3, the use of data, if the normal use of data will update the data status is used, and delete table2 records

Update table1 Set state = ' used ' where id= ' data id ' state = ' unused ';

Delete from table2 where id=[id];

How to use database to realize the problem of competing resources for multiple servers