Welcome to the october web special. Today we will have a deeper look into some concurrreny situations for updating queries. In terms of transactional processing, update queries have to be handled with pretty much efforts by the database system. On the one hand, the updated data has to be stored in the database, on the other hand, the overwritten data has kept to be available in case of an aborted or rollbacked transaction. If a table is penetrated by heavy concurrent update operations, this might lead to deadlock situations. The topic of today webspecial is to unterstand such situation and to avoid deadlock effects

Assume the following scenario

• Table t1 with schema a int, b string(100) and about 50 rows
• Thread 1 makes heavy updates on table : while true, do update t1 set b = 'alpha', done
• Thread 2 makes heavy updates on table : while true, do update t1 set b = 'beta' , done

On the one hand, the table has to be traversed by a tablecursor, which takes a read lock for the corresponding data pages and the system page record lock. For the insert operations, a write lock is requested for the system page record lock and the data pages. In case of concurrent operations this might lead to a dead lock.

This might result in deadlocks for sysrecord locks and datapage locks as shown in the picture below

To simulate this effect, we create two small SQL scripts, each dedicated for another database thread For database thread T1, we create t1_update.sql

 set update sync off;
 update t1 set b = 'Hugo';
	

And for database thread T2, we create t2_update.sql

set update sync off;
update t1 set b = 'Bert';
	

The penetration loop can be implemented via a small shell script do_pen.sh , e.g. for thread 1

 #!/bin/sh
 while true
 do
     cgclt --server=localhost --user=lemke/lemke --batchfile=$1
 done
	

Now start both threads from two console windows in parallel

$ ./do_pen t1_update.sql
	

$ ./do_pen t2_update.sql
	

You should observe several locking messages in the database log file. This might lead to a deadlock situation as described above. Furthermore, it is not sure that all tuples are updated by one query. Due to missing synchronisation, the concurrent thread might have access to some of them. To avoid these effects, the update sync feature has to be enabled.

Now edit the two SQL scripts, to set update sync on. For this, edit both SQL scripts and change the update synchronisation as shown below

set update sync on;
	

For each query, the complete tables should be get updated now. The locking messages should be reduced.

Take care !