CODEWITHSUNDEEP
sqliteatomic
LLS
LLS

Reputation: 2228

Sqlite atomically read and update a counter?

I am trying to implement a simple counter using SQLite provided with Python. I am using CGI to write simple dynamic web pages. It's the only simple way I can think of to implement a counter. The problem is I need to first read the counter value and then update it. But ideally, every user should read a unique value, and it's possible for two users to read the same counter value if they read simultaneously. Is there a simple way to make the read/write operation atomic? I unfamiliar with SQL so please give specific statements to do so. Thanks in advance.

I use a table with one column and one row to store the counter.

Upvotes: 8

Views: 5187

Answers (3)

Felipe
Felipe

Reputation: 17181

The complete code:

BEGIN EXCLUSIVE TRANSACTION;
UPDATE table_name SET counter = COALESCE(counter, 0) + 1;
COMMIT;

Upvotes: 0

Lothar
Lothar

Reputation: 13093

You should better not use the EXCLUSIVE keyword in the transaction to make it more efficient. The first select automatically creates a shared lock and the update statement will then turn it into an exclusive. It is only necessary that the SELECT and the UPDATE are both inside an explicit set transaction.

BEGIN TRANSACTION;
// read counter value
// update counter value
COMMIT TRANSACTION;

Upvotes: -2

Serhii Mamontov
Serhii Mamontov

Reputation: 4932

You may try this flow of SQL statements:

BEGIN EXCLUSIVE TRANSACTION;
// update counter here
// retrieve new value for user
COMMIT TRANSACTION;

While you perform updates in trisection, changes can be seen only with connection on which they was performed. In this case we used EXCLUSIVE transactions, which locks database for other clients till it will commit transaction.

Upvotes: 9

Related Questions